Data Warehousing Objects and its Definitions

Data Warehousing Objects

Fact tables and dimension tables are the two types of objects commonly used in dimensional data warehouse schemas.

Fact tables are the large tables in your data warehouse schema that store business measurements. Fact tables typically contain facts and foreign keys to the dimension tables. Fact tables represent data, usually numeric and additive, that can be analyzed and examined. Examples include Sales, Cost and Profit.

Dimension tables, also known as lookup or reference tables, contain the relatively static data in the data warehouse. Dimension tables store the information you normally use to contain queries. Dimension tables are usually textual and descriptive and you can use them as the row headers of the result set. Examples are Customers or Products

Fact Table:

A fact table typically has two types of columns: those that contain numeric facts (often called measurements), and those that are foreign keys to dimension tables. A fact table contains either detail-level facts or facts that have been aggregated. Fact tables that contain aggregated facts are often called summary tables. A fact table usually contains facts with the same level of aggregation.

Fact tables are summarized into three types:

1   .   Additive

2   .   Semi-Additive

3   .   Non-Additive

Additive facts can be aggregated by simple arithmetical addition. A common example of this is sales.Semi-additive facts can be aggregated along some of the dimensions and not along others. An example of this is inventory levels, where you cannot tell what a level means simply by looking at it. Non-additive facts cannot be added at all. An example of this is averages.

Requirements of Fact Table:

You must define a fact table for each star schema. From a modeling standpoint, the primary key of the fact table is usually a composite key that is made up of all of its foreign keys.

Dimension Table:

A dimension is a structure, often composed of one or more hierarchies, that categorizes data. Dimensional attributes help to describe the dimensional value. They are normally descriptive, textual values. Several distinct dimensions, combined with facts, enable you to answer business questions. Commonly used dimensions are customers, products, and time.

Dimension data is typically collected at the lowest level of detail and then aggregated into higher level totals that are more useful for analysis. These natural rollups or aggregations within a dimension table are called hierarchies.

Commonly dimension tables are categorized into three types:

1      Conformed dimension

2      Degenerated dimension

3.   Junk dimension

If we are saying in simple word, Conformed dimension table is a table which is having at least one or multiple primary key or unique key to join with one or multiple fact table. For example product table is related with the sales fact table

A conformed dimension is a set of data attributes that have been physically implemented in multiple database tables using the same structure, attributes, domain values, definitions and concepts in each implementation. A conformed dimension cuts across many facts.

Dimensions are conformed when they are either exactly the same (including keys) or one is a perfect subset of the other. Most important, the row headers produced in the answer sets from two different conformed dimensions must be able to match perfectly.

A junk dimension is a convenient grouping of typically low-cardinality flags and indicators. By creating an abstract dimension, these flags and indicators are removed from the fact table while placing them into a useful dimensional framework.

A Junk Dimension is a dimension table consisting of attributes that do not belong in the fact table or in any of the existing dimension tables. The nature of these attributes is usually text or various flags, e.g. non-generic comments or just simple yes/no or true/false indicators. These kinds of attributes are typically remaining when all the obvious dimensions in the business process have been identified and thus the designer is faced with the challenge of where to put these attributes that do not belong in the other dimensions.

One solution is to create a new dimension for each of the remaining attributes, but due to their nature, it could be necessary to create a vast number of new dimensions resulting in a fact table with a very large number of foreign keys. The designer could also decide to leave the remaining attributes in the fact table but this could make the row length of the table unnecessarily large if, for example, the attributes is a long text string.

Refer Ralph Kimball’s explanation about Junk Dimension

Hierarchies

Hierarchies are logical structures that use ordered levels as a means of organizing data. A hierarchy can be used to define data aggregation. For example, in a time dimension, a hierarchy might aggregate data from the month level to the quarter level to the year level. A hierarchy can also be used to define a navigational drill path and to establish a family structure.

Within a hierarchy, each level is logically connected to the levels above and below it. Data values at lower levels aggregate into the data values at higher levels. A dimension can be composed of more than one hierarchy. For example, in the product dimension, there might be two hierarchies—one for product categories and one for product suppliers.

Dimension hierarchies also group levels from general to granular. Query tools use hierarchies to enable you to drill down into your data to view different levels of granularity. This is one of the key benefits of a data warehouse.

When designing hierarchies, you must consider the relationships in business structures. For example, a divisional multilevel sales organization.Hierarchies impose a family structure on dimension values. For a particular level value, a value at the next higher level is its parent, and values at the next lower level are its children. These familial relationships enable analysts to access data quickly.

Levels

