Stand Alone Tables

building semantic layer
1

Hi,

What is the disadvantage in having all stand alone tables in a universe ?

As of I observed the biggest thing is we cannot create adhoc reports.

 Can anyone provide me more info.

Thanks,

Rodger (BOB member since 2007-09-16)

2

There may be no disadvantage. If the table has absolutely no relationship to any other table (never needs a join), then having it “stand-alone” is just fine. And whether a table is stand-alone or not has NOTHING to do with ad hoc. If it’s in the universe, it can be used for ad hoc query purposes.

{note as moderator … moving from General Discussion forum to the Semantic Layer forum}

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

3

Thanks for the reply.

But when I am talking about adhoc queries , I cannot use objects from different tables in a single report right ? If they don’t have join between them ?

Thanks

Rodger (BOB member since 2007-09-16)

4

Hi,

Dont mind if I am wrong but what I know is in standalone tables it still allows us to query and make any joins at the fly but where as in dimensional modeling we cant pull the data if the tables are not joined or not at the same level we get different errors like multivalue, incompatible,context etc. Even I am in the same boat of you, I am also trying to get more pros n cons among these two.

Thanks
Krishna

krishna3 :india: (BOB member since 2007-04-11)

5

In most of the cases we dont design the universe with single table.we will have many tables joined together for the reporting.

End user will not worry about how the data is getting fetched ,he will see whether he has the business metadata needed for his adhoc reporting.

so the universe designer should take this into consideration and make the universe in such way it will help the user for adhoc reporting.

My choice is go for the dimension modelling and design in such a way you dont get any flaws…

Rajasekar :india: (BOB member since 2004-07-16)

6

As a rule of thumb:
One should not have isolated tables in the universe (or islands of tables) A separate universe should be used for those. Exceptions should be justified on a case by case basis.

Note:
I am not talking about special lookup tables created for LOVs, or tables for which only ALIAS tables have been used, etc.

Andreas :de: (BOB member since 2002-06-20)

7

Hi Friends,

So here is my concern - what way dimensional model affects the performance when its 10 or more millions of records, can we give good performance with dimensional model for adhoc reporting. what are the pros n cons of dimensional model over standalone tables in universe.

Thanks
Krishna

krishna3 :india: (BOB member since 2007-04-11)

8

A properly tuned dimensional model will give outstanding performance. I’m still not clear what you mean by stand-alone table, but if you mean all possible fields fully denormalized into one physical table, you will likely end up with a very large, very “wide” table with lots of redundant data, which may actually hurt performance.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

9

Hi Dwayne,

   I mean tables which probably donot have any joins or its like whole data may come from a single table and the data is denormalized, so in this scenario what would you suggest me to take dimensional approach or just isolated tables.When we allow users drilling capabilities will the data come as the data is in very large volumes. Please guide me to take an approach.

:?:

Thanks
Krishna

krishna3 :india: (BOB member since 2007-04-11)

10

If I understood correctly,
You have opportunity to perform dimensional modeling and kind of build start or snoflex schema rather than dumping everything into single table.

I think that will give better performance. As if you have single big table, then maintaining slowing changing dimension is challenge as any time you need to update the Fact table for changing attributes.
Also with proper dimensional modeling and proper database design like using partitions, you can achieve excellent performance.

Thanks & Regards,
Sujit

sujit_patange (BOB member since 2005-07-19)

11

Hi Sujit,

   I have a challenge to convince my team and also Manager with dimensional modeling so how can I support my approach,Please guide me so that I can support my design with best performance. I am worried because of large volume of data. :idea:

Thanks
Krishna

krishna3 :india: (BOB member since 2007-04-11)

12

One of the main drawbacks of a one big standalone table that contains everything is the performance of the list of values because they are populated not from small dimension tables but from that one big table.

Waiting minutes to get a couple of distinct values into LOV could be (for some users) a reason not to use the universe/report.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

13

See closely related thread started by krishna3 here :?

Andreas :de: (BOB member since 2002-06-20)