SATYA RANKI REDDY OBIEE SOLUTIONS

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