A level represents a position in a hierarchy. For example, a time dimension might have a hierarchy that represents data at the month, quarter, and year levels. Levels range from general to specific, with the root level as the highest or most general level. The levels in a dimension are organized into one or more hierarchies.

Level Relationships

Level relationships specify top-to-bottom ordering of levels from most general (the root) to most specific information. They define the parent-child relationship between the levels in a hierarchy.

Hierarchies are also essential components in enabling more complex rewrites. For example, the database can aggregate an existing sales revenue on a quarterly base to a yearly aggregation when the dimensional dependencies between quarter and year are known.

More than 4000 Character in OBIEE

Recently I have come across throught the business requirement where the backe end databae SQL server is having some dimensional columns in a table which is having around 15000 character. As per the business need I need to show the same value in BI Dashboard.

But as we know OBIEE repository is having 3 types of Character types and max size of the datatype is 4000

1. VARCHAR
2. BINARY VARCHAR
3. LONGVARCHAR

If values exceed more than 4000 character in a columns It  does not show the value in the OBIEE answers/Dashboards but the interesting fact is OBIEE VARCHAR column support to the max size of 8000

So here is the workaround:

Step1:
Right click on the particular column in the physical layer of the repository and select 'Properties' and set LONGVARCHAR as data type and set '32768' as size

Step2:
Use the substring function the split the value in the column. So here my columns is having around 15000 character so I am splitting first 8000 character into one column and another 7000 character to another column.

Here keep two things in Minds.

First we need to cast the column to change LONGVARCHAR to VARCHAR and then use SUBSTRING to split the values

For e.g:

Original Column:
BAW."Dim - Product"."Sales Comment"
Sales Comment1 : Which will be holding the value of first 8000 character 

CAST ( SUBSTRING(BAW."Dim - Product"."Sales Comment" FROM 0 FOR 8000) AS VARCHAR ( 8000 ))

Sales Comment2: which will be holding the value of another 8000 character
 CAST ( SUBSTRING(BAW."Dim - Product"."Sales Comment" FROM 8000 FOR 16000) AS VARCHAR ( 8000 )) 

Step3: 
Drag the newly created columns into the corresponding presentation tables in presentation layer.

Step4: 
Reload the metadata repositoy in OBIEE answers side and check with the newly imported columns in results.

Note:
If you need both values in a single column result, try to use the narrative view methods.

Cool... isn't it???!!!

Cheers!
Bose

Important OBIEE Interview Questions

Q1. Tell me about you and your technical expertise.

      Ans: your personal details, with studies job experience and highlighting the projects 

      and your technical skills

Q2. Which versions of OBI/ Siebel Analytics you have worked in?

Q3.Have you worked in Informatica? Which version

Q4. Explain me about your recent Project?

      Ans: Explain the Domain of the Project, Company from which you have worked in             that project, who is the Client, Technologies used in this application like OS,             

      Database,  OBI, Source Application (Mention as Siebel CRM)

Q5. Have you worked on OBIEE or OBIA?

Ans: OBIA

Q6.What is OBIA?

Q7.What is OBAW?

Q8.What is the Components of OBIEE and OBIA?

Q9.What is the Difference in OBIEE and OBIA?

Q10. Explain the Architecture of OBIA?

Q11. Explain the architecture of OBIEE?

Q12. What have you done in RPD?

Q13. What have you done in Reports what kind of reports / views you have worked in

Q14. Explain about one of the complex situation you have faced in your project and how          did you solve that. (While answering Provide Example)

Q15. What is Connection Pool?(While answering Provide Example)

Q16. What do you mean by Shared Log On option in the Connection Pool?(While answering         Provide Example)

Q17. How do you create a DSN?

Q18. RPD UserID and Password and Connection Pool user ID Password are usually same or            different? Why? (While answering Provide Example)

Q19. What are the Physical Layer Objects?

Q20. Why do you need to create an Alias? (While answering Provide Example)

Q30. How do you create a view in the Physical Layer of RPD? (While answering Provide            Example)

Q31. How many Layers are there in the RPD?

Q32. What is the Use of BMM Layer? (While answering Provide Example)

Q33. What is the Use of RPD? (While answering Provide Example)

Q34. What is the use of Presentation Catalogue? (While answering Provide Example)

Q35. RPD is Holds Data or Metadata?

Q36. What is the use of Config.INI File (While answering Provide Example?)

Q37. Where is this file located? (While answering Provide Example)

Q38. Where is the Presentation Catalogue located (While answering Provide Example?)

Q39. How do you enable Cache (While answering Provide Example?)

Q40. What are the other properties that you can set in the Config File (While answering Provide Example)

