.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Dimension design: Key column of non-key Dimension Attribute

Posted By:      Posted Date: September 13, 2010    Points: 0   Category :Sql Server
Assume I have a product dimension where key dimension attribute is product code - this is a very large dimension with more than 1 million members, the key column for this attribute is productID (integer). There will be other attributes in this dimension related to key attribute.   My question is about the key columns to be defined for these other attributes. As it is related to key attribute, it has to include ProductID as part of key and hence forming a composite key - e.g. Product inception date - the key column for this has to be ProductID + date as many products can have same date. This design will violate the best practice recommendation from MS to have only numeric key columns for very large dimension attributes. But I can't find a way around this and assume this will be case for all non-key attributes in very large dimension. Is my understanding right? I assume this will be an issue for everyone? Any standard way to get around this? Or best to leave it as such? Thanks in advance.

View Complete Post

More Related Resource Links

Get SSAS Dimension Attribute SQL Column Name using C#



I am trying to determine what the real SQL Server Column name is for a Dimension Attribute using ADOMD.net, however all I am able to find is the the TABLE_NAME and COLUMN_NAME Columns using the GetSchemaDataSet("DBSCHEMA_COLUMNS", null) function.

Does anyone have any suggestions?



Getting counts by 2nd Date Dimension Attribute with Snapshot Style Fact Table

  I have an MDX question finding hard to solve.  I have a Snapshot Fact Table with a snapshot of the records in the source system for each batch date.  All records in the fact table are assigned the batch date with the batch date key.  There are many records for each day and each batch date is an entire copy of the source records.  So, the grain of the fact table is one record for each batch date that exists in the source system.  These facts rows have another date in them for when the record was entered.  This date is different from the batch date in that the batch date is based on the day the batch was processed and the entered date is based on when the record was entered.  If a record was entered many days before, its batch date will be today but its entered date will be several days ago.  Therefore each day a copy of all the records entered the previous batch date and all the records added on today's batch date are present. Fact Table : FactSnaphshotKey (surrogate for easier administration) BatchDateKey (link to batch date dimension – date dimension, first in dimension list so it is used for semi aggregate measures) EnteredDateKey (link to entered date dimension – date dimension) Facts Count – measure for fact table - default measure from Analysis Services cube 2 Dim

Different attribute name in each dimension for a role playing dimension

Is it possible to name the attribute differently in a role playing dimension. For example I use the Date dimension as the role playing dimension for Ship Date and Order Date. When I use these attributes on the report, both of them show as 'Date' which is the name of the attribute in the Date dimension. Is there any work around to implement these names differently?

Key Dimension attribute not visible in cube browser

When browsing the cube either through managemnet studio the key dimension attribute is not visible . However when I browse just the dimension, I'm able to see this attribute. Is this the default behaviour ?. Is there any property I need to set to make it visble ?. The AttributeHierarchyVisible property is already set to true.

Add non primary column as key column in Dimension



My dimension table X has primary column as Primary_ID as char(3) column. About 90% of rows in my dimension has value 'Not assigned' and such rows are scattered in table.

I have added one column as OLAP_ID to dimension table X which will assign default '000' value to all 'Not assigned' rows. This will group together all invalid rows.

Fact table has Primary_ID as FK.

I have following questions:

1. Which column should be use as key column in my dimension table.

2. What should be the hierarchy between Primary_ID and OLAP_ID column.

Let me know if you need more details.



Dimension key attribute changes in fact table



How do I need to handle a case in which the fields in a fact table that represent the foreign keys to the dimension tables might change? What kind of process do I have to do to the cube?



key not found for the attribute in dimension


Hi experts,

i have this problem: I have a fact table for sales analysis and i have a dimension for article of sale. The problem is that in the fact table i can find multiple records that not is present in  the table of articles. This condition not is an error because the software for the sales permit to do a sale without an article codified.

How can i solve this problem?

Can i force this error? If i set custom error on dimension with ignore key error enabled, the cube process however go in error

Dimension Attribute Reserve Word in AMO



I am using SQL 2008 R2 Standard SSAS. Has anyone tried creating a dimension attribute with the name "Name" using AMO.

AMODimensionAttribute = AMODimension.Attributes.Add("Name") - This does'nt work.

I get an error that, “dim_xxx” dimension contains a member property with invalid name “Name”. “Name is one of the Reserved Words”.    We can always make sure to check if the attribute name in the OLTP DB is "Name" and then prefix it with some additional characters.  But it restricts that, I can’t create an dimension attribute with the name “Name” using AMO.





Dimension Design Question


Hello All,

a quick question on dimension design...
We have a project detail page a asp.net based web page where admins enter the project details
There is a section in this page for Project Team where we can assign users to different role (like Project manager, Partner etc..)
There can me more than one user for the same role.

Right now we have total of 10 roles defined in the system.

Now the design question is while doing the dimension design do we need to create ten different dimensions? like DimPM, DimPart etc...
or there is any other way to do this by just creating one single dimension.

Basically from the cube we should be able to see who are the PM's, PART's etc for a project

