Sunday, July 8, 2012

Implementing Multiple Fact tables in OBIEE Repository File

 



 

Many of time people ask me some way to create the data model consists of multiple Fact table so I though I should create a simple example for people who are new to OBIEE. The tutorial follows step by step approach to  create a Data Model (RPD) with multiple fact tables.

   The example I’m going to use has two fact tables and three dimension tables.

Dimension Table :
Period_days, Products, Stores, Region

Fact Tables : Sales_Fact, Cost_and_Prices_fact.

Physical Layer : Picture Data Source with Multiple Fact Tables.

Step #1 Import the Metadata Source File->Import->Data Source


Step #2 Select the DSN and give the user credential to connet to Data Source


Step#3 Select only the below tables from the data source.[Delete the remaining one for this example we don’t need anyother tables.]




Step#4 : Now drag the Dimension and facts tables in BMM layer as given in the below diagram.
   As we have two fact tables and we suppose to have a star schema. We need to combine two fact tables into one. This we’ll do by adding a addition sources in Sales Fact Table. Just drag and drop the  
 Cost_and_Prices_Fact over the source of Sales_fact.
As seen in the below diagram the Product dimension is a confirm dimension(Bridge Dimesion)



Step#5 : The next step is to add logical sources(double click on logical source) of sales_fact


Step#6 : Make sure all columns are mapped correctly.

Step#7: Finally you’re BMM Logical Model should look like this


Step#8: In the below diagram is it crealy shown that the measures are coming from two different fact tables.


Step#9: Now simple drag the BMM Sales BMM to Presentation Layer.


Step#10: The next step is to run the “Consistency Check”


Step#11: Make sure there is no error.


Step#12: In order to deploy the RPD file we need to make the changes in NQSConfig.INI file as given below in the diagram.


Step#13: Now restart the services.


Step#14: Finally we’ll run the Answer and check the query is working correct.


Step#15: Login to the BI Answer.


Step#16: The presentation layer may look like this.

Step#17: Drag and drop the column from the presentation layer and create query like this.


Step#18: Process the query by clicking on Results section to get the output like this.


Step#19 : Now let us see the query the BI Server fires again Database.



As it is clearly visible in the log file that the Actual SQL query which fires again RDBMS consists of three tables (Two fact tables and one dimension table), however the logical query shows as if there is only one table exist(Main S Subject Area) in the above example.

Thanks,
Satya Ranki Reddy

3 comments:

  1. Thank You. Great article. However, the pictures are too small to follow. That completely takes away the value or use of the article. Low resolution makes it nearly impossible to see clearly. Please post high res or zoom enabled pictures. Also please provide a zip file with script for creating tables and loading sample data, RPD, log file etc.

    Shankar

    ReplyDelete
  2. Have you copied the content from the following URL and posted in your blog?
    http://www.scribd.com/fullscreen/27269046?access_key=key-wekcdahcdwkcsy4911g&allow_share=true

    ReplyDelete
  3. Screenshots are not clear,especially the last one,Could not able to see the query. Please post the screenshots in bigger resolution. Thanks very much for the information provided.

    Pavan

    ReplyDelete