Q41. Where is Instance Config.xml located and what is the use of this File(While answering Provide Example)

Q42. What is Cache Persistence? Where do you set that? (While answering Provide Example)

Q43. How do you make a table "Cacheable" (While answering Provide Example?)

Q44. Suppose In the Config file I have enabled the Cache still cache is Not generating what could be the possible reason?(While answering Provide Example)

Q45. Based on What criteria or situation a report / query will hit the cache? (While answering Provide Example)

Q46. What do you understand by Cache? (While answering Provide Example)

Q47. What is a Fact Table? (While answering Provide Example)

Q48. What is a dimension table? (While answering Provide Example)

Q49. What is Measure? (While answering Provide Example)

Q50. What is Attribute? (While answering Provide Example)

Q51. What is star schema? (While answering Provide Example)

Q52. What is snowflake Schema? (While answering Provide Example)

Q53. What are the Difference BW OLTP and OLAP? (While answering Provide Example)

Q54. What is Normalized and what is De-normalized? (While answering Provide Example)

Q55. Why the Fact is on the many side and Dimension is at the One Side of a star schema (While answering Provide Example)

Q56. How many LTS can a logical table have? (While answering Provide Example)

Q57. How many Physical Sources a LTS can have? (While answering Provide Example)

Q58. In which scenario you will have more than one logical table source under one logical table? (While answering Provide Example)

Q59. Under which scenario you will have one LTS and More than one Physical Sources inside that? (While answering Provide Example)

Q60. Why do we need to create a complex/logical join in the BMM layer? (While answering Provide Example)

Q61. What is the difference b/w Logical join and Physical Join?(While answering Provide Example)

Q62. One Logical Column can be mapped to how many Physical Columns? (While answering Provide Example)

Q63. One Logical Column can be mapped to how many logical columns? (While answering Provide Example)

Q64. If you create a Logical column based on an existing logical Column, and a similar column based on two physical columns what would be the difference? (While answering Provide Example)

Q65. How do you create a calculated measure? (While answering Provide Example)

Q66. How do you create Dimension Hierarchy? Tell all the steps(While answering Provide Example)

Q67. Why Dim Hierarchy is required? (While answering Provide Example)

Q68. What do you understand about content level of a Fact Table? (While answering Provide Example)

Q69. What do you understand about Granularity of a fact and Dimension table? (While answering Provide Example)

Q70. What is the Significance of providing the content specification in the Fact Table? (While answering Provide Example)

Q71. How do you implement Aggregate Fact Table? (While answering Provide Example) provide all the steps

Q72. Why do we need to use Aggregate table? (While answering Provide Example)

Q73. While implementing aggregate table how many LTS will be there in the logical table? (While answering Provide Example)

Q74. One presentation catalogue can be mapped to how many Business Model? (While answering Provide Example)

Q75. One How many Presentation catalogue can be mapped to one Business Model.(While answering Provide Example)

Q76. How many Types of variables are present ? (While answering Provide Example)

Q77. What is the use of Each Type of Variable? (While answering Provide Example)

Q78. What is a Initialization Block? (While answering Provide Example)

Q79. What is a Row Wise Initialization block? (While answering Provide Example)

Q80. What is a Level Based Measure? (While answering Provide Example)

Q81. What is the use of a Level Based Measure? (While answering Provide Example)

Q82. How do you create Level Based Measure? (While answering Provide Example)

Q83. I want to create the Following Measure Explain how you will do that.

     # of SR closed in last 14 Days. (While answering Provide Example)

Q84. Want to create a measure.

     # Of SR Closed Till Date (While answering Provide Example)

Q85. What is the Time Series Function? How to use Time Series Function. (While answering Provide Example)

Q86. How to create following measure?

     # of SR Closed Monthly / No of SR Closed Total

Q87. What is Fragmentation Content? (While answering Provide Example)

Q88. What is Where Clause Filter? Where do you apply such filter? Why do you apply such filter? (While answering Provide Example)

Q89. What is Authentication? What is Authorization? (While answering Provide Example)

Q90. How many type of Authentication are present? (While answering Provide Example)

Q91. What do you mean by Database authentication? (While answering Provide Example)

Q92. What do you mean by External Table authentication? (While answering Provide Example)

Q93. How do you authenticate the users using the variables? What kind of variable you use for this purpose (While answering Provide Example)

Q94. What is the Difference between Non system Session Variable and Dynamic Repository Variable?(While answering Provide Example)

Q95. How do you provide access to different Subject Areas? (While answering Provide Example)

Q96. Can a Group be member of another Group? (While answering Provide Example)