I have two approaches right now in my mind...
First Approach
a) Need ten dimensions for ten roles ex DimPM(PMKey, PMUserID, PMUserName). DimPart etc...
b) Create a Factless table with columns (FctLesskey, ProjectKey, PMKey, PRATKEY etc...)
c) Fact table - ProjectKey, FctLessKey, Amount etc...

Second approach -

a) Create a Role dimension (fill all the roles) and will have columns RoleKey, RoleName
b) Assuming there is a User dimension (Userkey, UserName etc...)
c) FactLess table - FactLessKey, UserKey, RoleKey
d) Fact Table - ProjectKey, FactLessKey, Amount etc...

Please let me which

Personalization Extensions and Attribute / Dimension visibility


Does anyone know if it is possible to hide / show Dimensions, Measure groups, or Attributes via MDX?  I am using personalization extensions (which is awesome) to create "things" on the fly keeping them separate for each client who uses our cubes.  But now we are entering the realm of "we don't want to see that, as it doesn't apply to us" [they came into the picture after so much was already built].  It would be great if I could use some MDX to hide entire things at a pop, and it is already built in.

Does anyone know if this is possible?  Or would I have to use XMLA to accomplish this?  Or would it be better to use the .Net classes (as they provide a safety layer between my code and future changes)?

Thank you in advance,

After adding a new attribute to a dimension and saving, SSAS unprocessed partitions. Can anyone help



After adding a new attribute to a dimension and saving, SSAS unprocessed partitions. Can anyone help me understand why this happened and point to some reading materials for detail? (can't find any...)


SSAS 2008 - Dimension colmun/attribute not visible in cube browser?


Hi, I have one dimension and one fact table in SQL 2008 server...

i.e. DimEmployee (Columns : EmployeeID, FirstName, LastName, DateOfBirth, Addrss, PostCode, MobileNumber, Gender)
     FactEmployeePay ( Columns : EmployeeID, Amount)

When I create SSAS 2008 cube on those two tables (Relationship is EmployeID colmun) , deploy/process project/cube and then I go to "Cube Browser", but in dimension Employee table I can not see any columns other than EmployeeID. Why in this simple cube I can not view other dimension columns like FirstName, LastName,....

Any idea for SSAS 2008?


Need to access MemberValue of a dimension attribute regardless of level.


I have a Product dimension that has an attribute (Std Cycle Time) that is numeric. I need to use that value in a cube calculation.

How can I access the value regardless of how the data is cut?

I tried this method:

  [Dim Product].[Product - Std Cycle Time].CurrentMember.MemberValue
FORMAT_STRING = "#,0.000", 
VISIBLE = 1 ; 

But what I see in a Pivot Table when I pull this value in is 'ALL' unless I pull the Std Cycle Time attribute into the table.

I understand that behavior. If specific attribute is not pulled into the pivot table, SSAS defaults to the ALL member.

Question is I want to retrieve that Std Cycle Time to use it in another calculated member to compute a weighted average. Basically, I want to do this:

 //Compute the Weighted Average of the Std Cycle Time based on the runtime for the given product.

Prefixes on column headings of date attributes using shared dimension


When reusing a date dimension multiple times as in the Adventure Works cube, the attribute names are prefixed with the cube dimension name in the selector screen. However, when they are brought into the query, the prefix is dropped. So "Delivery Date.Date" becomes just "Date". This is also the case in Excel 2007 which can be confusing for users. Any way ar ound this?



Design question for large fact dimension/many to many dimension


I have a pretty typical orders fact table that is aggregated to 1 line per order.

Each order may have between 0-x errors

I implemented this as a many to many dimension with one field in my intermediate fact table being the error number and the other field being the order id

this requires that i have a fact dimension on the orderid(int32) column.

Right now there are about 10m orders per month, putting my annual count around 120m which doesn't leave a lot of room for growth to stay under the ~150m key member (4GB) size limit for a dimension in SSAS.

I wanted to see if anyone had any thoughts on this. I have found 2 different strategies that might work, including using ROLAP For the order dimension or breaking the order dimension into 3 or 4 separate dimensions discussed here



Dimension Design Help


Hi All,

Need some help here...I have two dimension tables

1. DimWorkType

2. DimWorkTypeCategory

The DimWorkType is at lowest level and has the WorkTypeCategoryKey as foreign key.

We have a WorkType category defined in the WorkTypeCategoryTable.

There are two kind of busineess usres who have categorized the WorTypeCategory in the different way.

For example for one set of users we have the mapping like this

WorkTypeCode WorkTypeName  WorkTYpeCategoryKey

AMC                consultant            1

B110               Manager              2

The DimWorkTypeCategory is like this

ID  CategoryName 

1      Business

2      Internal

For another set of users the AMC comes under the category "Internal" and B110 comes under the "Business". Please let me know how we can handle this situation?

Do we need the separate dimension for the another set of users or we can manage in one DimWorkTypeCategory dimension?


Format dimension attribute with leading zero


I have a attribute in a dimension that represents a hour. The value is an integer. How do I format this with leading zeros, as Excel will treat is as string while sorting (meaning 0,1,10,11,12,2,3,...). I tried the formating attribute in the key column in the dimension-attribute. But that has no effect.



ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend