MDX

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.

How to Configure SQL Server Analysis services’s Action to Open an URL?

Standard

SSAS Actions are powerful! You can open web pages, open sql server reporting services, customize drill through reports among other things using actions. In this post, you will see a common requirement from users to navigate to a corporate intranet site from the cube – and usually it needs to be dynamic.

For example, user is interested in seeing the Order Entry Page hosted on the corporate intranet site by using the Order ID from the SSAS cube.

Here’s how you can set it up:

1. Open SSAS Cube in SQL Server Data Tools:

2. Navigate to Actions tab:

ssas url action analysis services sql server web page

3. Here you’ll see three types of action that you can configure

a. Standard (this have five subtypes including the URL action)

b. Drill Through

c. report action

4. For the purpose of this blog post, let’s focus on standard action:

ssas url action analysis services sql server web page5. Once you click on the “New Action” it will ask you to configure the action:

a. Name: Enter the desired name here

b. Target Type: In this case, Order ID is an attribute member but you will have to choose appropriate target type for your scenario

c. Target Object: In this case, it’s something like [Order].[Order ID] – in your case, you’ll have to choose an appropriate target object

d. Type: URL in this case (also don’t forget to check books online for what other types can do as well)

e. Action Expression: the format of the Action Expression if it’s driven by a parameter would go something like:

[code language=”SQL”]
"http://servername/site/Pages/OrderRef.aspx?Search&ID="+[Order].[Order ID].currentmember.member_caption
[/code]

f. Additional Properties: I like to set the Caption to clearly indicate the user that they are opening the “Order Form for Order ID 123999”. You can do that by setting the caption property. The format goes like this:

[code language=”SQL”]
"Open Order Entry page for Order ID: "+[Order].[Order ID].currentmember.member_caption
[/code]

Also set the caption is MDX to True if you are using above format.

That’s about it, don’t forget to test it (after deploying the cube) using excel or other end-user tool of your choice. In the Pivot Table, use the Order ID attribute in Row/Column labels > Right Click on any attribute member of Order ID attribute > Additional Actions > The caption with dynamic order id should show by here for users to click and navigate to the specified URL:

excel ssas url action analysis services sql server web page

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.

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.

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 MDX: How to write a query that works as LIKE operator in SQL?

Standard

Problem Statement:

In MDX, How to search for a sub-string using something similar to LIKE operator in SQL?

Solution:

here’s an example using Contoso Sales Cube, please use this to get started for your scenario:

Note the use of INSTR function.


select
[Measures].[Sales Amount]
on 0,
Filter(
[Geography].[Region Country Name].[Region Country Name]
,instr([Geography].[Region Country Name].CURRENTMEMBER.MEMBER_CAPTION,'United')>0)
on 1
from
[Sales]

SSAS MDX analysis services like SQL

Also,
if you are looking for NOT LIKE then replace >0 with =0 and you should get the desired results.

Conclusion:
In this post, you saw how to write a MDX query that does sub-string search for you similar to LIKE and NOT LIKE operators in SQL.

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.