Q97. Suppose a User U1 is member of a Group1. Group1 has access to Subject Area A1. Group1 Does not have access to Subject Area A2, Group1 is member of Group2. Group2 has no Access for Subject Area A2 , but has Access for Subject Area Sa3.

User U1 will have access to which subject Areas? Question from Group Inheritance (While answering Provide Example)

Q98. Want to replace the Null Values as 0 for the Measure Sales Value? How would you do that?(While answering Provide Example)

Q99.What is the difference between Pivot and Tabular report?(While answering Provide Example)

Q100. Why do we need exclude column option in Pivot report? (While answering Provide Example)

Q101. What is section in a pivot table report? (While answering Provide Example)

Q.102 what is Pages in a pivot table report? (While answering Provide Example)

Q103. What is calculated item in a pivot table report (While answering Provide Example?)

Q104. What is conditional formatting (While answering Provide Example)

Q105.  I want to compare if actual is greater than the target then we need to highlight the sales rep column how to do this (While answering Provide Example)

Q106. What do you mean by Scope of a Dashboard prompt (While answering Provide Example?)

Q 107.  What is Dashboard prompt how to create dashboard prompt (While answering Provide Example)

Q108. Difference between Column selector and View Selector (While answering Provide Example)

Q109. What do you understand by Report Defaults in Dashboard prompt (While answering Provide Example?)

Q110. What is the difference between Dashboard prompt and Global Filter (While answering Provide Example?)

Q111. What is the Difference between dashboard Prompt and Report Prompt (While answering Provide Example)

Q112. How many Type of Graphs are supported in OBI (While answering Provide Example)

Q113. Can we navigate from graph how? (While answering Provide Example)

Q114. How do you navigate from report? (While answering Provide Example)

Q115. I have a parent report with Region and sales. Once i click on a particular Region                Value it should drill down to other report with Region and Pending order, for that               particular region I clicked on. How to achieve this? (While answering Provide Example)

Q116. What is the difference in Report prompt and dashboard prompt?  (While answering Provide Example)

Q117. What is guided navigation (While answering Provide Example?)

Q118. How to attach document in the Dashboard (While answering Provide Example)

Q119. How to Create Combined Request (While answering Provide Example)

Q120. How to use Filter based on another request (While answering Provide Example)

Q121. What is narrative view?

Q122. I want to remove the download to excel option in the dashboard Reports - how (While answering Provide Example)

 Edit Dashboard -> select Property of Report -> Select Report link -> uncheck the download option.

Q123. How to create dashboard?

 Settings -> Administration -> Manage Interactive Dashboard -> Create Dashboard -> Give the dashboard name and builder to create.

Q124. How to give access to the different dashboards to different groups (While answering Provide Example).

 Settings -> Administration -> Manage Interactive Dashboard -> Select Lock Symbol for the specific Dashboard and give access to the users.

Q125. How to deny Permission to use answers to a particular group. (While answering Provide Example)

 Settings -> Administration -> Managed Privileges -> Access -> Access to Answers

Cheers!

Bose

SQL Query for Top 10 Product Sales

Here is the Oracle Query to find the Top 5 Products sales amounts from the table

Synopsis:

SELECT * FROM
(
  SELECT PROD_ID, SUM(AMOUNT_SOLD) AS "AMOUNT SOLD"
    FROM SALES
GROUP BY PROD_ID
ORDER BY SUM(AMOUNT_SOLD) DESC
       , PROD_ID ASC
) A
WHERE rownum <= 10;

Cheers!!!
Bose

Oracle / SQL Server Query for MTD (Month to Date), YTD (Year to Date), Rolling 12 Months, & Prior Year Sales

Recently I came across an interview and I had been asked for write an SQL Query from the table for Month To Date and Year To Date for sales amount from sales table.

Here is the synopsis:
If using Oracle use the trunc and numtoyminterval functions; if using SQL Server use the datediff function.
 
Oracle Month to Date:

SELECT sum(SALES_AMOUNT) FROM SALES WHERE DATEOFSALES BETWEEN trunc(sysdate, 'MONTH') and sysdate

Oracle Year to Date:

SELECT sum(SALES_AMOUNT) FROM SALES WHERE DATEOFSALES BETWEEN trunc(sysdate, 'YEAR') and sysdate

 
Oracle Rolling 12 Months:

select sum(SALES_AMOUNT) FROM SALES WHERE DATEOFSALES between trunc(sysdate - numtoyminterval(12, 'MONTH'),  'MONTH') and sysdate

 
Oracle Prior Year:

SELECT sum(SALES_AMOUNT) FROM SALES WHERE trunc(DATEOFSALES, 'YEAR') = trunc(sysdate - numtoyminterval (1, 'YEAR'), 'YEAR')

