Multidimensional

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.

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.