Hello,
I have a question about consolidating Query subjects (Facts or Dimensions) to create a virtual star schema (As View behavior). Is it possible to consolidate the query subjects which has the following cardinality/relationship between them?
1.1 -> 1.n
1.1 -> 1.1
1.1 ->0.n (left outer join)
Your help with this is highly appreciated.
Quote from: Ravisha on 11 Feb 2016 08:45:16 PM
Hello,
I have a question about consolidating Query subjects (Facts or Dimensions) to create a virtual star schema (As View behavior). Is it possible to consolidate the query subjects which has the following cardinality/relationship between them?
1.1 -> 1.n
1.1 -> 1.1
1.1 ->0.n (left outer join)
Your help with this is highly appreciated.
Yes.
MF.
Hello MF,
Thanks a lot for your swift response.
The reason why I posted that question earlier is because, I was struck while creating a model, which has two star schemas having no conformed dimension. The Fact query subject from star schema 1 joins to the other fact query subject (factless fact) in star schema 2 with the cardinality 1.1 -> 1.n
Example: Fact (star schema 1) 1.1 ------> 1.n Fact (star schema 2)
I was thinking to merge the aforementioned fact query subjects into a model query subject and join it to the rest of the surrounding dimensions. Will that work? Kindly please advise me.
Thanks
Howdy
No conformed dimensions? Not even time? Or do you really mean, 'they are conformed but at different grains'?
Assuming the latter, if the query layer has been properly laid out then putting all your facts into one model query subject shouldn't produce a problem although you'll probably put in tables which you don't want, which could hamper performance but that's another matter. Even if that's out of the picture, I don't think there's much point to the exercise other than serving a order fetish. I'm open to the possibility that there is a good reason for doing what you want to do.
If the former, there's even less point to the exercise. That's even if you don't end up with a cross join error. If there's no conformed dimensions what's the point? I wish to learn from your modeling situation and having you explain the rationale might help me.
You later on mention that you're contemplating joining the model query subject in which you want to put all your facts to the dimensions. I'm not sure that I'm happy to hear that. You might want to define the relationships between objects in the query layer. If they are in both places you might miss something or model duplicate relationships.
If you take the Cognos training you'll learn about how to do things like model for multiple facts, multiple fact grains, query planning, and other fun stuff.
My advice would be to for your company to spring for the Cognos training that IBM offers and for a service contract too.
Hi,
Thanks a lot for taking the time to answer my question.
Kindly please take a look at the attached image. In the DB layer, I've consolidated the Fact and the Factless query subjects into a Model Query subject. Then, I've joined the rest of the dimensions to this newly created model query subject and deleted all the other underlying relationships between the dimensions and the two Fact and Factless query subjects.
Something similar like Order Header and Order Detail table consolidated into a Sales Fact model query subject.
Since the cardinality between the Fact and the Factless query subject is 1.1 ----> 1.n, I've consolidate them into one.
Please let me know your thoughts.
Thanks