Hey Guys,
Have always worked wiith IQD's, so new to cube creation from an FM package. Basically, want to use one fact query subject and 2 dimension query subject. The relationship between the 2 dimension query subjects and fact query subject has been defined in FM.
Now, when I create 3 different data sources (same package of course...just "inserting" queries 3 different times within transformer) and build my dimensions from there, then the 2 dimensions generate categories but those 2 dimensions show up without anything under it...looks like a single category themselves. This happens because there is no directly related measure for these dimensions or so is my understanding. But, when I import all 3 query subjects in the same data source import session then the cube build sort of hangs...I started such a cube build yesterday on the server but terminated it after 8 hours.
Anyone have any suggestions?
Thanks for suggestions/help
Keeping those 3 queries seperate would be my choice. The Dimension query subjects should populate the dimensions with the categories and as long as you have those Dimension keys in the Fact query subject, it should roll up the measures propely. I have done lot of this.
Not sure what you mean by the dimensions show up as a single category. Can you post a screenshot? One thing to keep in mind is that in case of multiple data sources, the lowest level of the heirarchy or the level of the dimension that is supposed to be the 'connect' has to be marked as unique otherwise the Fact query will not be able to connect the measure with the categories populated by the dimension query subject.
Regarding your 2nd option to have everything in one query and use that joins of FM, I have seem the server terminating the process because it takes a lot of time just to open the pipe and the process terminates because of the idle time. Not sure if that is hapenning in your case but I would stick with the 1st process you took of having 3 seperate queries.
Did you check the scope . Does it show that Dims and Facts are connected?
Look at the attached screenshots. That's how the Fact will connect with the Dimensions properly with seperate queries.
Once it is mapped properly, the categories will be created as per attached.
When I first switched from using .iqd files, I found this document pretty useful:
http://www.ibm.com/developerworks/data/library/cognos/modeling/transformer/page512.html (http://www.ibm.com/developerworks/data/library/cognos/modeling/transformer/page512.html)
With this, and the suggestions from cognostechie, your cube build should no longer hang!
Rgds, Michael