SQL Server Performance Forum – Threads Archive

Less Pysical Dimensions Needed in SSAS 2005?

I have created numerous cubes based on the Kimbal star schema in SSAS 2000. I would have 5 – 20 dimension tables linked to the fact table (a typical star-schema.) I need to have a link for each dimension to the fact table (manifested by foreign key relationships) because of limitations of the SSAS 2000 functionality. For example, if I had a Sales data mart whose fact table has a gruanular level based on Customer Number (no line items) and Ship Date. I would then have created dimensions based on Customer Shipt-To Location, Customer Type, Shipping Type, ShipVia, Credit Terms, and Ship Date. There are no Type 2 changing dimensions and the full cube is rebuilt each month. In SSAS 2000 I would then break these out in the fact table / dimension table schema so that each of these create dimension tables and add the associated foreign keys to the fact table. I had to do this in order to get the cube to recognize these each of these dimensions and their associated hierarchies. Now comes SSAS 2005, and the way I see it, I can collapse all these logical dimensions (except Ship Date) into one dimension called possibly the "Customer" dimension whereby the join would be with the Customer Key. I can then create many different hierarchies in this one physical dimension that would represent all the physical dimensions that I had created structures for in the SSAS 2000 example above just by using all the attributes associated with the Customer (since they all depend on the Customer, except the shipping date which will be broken out as separate dimension.) I also get the AutoExist functionality at the same time when dragging and dropping these attributes/hierarchies in my OLAP software. When I display the hierarchies in my OLAP software (because of the way that I name my hierarchies), the user cannot tell that I have only one dimension in the cube, if they were to guess, they would probably guess that there are 5 or more dimensions. I have been trying to research this through the new Kimbal/Mundy book, other books and google, but have not seen this new SSAS 2005 functionality and question addressed (although maybe indirectly.) It does seem to certainly collapse the star-schema to much fewer physical dimensions (although logically there can still be the same larger number of dimensions) cube schema. Has anybody had any experience with this powerful functionality in SSAS 2005? Or know of any articles that address this question? Thanks

---

I don’t have experience with SSAS 2005. However, afaik, two main arguments for using star schema are: 1. Having simple, straight-forward, end-user friendly schema for querying.
2. Reducing number of joins in queries to gain performance. I always felt the argument #2 is questionable and not correct in many cases. So if SSAS 2005 alows for simple presentation of data, reducing number of dimensions at the same time I would definitly test two designs for performance and make decision based on results of testing. If you decide to do it, please post results here, I am very curios to know the conclusion. Btw, do you know that you could use mini-dimensions to reduce number of dimensions?

---

Thanks for you comments. I think your answer is probably based on the table schema being access from SQL Queries . The schema that I describe will incorporate regular tables, but these tables and schema are only being used to create SSAS cubes which will be accessed through a software front-end via MDX statements. Once the cube is built, the tables will not be accessed at all, only the cube. We are also not using drill through (which sometimes uses SQL queries to the underlying tables.)

---

Yes, you are right. Unfortunately I don’t have experience with AS, hopefully Raulie or someone else who actually works with it will join the discussion.

---

See thishttp://www.dbtalk.net/microsoft-pub…use/create-star-schema-sql-server-320998.html for relevant information. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Hi,
Consider this example. 1>I have a time dimension as [Time Period]
In this dimension i have created two different time hierarchies.
a)[Year-Month-Day-Hour-Minute]
b)[Year-Week-Day-Hour-Minute] So in MDX whenever you access any of hierarchy member you will access it using notation
[dimension].[hierarchy].[member] right?
So my point is name of dimension in all cases will be same which is [Time Period]. So when you represent your hierarchies on OLAP viewer base them with Dimension name as well, which will let you know that in this example there are not 2 different dimension but one dimension with two different hierarchies.
like
[Time Period].[Year-Month-Day-Hour-Minute]
and
[Time Period].[Year-Week-Day-Hour-Minute]
Hope this helps

---

Satya, I appreciate the link, but found it very general with information I already possess in my discussion. My issue is much more specific than that article addresses. It basically suggests books, all of which I’ve been studying for years and have been researching on this issue, but have not been able to see much in them to elaborate on this question. There was one link another friend of mine sent to me that seems much more in tune with what I am inquiring about (especially the second part of it.) That link is athttp://blogs.conchango.com/jamiethomson/archive/2005/01/06/706.aspx Thanks

---

Spalding, thank you for the link.

---

]]>