.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

Different attribute name in each dimension for a role playing dimension

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
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?

View Complete Post

More Related Resource Links

filtering a role playing dimension

I have a role playing dimension and need to run a query that will select the intersection of members that are common on a single attribute...    So Dim1 and Dim2, they both have the attribute [Name] (though the fact table has two different foreing keys mapping to the single primary key on the dimension table) I need to select the members where both foreign keys on the fact table map to the same member in the dimension table so for example,  Dim1.Name = 'John Smith' and Dim2.Name = 'John Smith' Any ideas? Javier Guillen

Role playing dimension and member naming question.


I have a fact table with invoice information that has multiple date columns.

I had originaly only needed to join my time dimension to this fact table on it's create date, but I have now added a role-playing dimension to join to the invoice date.

When I had 1 date dimension all of it's members where called 'week','year', 'day', etc.
Now that I have the role-playing dimension I have two dimensions with member names like 'Date.week', 'Date.year', 'Date.day', 'Invoice Date.week', 'Invoice Date.year', 'Invoice Date.day'.

So many queries I had written to reference the original date dimension no longer work because of the extra 'Date.' prefix. Is there a way to hide this prefix for my original date dimension?

Thanks in advance.

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

Dimension design: Key column of non-key Dimension Attribute

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.

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.

Cross Dimension Role Security

Hello guys,





The Problem:

As you can see the DimSeller is related to the DimCustomer by a non key attribute called "CNPJ", my question is how can i define role security based on that dimension attribute.

For example:

Im a customer with the cnpj 1234, and when i want to see the seller cnpj i can only see the "rows" that the Customer.CNPJ in the DimSeller is equal to my cnpj. 

My future needs i will associate the cnpj with a claims autentication user so than i can use that on sharepoint. At this page I found something like i need, but there i should repeat the steps for each attribute of the dimension, and that would be very hard.

Anyone have some suggestion wich is the best approach for doing this?

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?



Dimension browse using Role fails with Error retrieving children

Hi, this concerns SSAS 2008 R2. I've created a test user and I want to give this test user re-only access to a cube and all of its dimensions. So, I created a Role as follows: General: Checked the Read Definition for database permissions Membership: users and groups set to Domain\Testuser Data Sources: (cube data source name), Access None, Read definition (grayed and checked) Cubes: (cube name), Access Read, Local Cube/Drillthrough Drillthrough, Process (unchecked) Cell Data: all blank Dimensions: All database dimensions, Access Read, Read Definition (grayed and checked), Process (unchecked) Dimension Data: (default, all checked) Mining Structures: (default, all blank) The Testuser can browse the cube using Management Studio, no problem. This includes access to all dimension members and browing the member hierarchies (as part of the cube). However, Testuser cannot browse the dimensions. The primary error is a blank pane saying that "the dimension does not contain hierarchies". So, I investigated. While logged on as Testuser, I looked at the properties of the Role as displayed in Management Studio. No suprises until I got to Dimension Data. Then, for each dimension, instead of the list of members and checkboxes in the main display pane, I saw "Error retrieving children: The '$dimensionname' object was not found. Parameter name: index" (where dimension

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?



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.





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.

Restrict SSAS dimension hierarchy to show based on role


I am having an issue that involves SSAS and Sharepoint.  I don't think I can fix the issue in Sharepoint, I think it has to be in my ssas cube.  THe issue is that in sharepoint I have a ssas filter webpart that displays the geography hierarchy based on the role that is defined in SSAS.  So if I have a user that only has permissions to Switzerland than they will see the geography hierarchy as (Region, Sub Region, Area, Country)

All Sales Region


         Eastern Europe



What I want to know is in SSAS can I restrict the hierarchy to only show country if the user belongs to a certain role.  So what I want to basically say is if the user belongs to SSAS_CH then the hierarchy should just show Switzerland, not All Sales Region > Europe, etc....

Can this be done?



How can we show certain no of dimension only for two different users having different role in a sing



well i might be having a security problem so i just want to know that can we show certain dimension to one user say  (i.e 10dimension for A user and 15 dimension for B user having different user role in a single cube.)

Please any one help me out soon and give some ideas...so that i can solve my issue ,can we solve this issue naa.. :( ..!!!


Anil Maharjan

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