Disclaimer

All of the topics discussed here in this blog comes from my real life encounters. They serve as references for future research. All of the data, contents and information presented in my entries have been altered and edited to protect the confidentiality and privacy of the clients.

Various scenarios of designing RPD and data modeling

Find the easiest and most straightforward way of designing RPD and data models that are dynamic and robust

Countless examples of dashboard and report design cases

Making the dashboard truly interactive

The concept of Business Intelligence

The most important concept ever need to understand to implement any successful OBIEE projects

Making it easy for beginners and business users

The perfect place for beginners to learn and get educated with Oracle Business Intelligence

Tuesday, February 15, 2011

How to join 2 tables conditionally

In a project, more often than none that it is desired to join a dimension table to a fact table based on certain conditions instead of a direct join.

For example, I have a time dimension table that keeps two calendar codes and we only want the data that its calendar code is '001' to be matching with the data in the fact table, otherwise for certain product or location, it's measure would be counting both calendar code.

Now before we proceed, let's look at another option of doing this. We can apply a filter on the time dimension table to filter out all the records that it's calendar code is '001' and then join this table to the fact. Note that if done this way, then the filter will be applied to this dimension and will not be changed. So what if this time dimension is a part of a conformed dimension that joins to two fact tables that one fact that does need both calendar code to be counted? Therefore, we will see if we can change the join logic between the time dimension and the fact table.

So let's look at the below data model:


So let's change the join between Retail Sales Calendar Time and A_RTL_Sales_Audit_AGG

Instead of using a foreign key join, let's use a complex join in physical layer (Yes, I just broke the best practice rule again!):



In the expression builder, enter the following code:
CASE WHEN SUBSTRING("Retail Sales Calendar Time".RETAIL_YEAR_WEEK_TYPE_CD FROM 7 FOR 3) = '001' THEN "Retail Sales Calendar Time".RETAIL_WEEK_ENDING_DATE END = A_RTL_SALES_AUDIT_AGG.CW_ENDING_DATE

Here what I am doing is looking up the column "Retail Sales Calendar Time".RETAIL_YEAR_WEEK_TYPE_CD and if the last 3 digits of that column is '001', then use the column "Retail Sales Calendar Time".RETAIL_WEEK_ENDING_DATE as the joining column, otherwise the join will not return any matching data.

Let's run some test:



And the resulting SQL generated by this query:


However, if the report is using measures from another fact table that doesn't want this condition to be applied:



Then the SQL generated as a result will not have the above condition applied to the query:



Thanks
Until next time!
Related Posts Plugin for WordPress, Blogger...