Universe Design approach - Dimensional data model

[RamsBI]

We use a Dimensional data model which has about 15 different models based on Subject areas. Eg: Billing, Claims, Eligibility, etc. Each model has its own Fact table linked to Dimensions, some of which are Conformed dimensions which is present in multiple models. We want to build Universes on top of this model, for creating Crystal Reports and to expose it to the Business Users to create WebI reports through InfoView.

The Client has already built 15 Universes one for each Subject area, which has 1 fact table each and many Conformed dimensions with some junk dimensions. When a Report needs data from more than one Universe, we have to link the different Universe queries at Report.
Major drawback with this approach is change management. As our data model will be expanded in future, which in turn makes me to update multiple Universes when, say a Conformed dimension changes; since the Conformed dimension table will be present in multiple Universes.

Now we are considering the below approaches to have better Architectural design and have easier User interface.

1. Creating a master Universe for the Dimension tables(here there may be a effort to modify data model to suit linking Dimension tables together). Then to create derived Universes for each Fact table. These derived Universes will be linked back to common dimension Universe.
Maintenance will be easier in this approach, as whenever a Dimension changes I need not update multiple Universes, but as I am linking Universes at Designer level as Master and derived Universes, I am concerned about the Report development if the report needs data from multiple Universes. Then I would be linking “multiple Linked Universe” queries at Report.

2. The other option I have is to combine multiple dimension models(Subject areas) into one Universe. By this we will create minimal number of Universes as possible. May be end up creating 5 or 6 Universes, but we will have tough time in maintaining Security of data elements. For instance, at high level a Universe may have Billing and Eligibility data, where I have to maintain strict Security for the User groups, and let only specific users to see/ use all data elements (objects).

Hope I have summarized my question well, any inputs from you on the approach you are aware of/ pro’s and con’s of it* in terms of time it takes to build, the performance of Report(creating WebI reports through InfoView) is appreciated !! We want to see which approach makes it better for creating Crystal Reports and when it reaches Business Users who has little patience waiting for a Report and needs best possible interface

 

[hilfy]

Using linking to create master and derived universes would not be too difficult and would definitely be easier to maintain.

We have a unique situation where we are using linked universes quite successfully. Because we are working with loan data from banks each of our clients, by contract, has their data in a separate database. Each client's database has exactly the same structure. So, we have a master universe where all changes are made and derived universes where the only difference is the connection. We do "consolidated" reporting in WebI for our corporate managers that have multiple queries - the same query in each universe. These reports contain both the master and the derived universes and we don't have any problems running them.

Some things to remember when using linked universes:

- You cannot open both the master and one or more derived universes at the same time. You can have either the master or one or more derived but not both.

- Parameters, Context, and Hierarchy do NOT come through the link. You have to manually set them in each derived universe.

- If you use the Import Wizard or the Lifecycle Management tool (available in XI 3.0 and above), you MUST migrate the master and all of the derived universes at the same time. If you don't, the links will be lost and the derived universe won't work.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.