Many a times i have heard users facing issues with server picking up improper joins in Admin tool. Lets take an example to understand this and lets c the solution….
Take an example that we have 2 dimension tables viz. Day Dim & Opty Dim and 2 fact tables viz. Revenue Fact & Order Fact. Now, I wana make my report in such a manner where i take only 2 dimension columns from the 2 dim tables and no fact column eg: I wana c number of opptys in an year.
Take an example that we have 2 dimension tables viz. Day Dim & Opty Dim and 2 fact tables viz. Revenue Fact & Order Fact. Now, I wana make my report in such a manner where i take only 2 dimension columns from the 2 dim tables and no fact column eg: I wana c number of opptys in an year.
What will happen???
When the report will run, the data will be fetched by joining the dimension(Day Dim) with dimension(Opty Dim) through a fact table(Order Fact or Revenue Fact) .Now, sometimes in these cases the server gets confused that from which fact table it should join so as to fetch the data. Then what the server does, is that, it joins with the nearest fact(eg: Order fact) and pulls the data through it.
When the report will run, the data will be fetched by joining the dimension(Day Dim) with dimension(Opty Dim) through a fact table(Order Fact or Revenue Fact) .Now, sometimes in these cases the server gets confused that from which fact table it should join so as to fetch the data. Then what the server does, is that, it joins with the nearest fact(eg: Order fact) and pulls the data through it.
But this can sometimes causes issues and may fetch incorrect data. Because if the user want to see the data based on the Revenue Fact and server joins with Order fact and pulls the data, then the number of Optys per Year can differ in count as there many be different Opty wids in both the facts.
I guess u got wht i meant….
So, to take care of this issue we can assign an Implicit Fact Column in the Presentation catalog in the Admin tool. This column is generally the lowest granularity column of the fact table like Row_wid etc. By setting this column in the presentation catalog the server takes the join from a particular fact table whoose column is specified in the Implicit fact column for that particular presentation catalog..
We can set the implicit fact column as shown below:
So, to take care of this issue we can assign an Implicit Fact Column in the Presentation catalog in the Admin tool. This column is generally the lowest granularity column of the fact table like Row_wid etc. By setting this column in the presentation catalog the server takes the join from a particular fact table whoose column is specified in the Implicit fact column for that particular presentation catalog..
We can set the implicit fact column as shown below:
User feedbacks are invited and kindly post if you have faced or are facing the similar issue.
Posts
