First and foremost, let me introduce myself. My name is Pete and I have been working with data for about 20 years…most of that within the Healthcare setting and reporting. I am somewhat new to Business Objects aside from creating Crystal Reports. I am currently being tasked with some administration duties of BusinessObjects BIP (Currently 4.1…but soon moving to 4.2). One of the things I am being tasked to do is in taking a look at some of the universes that are pre-existing.
I searched for a topic similar and only came up with the following:
My question should be a simple one…
Consider say a questionnaire. We have a table (A) that contains all of the answers. And a table (B) That contains the user and questionnaire instance.
I notice that in the universe, the builder created 25 alias tables of (A) and then the join would include the filter for the questionnaire question.
So…the join for line 1(question 1) would look something like this…
B.quest_id = A1.quest_id and A1.line = 1
We have 25 of these tables each with a different join (line number)
So this is a question of Best Practices…
Is this the best way to do this???
Or should there be 25 Derived Tables that only brings in the appropriate Line???
The answer really depends on how the data is going to be used. I think most of us would gasp at a universe with 25 aliases of the same table. There might be a legitimate need for it, but more likely it could be done more efficiently in a different way.
As to your question, yes you probably could use DTs instead, but that wouldn’t get you much. You would just have 25 DTs instead of 25 aliases. And at least with the 25 aliases, you have a single conformed dimension (the user info).
I believe they are creating a dimension(s) based on, in this case, a “Sensitivity Result”
so there can be up to 25 separate results and each result has 5 items each of which, they are creating a dimension upon.
so, we have a table of 5 items for Result 1, a table of items for Result 2, a table of 5 items for result 3, etc…(so essentially 125 dimensions if my math is correct…lol)
That is what I am seeing…
This design is quite different that what I was exposed to before, where we would transform data and move into tables with a scheduled ETL…Much smaller in the amount of tables, still a lot of data…In here they seem to do all of the transforming with alias tables…
So we have some OLAP Cubes using a Kimball modelling method…and I think they are considering designing a cube for this same data (not my decision…just relaying)…My thought is that maybe this should be denormalized and create a derived table of 126 columns (1 ID column and 125 dims) (Or do the work outside of the universe in a SQL Server and alias that table once…)
Given what you have described, I think you have about the most efficient universe design you are going to get.
I would not go the derived table route, especially your idea of one large derived table. When you use a derived table, you are essentially running that SQL and experiencing the cost of it every time you run a report. I use derived tables when I need to, but I try to stay away from them because of that.
You last suggestion of doing some denormalization work outside of the universe and just referencing the table once might be a better option if you gain any performance improvement from it, otherwise I wouldn’t bother with it.
(BOB member since 2002-08-29)