Universe Design Queries

building semantic layer
1

Hi Friends,

I have a query…I have to design a universe from scratch…and this is the first time i am doing it…So I have many questions here in which I need your help.

firstly we have a transactional database with tables and columns…we have been asked to create a reporting database(second layer) which will extract different columns from different tables in the transactional database and dump it in the reporting database to have its own set of database tables and columns… means the tables in the transactional database and the reporting database will be different…We are also creating an SSIS Package(stored procedure) so as to dump the data from transactional database to reporting database.We will be mapping transactional database to reporting database so that the SSIS package dumps the data in the reporting database…Then after this process we are going to pull the tables of the reporting database in to the BO
universe.Now my questions are as follows.

We have already been given the format of the canned as well as the Adhoc reports to be generated…for example we have an excel sheet which shows the Report layout…like which all columns the report should have

1.While creating the SSIS Package we would have made joins keeping the report format in mind so as to create the tables in the reporting database…SO is it necessary to create joins in the universe again?

2.we are working on both Canned and Adhoc Reports…now i have one question regarding this…We make joins in the universe keeping the Canned Reports in mind as they are in a fixed format…But for Adhoc reports Do we have to make joins?..Just think for a minute that the adhoc report has 14 columns and a user wants to see only 5 columns…so he drags and drops the required 5 elements only…it means that the report he is generating is as per his wish…So my question is when we are creating a universe…do we have to make joins only keeping canned reports in mind or both canned and adhoc…what i am asking is do we have to make joins for adhoc reports too?

3.and also we have some aggregate columns in our reports…Should I accomadate all the aggregates in one table in the reporting database? or split them accross many tables wherever it applies to?

4.And My final question is that…The business have given us the structure of the universe design…They have no idea of business objects but they have given us some classes and attributes after going through the reports…But we have a different idea because we know how the tables are related in the database so should we follow their way or should we follow our way of thinking?

Please reply,

Thanks

thehulk200081 (BOB member since 2008-01-13)

2

First off, welcome to BOB.

What follows is my opinion, formed over several years of trying to work with BO. You have a golden opportunity in helping to design the data warehouse. Ususally the DBA’s design it and then you have to live with what they give you.

My first piece of advice is: make sure you design the data warehouse for data extraction, not data input. This sounds too obvious to mention, but believe me, it is not. I have worked on too many systems where the reporting database was designed for input, even when the DBA’s swore it was designed for output. The universe can not be any better than the database it is built on. Ok, there are some tricks that will make it seem better.

WARNING: I am going over the top to make my point. Rant mode ON.

You should not need to worry about if the report is ad hoc or canned. If you do, the data warehouse design is wrong. You should not need to worry about decodes, substrings, case statements, in your joins. If you do, the data warehouse design is wrong. Tables should join one way, no matter what data is being requested. If they don’t, the data warehouse design is wrong. The users should be able to use the classes and objects they provided you with. If they can’t, the data warehouse design is wrong. If the data warehouse is designed correctly, building the universe should be easy and straight forward. If it is not, the data warehouse design is wrong.

Ok, so it is not as straight forward as that. My point is; if you design the data warehouse correctly, everything gets much (much, much, much) easier.

Rant mode OFF.

To answer your questions:

  1. Yes, you must add the joins to the universe. The reports depend on these. They can not access the ones in the SSIS package.

  2. You have to design for both canned and ad hoc and they must be the same. See rant above.

  3. There are a lot of factors to consider and it is not my strong point. I will let someone else answer.

  4. Put a lot of weight on the design they gave you. This is how they plan to be using the universe. This is what they expect the universe to deliver. Of course, it depends on how well they know what they want. Take what they give you and try to make impovements based on what is good for them, not what is good for the warehouse design. Change the warehouse if you need to.

Hope this helps. This is all opinion gathered from the many scars I have obtained fighting the battles that I hope to help you avoid.

Lee Drake :us: (BOB member since 2002-08-15)

3

I actually thought you were quite restrained… :lol: .

Lee, I couldn’t agree with you more on this… 8) .

Lee, I think we all have them…it helps if you have a DBA that can actually speak… :rotf:

Mak 1 :uk: (BOB member since 2005-01-06)

4

Hi Lee,
I think your co-workers must have given you the nickname (tDwDiW == the Datawarehouse Design is Wrong :wink: ) .
It’s utmost important that how you approach towards the provided database / datawarehouse design to you.

While designing the universe you should keep following points in mind to make the universe much more easier for the users to use.

  1. See who is the audiance i.e. if the users are going to be the business users or the people with a little or substantial knowledge of databsess. Here I dont mean the audiance should not have sound knowledge but should know the tbles in which the objects are lieing then in that case you can even just drag and drop the tables in the classes panel. Else If the user is the end user who is not having any database knowledge at all then you have to design the universe which is much more comfortable for the user. i.e design the classes and obejct the way user want to see. In this the class may include the obejcts from different tables as well.
  2. For what ever type of reports you are designing the universe, it should have tables is consideration to be properly joined. Else it will give you incorrect results
  3. You have to consider the issues of traps in the unvierse nad you will have to solve them accordingly.

Hope this helps you.
Do revert back in case any help is required.

Omkar Paranjpe :us: (BOB member since 2006-02-13)

5

Moved to the Universe forum!

Nick Daniels :uk: (BOB member since 2002-08-15)

6

Actually, my nickname is “Mr. Obvious”. If you have not noticed, I am not exactly a subtle kind of guy. I try to be (mostly) quiet and polite, but not subtle. :mrgreen:

Lee Drake :us: (BOB member since 2002-08-15)

7

Hi,
I said so as there were many instances where you said The datawarehouse design is wrong :stuck_out_tongue:

Omkar Paranjpe :us: (BOB member since 2006-02-13)