SQL Server Analysis Server

Time Intelligence in MDX: last N days

Standard

it’s a common requirement to create a report that shows last N days of a business metric – so I thought I’ll post a template here for SQL server analysis server’s MDX query:

[code language=”SQL”]

WITH
MEMBER [Measures].[Sales_last_15_days] AS
Sum
(
{
[Calendar].[Date YYYYMMDD].CurrentMember.Lag(14)
:
[Calendar].[Date YYYYMMDD].CurrentMember
}
,[Measures].[Sales]
)

MEMBER [Measures].[CurrDate] as
"[Calendar].[Date YYYYMMDD].[" + Cstr(Year(Now())*10000+month(now())*100+day(now())) +"]"

SELECT
{
[Measures].[Sales_last_15_days]
} ON COLUMNS
FROM
[CubeName]
WHERE
STRTOMEMBER([Measures].[CurrDate])

[/code]

Here are things that you’ll need to adjust to make it work for your scenario:

1. Date Dimension Attribute & it’s format. The example shows yyyymmdd but you could have different format of the date.

2. Measure name. Instead of [Measures].[Sales] you’ll have to replace it with your business metric. Also, make sure you are using the right aggregate function, in the example above I have used SUM but you’ll have to change this based on your requirement.

3. Create a parameter and use it in index for the Lag function.

4. change [cubename] to your cube name.

I hope this gives you a good starting point to create last N days for your business metric.

SQL Server Analysis services – How to set the order by attribute sort key?

Standard

Problem:

How to sort the dimension attribute by something other than the key and name column? How do you set the “OrderBy” property?

Example: You have created an Inventory age buckets 1-50,51-100,101-150 and so if a business user uses this dimension attribute then the sorting won’t be logical. It would be 1-50, 101-150,51-100 – so how to show the buckets in the logical order?

Solution:

1. make sure that the table/view that you are bringing in has the sort key.

Example:

1 SSAS Attribute order by sort key2. Now, switch to SSAS and open your dimension. I am assuming that you’ve already configured your data source views and you are already bringing in these columns in the dimension:

Dim Inventory SSAS SSIS VIEW Data source VIEW

3. Let’s start with hiding Aging Bucket Sort key so that it’s not visible to user. Change the AttributeHierarchyVisible to False

4. Now, switch to Attribute Relationships – Right Click on Aging Bucket and click on New Attribute Relationship. And set the attribute relanship between Aging bucket and Aging Bucket Sort Key

Attribute Relationships SSAS

And you should see something like this in your attribute relationship section:

SSAS Attribute Relationship Sort Key

5. Now, one more thing to configure. Go back to dimension structure section. Open the properties section for the Aging Bucket Attribute and change the OrderBy property to AttributeKey. Also, change the orderByAttribute property to Aging Bucket Sort Key (in your case, choose the sort key that you have)

SSAS Order Sort by attribute property

That’s it, after you process the model then you should see the attribute being sorted based on the sort key that you had.

Conclusion:

In this post, you saw how to configure sort/order property of a dimension attribute.

SQL Server Analysis Services (SSAS) multidimensional cube: How to create a role playing dimension?

Standard

Summary:

I was recently asked by a SSAS developer about how to set up a role-playing dimension so I did a brief demo to show how to set it up. In this post, I am document the steps to help you get started:

What is role-playing dimension?

What if you want to see sales data by Order Date as well as Shipped Date? You can one date dimension that is used more than once for seeing sales data by order date and shipped date. In this case, date dimension becomes a Role Playing Dimension.

Steps:

1. Open SQL Server Data Tools and SSAS project that you are working on.

2. Go to Data Source Views from the solution explorer.

3. Make sure that the relationships are specified between dimension (e.g. date dimension) and Fact Table (e.g. Fact Sales). Example: If you’ve OrderDateSK and ShippedDateSK in your fact table, then establish a relationship between the both columns with the Primary Key of the Date dimension.

SSAS Data Source View Role playing dimension

Note: If you do not have the relationships specified in the data source views, there’s an alternative to go to dimension usage for a cube and setup role playing dimensions. since this is targeted to get you started, I am not covering the steps for that.

4. Now go to the Dimensions section in the solution explorer and add a Dimension that uses the existing data source view from step #3.

5. switch to Cubes.

Now, if you don’t have a cube created yet then you can use the cube wizard if you want and as long as you have the relationships specified at DSV’s, it should create a role-playing dimension.

Alternatively, you can also create a new dimension. In the Design pane, lower left section, you’ll see dimensions.

Add Dimensions Cube SSAS

select your dimension that you created in step #4 that’s a role-playing dimension from the list and click on OK. And it creates a role-playing dimension for you! So all you had to do was to make sure the relationships are created between the fact and (role-playing) dimension table and SSAS does the rest of the work for you

SSAS Role Playing Dimension

 

Note: for the demo I didin’t rename the dimension attributes but you do all that good stuff too when you are building the cube for users!

