Month: August 2014

How to create an Average Aggregation in SQL Server Analysis services?

Standard

Problem:

How do create a measure that does an average over a field from fact table? You can’t find it the “usage” property while trying to create a new measure:

SQL Server Analysis Services Average Aggregation

Solution:

Before i show you the solution, I want you to know that this is a Level 100 solution to get you started – so depending on the complexity of your cube the calculated measure that you are about to create may or may not perform well – if it does not perform well, you might have to dig a little deeper and here’s one blog post to get you started: URL

OK, back to topic! Here are the steps.

SCENARIO: you need average of Sales Amount.

1. Create a SUM OF SALES AMOUNT measure

Steps: Open cube > Cube Structure > Right click on Measure Group > New Measure > Usage: “SUM” > Source Table: Pick your Fact Table. In this case let’s say it’s Fact Sales > Source Column: In this case, lets say it’s SALES AMOUNT

2. Create a COUNT OF SALES measure (important: row count vs. non empty count – this is not a developer’s choice, a business user needs to define that)

Steps: Open cube > Cube Structure > Right click on Measure Group > New Measure > Usage: count of rows OR count of non empty values (again this is not developer’s choice, a business user needs to define this) > Source Table: Pick your Fact Table. In this case let’s say it’s Fact Sales > Source Column: In this case, lets say it’s SALES AMOUNT

3. Create a Calculated Measure that equals (SUM OF SALES/COUNT OF SALES)

3a. Switch to Calculations section > create a new calculated member:

SSAS Analysis services new calculated measure

3b. Complete Name, Format String & Associated Measure Group. For the Expression, use the following expression. Please use this as a starting point for your measure:

[code language=”SQL”]
IIF([measures].[COUNT OF SALES]=0,0,[measures].[SUM OF SALES AMOUNT]/[measures].[COUNT OF SALES])
[/code]

4. Before you test it, if you don’t need the SUM OF SALES AMOUNT and COUNT OF SALES measures than don’t forget to hide them!

Conclusion:

In this post, you saw how to define a measure with average aggregation is SSAS.

SQL Server Analysis services warning: “The name specified for the attribute relationship differs from the name of the related attribute”

Standard

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):

ssas attribute relationships cube dimension3. 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.

ssas analysis services attribute relantionship propertiesThat’s it, this should fix the ssas warning message now since the name specified for attribute relationship would now match related attribute.

SQL Server Analysis services warning: “The name specified for the attribute relationship differs from the name of the related attribute”

Standard

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):

ssas attribute relationships cube dimension3. 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.

ssas analysis services attribute relantionship propertiesThat’s it, this should fix the ssas warning message now since the name specified for attribute relationship would now match related attribute.

Business Intelligene Dashboard for Quality Managers

Quality Test Results Dashboard
Standard

Business Goal:

Need to understand the patterns in Quality test results data across all plants.

Summary:

– The solution involved creating a Business Intelligence system that gathered data from multiple plants. I was involved in mentoring IT team, development and end-user training of a Business Intelligence Dashboard that used SQL server analysis services as it’s data source.

– Dashboard development involved multiple checkpoint meetings with business leaders since this was the first time they had a chance to visualize quality test results data consolidated from multiple plants. Since they were new to data visualization, I used to prepare in advance and create 3-4 relevant visualization templates to kick off meetings.

Mockup:

(it is intended to look generic since I can’t discuss details. Also, drill down capabilities had been added to the dashboard to go down to the lowest granularity if needed)

Quality Test Results Dashboard

Business Intelligence Dashboard for Plant Managers (operations focused):

Standard

Business goal:

Plant managers needed a centralized automated solution that helped them monitor key metrics (operations focused) to help them better manage manufacturing plants.

Technical Summary:

– Work with the plant managers to identify key metrics & calculations to be displayed on dashboard

– Work with the IT managers to identify data source systems.

– Develop the Dashboard using SQL Server Reporting Services. (Built iteratively by making sure to have three checkpoint meetings with plant managers while working with IT/Business-Analysts to ensure data integrity)

– Developed drill down reports see detailed data at plant and machine level.

Mockup:

Plant Managers dashboard operations manufacturing

Power Pivot: How to get Month Name from a date field?

Standard

Problem:

How do you get a Month Name from a date field in Power Pivot?

Solution:

here’s a code snippet that should help:

[code language=”SQL”]
=FORMAT([date],"MMM")
[/code]

This should give you month names (Jan, Feb, …) instead of integers that are returned by the MONTH function.

couple of notes:

1. date field needs to be used to get the month name

2. MMM needs to be in uppercase.

I hope this helps.

SSAS Joining Facts at different granularity to a single dimension:

Standard

Problem:

You have a Fact Sales and Fact Target in your data mart. Fact Sales stores values are product sub category level and fact target stores values at product category level because business sets “sales targets” at a higher (rolled up) level. How do you connect it to a single dimension at different granularity?

Solution:

Here’s the table structure, I just made this up for the demo purpose:

Fact Sales Table

1. Fact Sales

Fact Target

2. Fact Target

Product Sub Category Table

3. Dim product sub category

so, you went ahead and tried testing by creating relationship’s to single dimension at different granularity in the cube:

SSAS Dimension Usage RelationshipsNote how the relationship was specified between Fact Target and Product Sub Category Dimension – it’s joined at a different granularity compared to fact sales. it would be help you from a performance standpoint if the fields that you are using to join the fact and dimension is an int.

SSAS Relationship Dimension Usage Regular

So, you browse the cube and here’s what you get:

Excel SSAS Analysis Services

Note the problem: the target values are being repeated for sub categories but that shouldn’t be happening, right? that’s misleading to business users…ok, to recap what we need to do here: hide target values for subcategories since targets are not set at that granularity. but we do need to show them if the business users pulls in product category.

So here’s a measure group property that comes to the rescue!

Go to Fact Target Measure group’s property > Set IgnoreUnRelatedDimension to False

SSAS Ignore Unrelated Dimension Property

deploy and browse your cube again, here’s what you will see now:

Excel SSAS Analysis Services Pivot Table

That’s it! you have successfully joined facts at different granularity to a single dimension.