 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Data Warehousing / September 2005How to handle large number of attributes for a dimension
I have a Product dimension on which I expect about 90 attributes mainly they are related to classifying the material. Can somebody please share ideas how I can implement such a large set of dimension attributes any other alternatives you can think off. Sometimes the attrbutes can be dynamic how can I implement such a scenario in analysis services dimensions. To explain the problem in more details: I have an extract provided to me for the product dimension the extract file structure is as follows: Product, AttributeName, AttributeValue --------------------------------------------------------- 110000,Color,Red 110000,Gauge,0.5M 110000,Length,5M 110000,Sealant,F 110000,Market,US 110000,MarketSegment,NA 110000,MarketApplication,102 110000,Industry,Healthcare 110000,PrintDescription,XXXXXXXXXXXXXXX 110000,PrintLabelType,ABC -------------------------------------------------------- Now my challenge with this for a given product I might have anywhere from 10 such attributes to 80 or 90 such attribute name & value pairs. So, given this my cubes currently have data only at a product level I need to report using these attributes. So, I face the following challenges: 1. How do I model this dimension to provide reporting on all these attributes 2. If I model my dimension as follows: Product, Attribute1, attribute2,.....Attribute80 then how do I flatten the data in the above structure and load it so that for a given product I populate the attribute values into attribute1 to attributeN. Not sure how this can be done from DTS or from a Stored Procedure or whatever. 3. Even if I flatten and load the dimension how does the user when he wants all Red colors or 5M length or for a particular mkt segment to do the reporting Storing the attributes in the cube is next to impossible I cannot store 80 or 90 attributes in the cube. I would greatly appreciate if others having faced a similar problem how you have done modelling and handling this kind of variable attribute set dimensions and modelling and reporting of this kind of dimensions. Thanks Karen I'd go CREATE TABLE Attributes ( AttributeId INT NOT NULL PRIMARY KEY, AtttributeName VARCHAR(50) NOT NULL ) CREATE TABLE Products ( ProductId INT NOT NULL PRIMARY KEY, ProductName VARCHAR(50) NOT NULL ) CREATE TABLE Attribute_Products ( ProductId INT NOT NULL REFERNCES Product (ProductId), AttributeId INT NOT NULL REFERNCES Attributes(AttributeId ) ) Note: I don't know your business requirements , does the product have only one attr or many? You can add primary key on Productid and AttributeId otherwise add a column to identify a row. >I have a Product dimension on which I expect about 90 attributes > mainly they are related to classifying the material. [quoted text clipped - 55 lines] > Thanks > Karen But this still does not answer my question. I want to report as I said in the following form: ProductId, Attirbute1, Attribute2...Attributen ........Then the values in the cube attached to the product. Seperating the attributes this way still does not solve my problem Thanks Karen Well, can you please DDL+ sample code+ expected result? > But this still does not answer my question. > [quoted text clipped - 7 lines] > Thanks > Karen have you look at SQL 2005? handling attributes is very easy in 2005. the problem come from the end user interface, today there is no or only a few number of tools on the market. With AS2000, you have to choose the most requested attributes. or, maybe, create a combination (concatenation) of attributes. Like attribute color + inner color become: full color attribute this could reduce the number of attributes without loosing the information. >I have a Product dimension on which I expect about 90 attributes > mainly they are related to classifying the material. [quoted text clipped - 55 lines] > Thanks > Karen Karen, There are two problems here... 1. Maintaining the product dimension itself. 2. Putting it into the cube. In terms of maintaining the dimension table...I have released C source code for handling large dimensions at the database level..... it is on this page http://www.peternolan.com/Downloads/tabid/138/Default.aspx -> various source code -> customer dimension table code. The direct link is here...http://www.peternolan.com/LinkClick.aspx?link=InsuranceCustDimDocs.zip&tabid=138 &mid=564 You can learn all you need from there...though you will have to implement it in something else as MSFT no longer support embedded SQL in 2005. However, this is in the database, not in the cube.....in cube products you generally need to be somewhat careful of building very large and complex dimensions and then building measures for those dimensions.....as volumes and complexity go up performance goes down...and the designer needs to be fully aware of how the particular cube product performs in various circumstances....As far as I am aware this comes from experience with the particular cube product because they are different. What I have generally done is build the underlying dimensional model such that any cube can be delivered. I have not done this with AS. I have done this on many projects with Cognos and Essbase which are similar cube products to AS... Best Regards Peter Nolan www.peternolan.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.