OLAP Dimensions

Last Post 21 Jul 2005 07:35 AM by Shill_SQL. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Shill_SQL
New Member
New Member

--
21 Jun 2005 02:54 AM
We have a dimension that is made up of 4 levels
Major Group
- Minor Group
-- Product Description
--- Product Code.

When this dimension is processed against a fact table containing 120 million records the size of the dimension is approx 325MB and takes approx 2 1/2 hours to process after the data file has finished processing.

In the design of the dimension how easy is it to concatenate values together so if I merged product code and description together reducing the dimension structure by a level would this reduce the time taken to process the dimension?
SQLUSA
New Member
New Member

--
15 Jul 2005 12:39 AM
Very likely. Can you try it and let us know?


Kalman Toth, SQL Guru
http://www.sqlusa.com/ordergrandslam/

Shill_SQL
New Member
New Member

--
21 Jul 2005 07:35 AM
I'm in the process of developing the dimension however after some thought I am going to need 3 dimensions using the example highlighted but displaying the data in differing ways.

Creating 3 distinct dimensions is not the correct way as it means that there is more to process.

The dimensions I want to create consist of the following breakdowns:-

Major Group
- Minor Group
-- Description

Major Group
- Description

Description

Is a dimension with multiple hierarchies the correct method for the result I wish to achieve?

Would appreciate any thoughts.
PaulMcKibben
New Member
New Member

--
24 Aug 2005 06:14 AM
I would suggest multiple hierarchies.

I have dimensions with the product code as the leaf node and have concatenated the description wtih the code for display purposes. Here is a sample of the code that is in the Member Name Column:

"dbo"."Item Values"."prdcommcode" +'-'+ "dbo"."Item Values"."prdcomcddsc"

Hope this helps
Shill_SQL
New Member
New Member

--
25 Aug 2005 03:00 AM
Paul,

I tried using multiple hierarchies, at least what I thought were multiple hierarchies, and my actions increased processing time when I tried it, is this right?

I created the dimensions as described in Technet and used the sample MDX application to make sure that it recognised what I had done, so it looked right from that view.

Where am I going wrong?


Acceptable Use Policy
---