In this post we will see how to address the SSAS warning message: “The name specified for the attribute relationship differs from the name of the related attribute”, it’s not a critical waning but it’s always good to make sure that these warnings are addressed before going to production.
Usually this happens because attribute names were renamed after the relationships between attributes had already been defined.
To fix the warning messages:
1. Go to Attribute Relationships section for the dimension.
2. In the lower right corner, you should find list of relationships.The ones that cause the warning would have a blue squiggly line with a warning symbol on the arrow (example shown below):
3. Right Click on the Relationship > Go to Properties > Change the Name property to the new renamed name that you gave to the attribute – it should be what’s shown in the Attribute property.
That’s it, this should fix the ssas warning message now since the name specified for attribute relationship would now match related attribute.
In this post we will see how to address the SSAS warning message: “The name specified for the attribute relationship differs from the name of the related attribute”, it’s not a critical waning but it’s always good to make sure that these warnings are addressed before going to production.
Usually this happens because attribute names were renamed after the relationships between attributes had already been defined.
To fix the warning messages:
1. Go to Attribute Relationships section for the dimension.
2. In the lower right corner, you should find list of relationships.The ones that cause the warning would have a blue squiggly line with a warning symbol on the arrow (example shown below):
3. Right Click on the Relationship > Go to Properties > Change the Name property to the new renamed name that you gave to the attribute – it should be what’s shown in the Attribute property.
That’s it, this should fix the ssas warning message now since the name specified for attribute relationship would now match related attribute.
In SSAS multidimensional cube, while processing a cube, you get an error/warning for a non-key “price” column that says:
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘Table Name’, Column: ‘Price’, Value: ’29’. The attribute is ‘Price’.
You see, if it would complain about a key column having duplicate attribute that would have been fine but it’s complaining about duplicate attribute key when an attribute is non key. How do you solve this?
Solution:
mostly this is because that the view/table that you are using in your data source views have blanks/nulls.
so in your source, use ISNULL/COALESCE TSQL function before bringing data in the cube OR if it makes sense for you then you can filter out blank data too.
One of the common business requirement for Dashboards is to be able to see last/rolling 12 month trend on a chart. To meet this requirement, you’ll need to get a data set from the data source and if it’s a SSAS cube, How do you get last/rolling 12 months using MDX?
Here’s the pseudo code, I hope it gives you a good starting point:
[code language=”sql” gator=”false”] SELECT NON EMPTY { [Measures].[MeasureName1], [Measures].[MeasureName2] } ON COLUMNS, { [Fiscal Period].[Fiscal Period] } ON ROWS FROM (SELECT STRTOMEMBER(@FiscalPeriod).Lag(12):STRTOMEMBER(@FiscalPeriod) on 0 FROM [CubeName])
[/code]
Note the use of sub select & query parametrization. You’ll need to pass in current month value in the fiscal period parameter. I hope this helps!
In this series, I intend to document common questions asked by Power users about Excel connected to SSAS cubes (or data warehouse) after they go beyond the basic stage of understanding & using Row labels, column labels, report filter & values in Pivot Tables. This post is #4 of N:
a. How to Filter Measure Values?
Report Filters are a great way to put constraints on the data that is displayed. So once a user gets the concept of selecting dimensions as report filters & slicers, they might start wondering how to filter measure values? How do they show a list of customers that have ordered less than $100,000 worth of products? They might try to drag a measure to the report filter section but that won’t work & Excel will throw an error. So how can users filter measure values? it’s a feature of Pivot Table called “Value Filters” and that would be helpful here. Here are the steps:
1. Select the Pivot Table.
2. click on a drop down menu besides “row labels”
3. After that, go to Value Filters. You can see that you can filter by applying different rules like Top 10, less than, greater than, etc. Let me demo “Less than or equal to”
4. On the next dialog box, you can select the measure, rule & the filter value:
After you’ve set them up, click on ok and the Pivot Table will filter by measure values.
Note: Be careful when sharing the files that have value filters with other users, let them know that you’ve value filters set on the report as the value filters are not clearly visible if you’re looking at the file for first time.
b. How to move a Pivot Table?
If a user is trying to create a dashboard using excel, they’ll have more than one Pivot Table in a sheet. In that case, they’ll need to move the Pivot Tables around. here are the steps:
1. select the Pivot Table.
2. In the menu bar, go to Pivot table Tools > Move Pivot Table:
3. select your NEW destination and click on OK.
That’s it!
Conclusion:
In this post, we saw how to filter measure values & how to move Pivot Tables.
User’s use more than one end-user tool to access the cube.
How do you track the usage by End-User Tool?
Problem:
By Default the SSAS Trace does not capture all End-User Tools like Excel, SSRS, Third-Party Tools.
So what do you do?
There’s a connection string property called Application Name that you can use to capture this information. Once you set it, the application name property will start showing up in the SSAS Trace:
you’ll have to modify ALL connection strings used to point to the cube though.
3. Right Click on the Relationship > Go to Properties > Change the Name property to the new renamed name that you gave to the attribute – it should be what’s shown in the Attribute property.
That’s it, this should fix the ssas warning message now since the name specified for attribute relationship would now match related attribute.
