How to handle Shared Dimensions with Multiple Star Schemas with 1

[dlutzker]

To all,
I am adding several new star schemas to an existing data warehouse (MSTR 7.2.2/Oracle 9i). My predecessor used DB views to handle shared dimensions. Since performance is not great, I thought about using new column aliases (in the attribute form).
Is that a good practice? Will this work? I assume that if I don't handle this somehow that MSTR will blow up somewhere...cross joins across fact tables or some kind of circular join.... Is this a good alternative? Any other suggestions?
Thanks in advance.
Dave

 

[dmcmunn]

Your peformance question's answer is in the "used DB views" vs. replication of data. Adding a new attribute form simply gives you the ability to access a column of data in association with a particular MicroStrategy attribute.

I'm not sure I see how this will have any affect on performance, unless the form being added references a new column in a view which has had a new join referencing one of your new schemas ? Am I getting close here ? Need more input...

Best regards,
Johnny 5

 

[dlutzker]

Johnny 5 - Thanks for the reply. The performance question is to decide between these two options:
[highlight]1. [/highlight]Create another view for each time I reuse a shared dimension in a new star schema.
[highlight]2. [/highlight]I can reuse the dimension by creating a new object (it is redundant, but it would have a different purpose/function). For example, the "Contact" table has customers, students and leads. These would appear in three different star schemas, but all are looking at the same dimension table. I am thinking that if I do that, that eventually someone will need a report that spans two star schemas (wanting facts from two fact tables), and that the SQL engine will generate a loop or a cartesian product.
[highlight]3. [/highlight]By using a column alias. I am hoping that Microstrategy's SQL engine will join the table to itself by using a table alias, and will return the correct result set (based on an inner join between the star schemas)
Any thoughts? Need more clarification?
Thanks.
Dave

 

[entaroadun]

The concept of a shared dimension in MSTR ROLAP is meaningless. You should create a separate table to hold your dimension, separate from your star schema fact tables.

Three potential problems exist if you do a DISTINCT view on a star schema fact table for your dimension:

1. Performance will be horrible.
2. If the fact table you base the attribute on is missing some elements that another fact table has, they will be dropped.
3. If you want to correct for #2, then you have to do a DISTINCT UNION of the multiple fact tables in your view. Performance will be horrible.

Just replicate the data. It's easier. MSTR is not MOLAP, so please don't subject yourself to MOLAP constraints.

Column aliases do not create a separate table alias instance in the SQL. Only a separate logical table object in MSTR will force that. Anyways, it won't fix your performance problem.

 

[dlutzker]

entaroadun - I think you misunderstood me, but your advice may still make sense.
I have dimension tables separated from the fact tables - it is a hybrid star-snowflake relational design. The "Contacts" table, as an example, is used in each of the 4 star schemas (1 existing, and three new ones I am working on now). I am looking for the best way to handle them, keeping performance issues in mind.
Does Microstrategy have table aliases, or allow multiple logical tables for one physical table? Wouldn't that be better than using views (I am not planning on using materialized views for other reasons, though it is becomes a necessity...).
Thanks for your input!
Dave

 

[entaroadun]

I apologize, but I don't think I understand your issue. Please define what you mean by "shared dimension". What exactly does your Contacts table contains, and how are attributes modeled on it?

I don't think you need to do anything special. MSTR will not join multiple fact tables together. If your report has facts from different tables, then all of the facts from each table will be processed in its own pass. The results of each pass then get merged based on the attribute keys.

 

[dlutzker]

Please take a little more time to understand my situation, and I will be happy to learn that there is not issue here...

Sales Schema - has Sales_Fact, Time_Dim, Contact_Dim, etc.

Contacts are people, they order products. They also take exams:

Exam Schema - has Exam_Fact, Time_Dim, Contact_Dim, etc.

The Sales schema was built prior to my arrival, and there are multiple views of each dimension, one for each object: The Sale_Date_View and the Ship_Date_View are identical, consisting of

 Select * from Time_Dim

Each is used to build a different Microstrategy object: "Sale Date" and "Ship Date".

Now I am adding the "Exam Date" and "Exam Cancelled Date" objects in the Exam Schema. Do I need to:
[highlight]1. [/highlight]Create a Exam_Date_View and Exam_Cancelled_View? I know this will work, but I am looking for a better way, in which I will not take a performance hit because I am using views. With that approach, I will end up with a query that joins 5 views to a fact table with several million records.
[highlight]2. [/highlight]Create separate physical database tables - this solution rubs me the wrong way, not only because of redundant data, but redundant ETL objects, jobs, increased load time....
[highlight]3. [/highlight]A Microstrategy solution?? If I could use some kind of alias (logical table, or object alias) which will give me correct results when querying two fact tables with the shared dimensions, I would be happy. Does such a solution exist?

I really appreciate the time you are taking to help me out.
Thanks!
Dave

 

[entaroadun]

MSTR has physical table objects and logical table objects. When you add a table from the DB logical schema into MSTR, MSTR creates a physical table object (i.e. Time Dim). By default, it creates a single logical table object mapped to this physical table object, also called Time Dim.

In MSTR Desktop, you can only see the physical tables in the Warehouse Catalog. In the Schema Objects/Tables folder, you are actually looking at logical table objects.

To create additional logical tables on your physical table, right click on one of the logical tables and select "Create Table Alias". This creates another logical table on the physical table. You can rename this.

Model your new attributes, each on a different logical table. In the Attribute Form display, you will see a list of logical tables that you can attach the Attribute Forms to.

The SQL generated will include a separate instance of Time Dim for each attribute. You won't see the logical table name here, however, only the physical name.

Look, Ma, no views!

P.S. Are you sure that the SELECT * view is degrading performance? Most commercial RDBMSs can see right through it.

 

[dlutzker]

To entaroadun - you have hit the nail on the head!
Your explanation of logical tables, and how to create them is exactly what I wanted to hear. Just as important, the point that you make regarding "select * from..." views is also correct (I have been speaking with our Oracle expert).
Before I wasn't sure how to evaluate my options, but now I can make an informed decision...
Thanks so much!
Dave