Hi All
I am designing sales universe. There is a situation where i have 3 fact tables and several lookup tables.
Fact table A > talks about all the sales data which has 3years of data and measures which include net qty , sale qty etc.
Fact table B > talks about all the sales too in addition to the item level description which is not available in fact table A. and this is a monthly feed.
Fact table C > talks about the chargeable sales in addition also talks bout list price and measures that were in previuos fact tables.
this is a 10 years data.
there are some similar tables that joins all these fact tables.
what is the best possible way of desiginig the universe?
should i include all the fact tables together in a single universe?
Can we join multiple fact tables? there are about 10 to 13 columns in all the each fact tables that matches the other two fact tables?
is it a good solution to include all the facts in a single universe?
Nothing wrong with that at all, so long as it satisfies your user requirements.
No. It looks like your FACT tables are saisfying different levels of aggregation, so look at using the aggregate navigation function when designing the universe. You will also need to look at using aliases and contexts.
There is a raft of information on this site regarding these, so have a search. Have you attended a training course in Designer yet?
I wouldn’t recommend it. You will more than likely return incorrect results, particularly if they are at different levels of aggregation.
If the schema and tables are designed correctly, then you shouldn’t need to do this anyway.
If your users will never require data from all the FACT tables in a single report , then seperate universe is easier to implement
If they do require the data from multiple fact tables in the same report , eg: summary with detail report , then you need to create separate contexts for each fact table.
Create one context at a time , and check you have no loops in each by using aliases to reuse common tables eg Country, Calendar
As stated by the previous person.
Do NOT join fact tables together , especially if the data is of different levels of granularity , as you will get the wrong results.
Mmm the more universes you have the more confusing it can get for the end users, and the harder it is for you to maintain. If you have lots of universes it be difficult to know which one to use to answer a specific question.
If the Fact tables relate to a particular business area then I would put them in the same universe, irrespctive of whether the they will be used in the same report.
If it’s a different business area then I’d put them in different universes.
I do agree with you – users can be easily confused by most things …
I too prefer to have a single universe where possible , and let BO take care of creating seperate SQL queries for each defined context.
I have a problem something similar to this. I have two fact tables and two child tables in my univrese. Both the fact tables share common dimensions and almost all the columns are similar.
i have four contexts for each fact tables and child tables. I’m getting my measures from the alias of fact tables. Now users wants to see data from both fact tables in a single report. i did in canned reports using merged dimensions in th report level.
But the problem here is , they want it in adhoc environment. how can i accomidate this. what will happen if i join both the fact tables. how should i change my design to achieve this.
You need to look at defining your contexts and your aliases.
Two Fact tables? Two Dimension tables? I’d expect only two contexts for this, not four! If done properly, your report will spilt the measure objects from your FACT tables into two SQL scripts on your report.
This is a very simple explanation:
Imagine your four tables: FACT1 on the left, FACT2 on the right, DIM1 at the top and DIM2 at the bottom with joins going from
FACT1 <= DIM1
FACT1 <= DIM2 This is context1
FACT2 <= DIM1
FACT2 <= DIM2 This is context2
Pulling dimensions from your DIM tables and measures from your FACT tables will generate two queries at the report level. You mention having aliases too so accommodate these into your contexts.
I knew i was not clear enough in expressing my issue.Let me be more elobrative.
Actually i have
2 fact tables F1,F2
2 detail tables D1,D2 (earlier i mentioned them as child tables…SORRY )
2 alias tables of the fact tables to get my measures AF1,AF2
and
i have 13 dimension tables which are common for both the fact tables.
now this is how i have my 6 CONTEXTS IN MY UNIVERSE
1.F1-D1-DIMENSION TABLES
2.F1-D2-DIMENSION TABLES
3.F2-D1-DIMENSION TABLES
4.F2-D2-DIMENSION TABLES
5.F1-AF1-DIMENSION TABLES
6.F2-AF2-DIMENSION TABLES
i use context 5 and 6 when i am pulling measures in my report.
now the issue here is user wants to pull data from both the fact tables into same report. how can i accomidate it.
hi ,
i agree with you . but my thinking, will get improper values for measures when they are pulled from the context with detail tables in it. so i created alias table of fact from which i get my measures.
Question, what is the problem in having two detail tables in same context. I had them in same context, but my admin asked me to put them in different context during the review :?
it is star schema. But other than fact and dimension , i also have detail tables in my universe. they are just lookup tables which contains the details of the facts.
there is a possible fan trap when i have a lookup table in a context and im pulling measures from the fact. so i have alias table in a seperate context for measures.
so, i got two context for two fact tables. one alias each. no detail table in this context.
say
1.departed
2. arrived
Another four context without alias but with detail tables for each fact tables seperate.
This post is getting too confusing Kumar with all the different terminologies. If you are using a dimensional model, why do you need lookup tables in your universe?
What do you mean by detail tables?
Please ask your Data Warehouse Architect or your team lead to provide you details of what is the vision for reporting out of these star schemas. You wanna have only thoise tables in your universe which are actually needed for reporting
I’ve seen a star schema where the fact table had lots of foreign keys to dimension tables, then “facts”, and then a foreign key to another table that had a one-to-one correspondence to the fact table – which had additional attribute data for the detailed transaction.
If that is what you are talking about, then you can join your fact table to that attribute table which has a one-to-one correspondence. It’s not another fact table – it’s more like it’s an extension of the fact table.
I agree that you can join fact table to any lookup table which is a one to one, but, if the dw architect is following standards, then those lookups should have become regular or junk dimensions or snowflakes.
It sounds like the detail/look up tables are really a mirror of the fact tables but with descriptions e.g. to help debugging if so these should not be in a universe as the same data exists in the dimension. Ask the admin person to explain the relevance of these “detail” tables.
We have similar model here, again I would say the general rule of thumb is No contexts equals = No fact tables. But if you have a view of a fact table then it is an extra context.
Now for a question I have 2 fact tables with two different contexts, if I now pull a report with just dimensions BO client says please choose client. Is there anyway (avoiding adding extra tables, shortcut joins etc.) that I can say this context is the default context?
(BOB member since 2004-01-29)
(BOB member since 2004-01-29)
(BOB member since 2005-08-19)
(BOB member since 2007-01-12)
)
now this is how i have my 6 CONTEXTS IN MY UNIVERSE
(BOB member since 2007-01-12)
. but
my thinking, will get improper values for measures when they are pulled from the context with detail tables in it. so i created alias table of fact from which i get my measures.
with all the different terminologies. If you are using a dimensional model, why do you need lookup tables in your universe?
(BOB member since 2004-06-02)
(BOB member since 2002-06-17)
(BOB member since 2004-06-02)