SQL Server Month to Date:

SELECT sum(SALES_AMOUNT) FROM SALES WHERE datediff(month, DATEOFSALES, getdate()) = 0 AND DATEOFSALES <= getdate()

 
SQL Server Year to Date:

SELECT sum(SALES_AMOUNT) FROM SALES WHERE datediff(year, DATEOFSALES, getdate()) = 0 AND DATEOFSALES <= getdate()

 
SQL Server Rolling 12 Months:

SELECT sum(SALES_AMOUNT) FROM SALES WHERE datediff(month, DATEOFSALES, getdate()) <= 12 AND DATEOFSALES <= getdate()

 
SQL Server Prior Year:

SELECT sum(SALES_AMOUNT) FROM SALES WHERE datediff(year, DATEOFSALES, getdate()) = 1

Hope it is useful.
Cheers!!!
Bose

Creating Briefing Book in OBIEE 11g

Briefing Book:

A briefing book is a collection of static or updatable snapshots of dashboard pages, individual analyses, and BI Publisher reports.

If your organization licensed Oracle BI Briefing Books, you can store a static snapshot of dashboard pages or individual requests in one or more briefing books. You can then download and share briefing books for viewing offline. Briefing books and their content can also be updated, scheduled, and delivered using Oracle BI Delivers.

To create an Oracle BI Briefing Book

1. Navigate to a dashboard in Oracle BI Interactive Dashboards and then perform one of the following actions:
• Click Page Option and then click the Add to Briefing Book button, available as 4^th option of Page Option in dashboard page.   

NOTE:  This button is not available on an empty dashboard page. Click the Add to Briefing Book link that appears with an individual request on the dashboard.

2. Once we are clicked with ‘Add To Briefing Book’ New pop up window will open like below;

 For Content Type, choose one of the following options:

• Snapshot. This adds the content in its current state. Snapshot content preserves the original data and is not updated when the briefing book is rerun. Snapshot content will not be updated using Oracle BI Delivers.
• Updatable. The content is refreshed whenever the briefing book is downloaded, or when it is specified as the delivery content for an iBot in Oracle BI Delivers.

 For Follow Briefing Book Navigation Links, choose one of the following options:

• No. Briefing book navigation links will not be followed.
• Yes. Briefing book navigation links will be followed.

3.  Then click ‘Browse’ with above screen to add this Dashboard page to Briefing Book. If Briefing Book is already created then Select that book and add this page otherwise browse to the location where you want to save the briefing book and give the new name for briefing book and click ‘OK’ to save.

4.            And then click ‘OK’ with next Screen

We will be getting a confirmation popup window like below after successful creation of Briefing Book

5.   Click the Cancel button to return to Oracle BI Interactive Dashboards.

 This creates an empty briefing book. The briefing book folder appears in the selection    pane in Oracle BI Answers and Oracle BI Delivers.

To add additional Dashboard pages add the same briefing book, Click the Add to Briefing Book link or button, and then select the Briefing Book which we have created or create new Briefing Book using the preceding steps.

6. If you wish to add only particular saved request from the dashboard not the entire page then Follow the below steps. Click the Page Option from the Dashboard and then click ‘Edit Dashboard’ Option

7. Once you are edited the dashboards then go to the section where the saved requests or analysis is available which you want to add to the briefing book. Click Section Properties and then Click ‘Report Links’ Options

From the ‘Report Links’ Option, select customize  and then select ‘Add to Briefing Book’ option and then Click ‘OK’.

Now Save and Run the dashboard.

8. Now if you are looking below the saved requests or analysis in dashboard you could able to see ‘Add To Briefing Book’ option. Click that option and follow the   same steps to add that report to Briefing Book

Congratulations !!! With the above steps you have successfully created Briefing Book in OBIEE 11g.

Adding Briefing Book in Dashboard

1. After successful creation of Briefing Book, Edit the dashboard page where you want to add the Briefing Book. Drag ‘Folder’ object from Dashboard Objects to the Column or Section or new Page where you want to add the Briefing Book

2. Once we are clicked ‘Folder’ Properties ‘Folder Properties’ pop up will open

 

Browse the folder where we have saved the briefing book with the ‘Folder Properties’ Window and select ‘Expand’ Option.

3. Now Save and Run the dashboard and check the changes in the dashboard. You will be able to see the newly added Briefing Book in the dashboard.

4. OBIEE 11g is supporting only .mht and PDF format to download the Briefing book. Right click on the Briefing book from dashboard and select further option to edit or download the briefing book.

 Happy Blogging!!!

Bose