Conclusion:

In this post you got started on how to add a role-playing dimension in a SSAS cube.

SSAS Multidimensional cube: How to solve duplicate attribute key error when attribute is non key?

Standard

Problem:

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.

I hope that helps.

SQL server analysis services MDX: Current Fiscal Period vs Previous year same Fiscal Period Query

Standard

Problem:

Comparing data current fiscal period vs previous year same fiscal period query is a very common problem. Here’s a sample query.

Solution:

Please use the query as a staring point for current fiscal period vs previous year same fiscal period. I used the Contoso Cube to come with the query. The key part is to use the PARALLELPERIOD function.

[code language=”SQL”]

with member [Sales Amount Previous Year Fiscal Month] as
SUM(ParallelPeriod([Date].[Fiscal Month].[Fiscal Month],12,[Date].[Fiscal Month].CurrentMember ),[Measures].[Sales Amount] )

select
{[Measures].[Sales Amount],[Sales Amount Previous Year Fiscal Month]} on 0,
NON EMPTY {[Date].[Fiscal Month].[Fiscal Month]} on 1
from
[Sales]

where
[Date].[Fiscal Year].&[2008]

[/code]

MDX current year vs previous year same fiscal periodConclusion:

In this post, I shared a MDX query that you can use for current fiscal period vs previous year same fiscal period.

SQL server Analysis Services: How to import cube meta data to SQL server Data Tools?

Standard

Problem:

Someone on your team deployed a cube and gave you access to it. Now, as an IT developer on the team you want to know the calculation that was used for some of the measures. How do get it? You can email the contact person, right? Sure, but let me show you how you can decrease email back and forth using the following steps.

Note: it’s applicable to both multidimensional model as well as tabular model.

Solution:

1. Open SSDT

2. File > Project > New

3. Installed Templates > Business Intelligence > Analysis services > Import from Server

SQL Server Analysis Services Business Intelligence Import

4. You’ll get a dialog box, enter server name and the cube/model metadata that you want to import

Import Microsoft Business Intelligence analysis cube

5. In the solution explorer, you should be able to see the cube meta data! Now, you can go look at the measure calculation that you wanted to see, or may be the KPI rule? or something else in the cube that you needed to know.

Conclusion:

In this post, I described how you can import SQL server analysis services cube/model from the server.

SQL Server Analysis Services MDX: How to display last/rolling 12 months trend in MDX?

Standard

Problem Statement:

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?

Note: It’s not a rolling 12 month sum. If you’re looking to do that, please refer: Sum of Last N Years, Quarters, Months, Periods.

Solution:

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!

SQL Server Analysis Services MDX: Sum of Last N Years, Quarters, Months, Periods.

Standard

Problem Statement:

create a calculated measure in MDX to compute last 3 years Sales Amount

Solution:

Here’s the MDX code to calculate sum of sales amount for last three calendar years:

[code language=”sql” gator=”false”]

with member [measures].[Sales Amount Last Three Years]
as
SUM({[Date].[Calendar].CurrentMember:[Date].[Calendar].CurrentMember.PrevMember.PrevMember},[Measures].[Sales Amount])
select
[measures].[Sales Amount Last Three Years] on 0
from
[Adventure Works]
where
[Date].[Calendar].[Calendar Year].&[2008]

[/code]

Results:

MDX Caculated Measure Sum of sales amout LAST N PERIODS

I hope that gives you a good starting point to calculate the Sum of Measure during Last N Periods.

Where can you find key SSAS MDX cube objects in the Metadata pane?

Standard

Where can you find key SSAS MDX cube objects in the metadata pane? Following picture should be of help:

1.  Cube

2. Measure Group

3. Measure

4. Dimension

5. Attribute Hierarchy

6. Attribute

7. Attribute Members

 

SSAS Cube Objects Meta Data Pane

 

SSAS MDX: Alias for a Measure Name

Standard

I was working on a requirement that needed me to write MDX code that had an alias for a measure name. If it was SQL, you could just do something like select [column name] as [new column name] from [table name] but in MDX it’s not straight forward. What you could do though is declare members in your MDX statement. so to alias a measure name & your code would look something like:

[code type=”sql”]

with member [measures].[new name] as [measures].[old name]
select
{[measures].[new name]} ON COLUMNS,
{[Date].[Calendar Hierarchy].[ALL]} ON ROWS
FROM [Cube Name]
[/code]

if you’re executing MDX code using OpenQuery, then you’ve one other option, something like this would work:

[code type=”sql”]

Select "[measures].[old name]" as "[measures].[new name]" from openquery(ConnectionName,’select {[measures].[old name]} ON COLUMNS from [cube name]’)

[/code]

Note: After taking inputs from business, Naming should be taken care in the cube. My requirement was to pull data from multiple cubes & so I was aliasing it so that I could standardize the names across different cubes.

I hope this gives you a good starting to alias names while writing MDX code.