I am new to BOE. I need to add one more fact table to the universe.
Already it has multiple fact tables and dimension tables assosiated with it.
my new fact table do not have any dimensions. issue is, when i am designing report based on join its giving multiple rows as its not getting joined with dimensions which are assosiated with the old fact table.
Please help me to get the details from old & new fact tables and dimensions.
You need to be little more clear. A Fact table is usually joined with dimension tables on the dimension keys (conformed dimensions, in case of Universes with multiple Fact tables) present in Fact table. What kind of a report are you trying to develop? Will it have data from other Dimension tables as well or just from this new Fact table? The data in the Fact table will make sense when you can view it against some dimensions.
In universe Fact1 with dim1, dim2, dim3 & Fact2 with dim4, dim5, dim6 are available.
Now i need to add one transaction table to generate the report from Fact1, dim2, dim3, Fact2, dm5 & transactional table attributes what i added.
i joined Trans1 to Fact1 & Fact2. But Report is not considering join and its giving fact1, dim2, dim3 (without considering join)data in one table and remaining data in another table.
WARNING, you might be in for a mess:
Directly joining fact tables with each other usually will lead to fan traps (which will cause inflated/incorrect measures).
As a rule of thumb: Each fact table with its directly related dimension tables (meaning no join path via another fact table) needs to be separated by using universe contexts.
I suggest getting an experienced universe designer to help you with adding this transaction table.
Firstly, in standard star schemas, you don’t generally join fact tables. You should connect Fact tables using conformed dimensions, the dimension tables which are shared between multiple Fact tables. What you are getting is probably ‘Synchronization’ - a result when a dimension table is unique to a Context and not shared among fact tables.
Consider two fact tables - F1 and F2 and you have 2 dimension tables (Say - D1 and D2) connected to both of them. There is first Context with tables - F1, D1 and D2. There is another Context with tables - F2, D1 and D2. Now, when you use all these tables in report - you do get a logical relationship as both the dimension tables are connected to fact tables.
Now, consider a case when F1 is only connected to D1 and F2 to both D1 and D2 (in their respective Contexts). In this case, a single query spanning all tables doesnt make sense because there is no logical relationship between F1 and D2. So, you would end up with a ‘Synchronization’ where two queries will be generated, displaying data in separate tables (which is exactly what’s happening in your case).
I have done as below in dev.
In same Universe, i have taken aliases for Fact1 & Dimension tables and i joined with my trans1 table and i created context.
Lokanatha, I would suggest you to first model your design (the way the tables needs to be connected). You need to clearly identify the logical relationship and the data flow.
As shared before, usually, fact tables are joined to dimension tables, and are separated out in different contexts. As a rule of thumb, for each fact table, you should create one Context (Fact table + associated dimensions).You should use conformed dimensions to connect fact tables.
What tables/joins have you included in the Context?
My Requirement is i have one transactional(T1) table to which i need to join one fact table(F1) which already in link with some dimensions D1, D2.
i have few more (D3,D4)which are linked with another fact table (F2). i have common columns for both the fact table. now i need to retrieve the data from the F1,D1, D3 & T1. With out T1 i will get the data without any issues as already contexts have been defined.
But i need to add my T1 to the above and generate the Report ?
(BOB member since 2008-08-28)
(BOB member since 2005-03-01)
(BOB member since 2002-06-20)
(BOB member since 2008-08-28)