SSAS MDX: How to hide null values for months in current fiscal year while comparing the value against last years numbers?

Standard

Let me explain the purpose of the blog post via a diagram:

ssas mdx nonempty month sales amount non empty

The current year selected was 2014 and since it’s 30 MAR 2014 today, it’s not showing data for April and onwards. But for 2013, it’s showing all months. How to hide null values for months in current fiscal year and at the same time show only applicable months for Previous Year? Here’s the pseudo code to get you started:
[code language=”sql” gator=”false”]

WITH MEMBER [Measures].[Prior Year Sales Amount] AS
([Measures].[Sales Amount],[Fiscal Year].CurrentMember.PrevMember)

SELECT
{[Measures].[Sales Amount],[Measures].[Prior Year Sales Amount]} on columns,

NONEMPTY ([Month].[Month],
[Measures].[Sales Amount]) on rows

FROM [CUBENAME]

WHERE
[Fiscal Year].&[2014]

[/code]

Note:
1) Do you see the use of NONEMPTY function? It hides the null values in the [measures].[sales amount].
Resource: Non Empty vs NONEMPTY function.

2) do not forget the { } around the measures that you’re putting on column axis. Otherwise you may see an error like “Parser: The statement dialect could not be resolved due to ambiguity.”

I hope this gives you a good starting point.

Licenses required in Excel 2013 & Office 365 for Power View and/or Power BI:

Standard

Here’s a quick chart that I created based on reading up from different sources about licenses required in office 2013/office 365 to use Power View and/or Power BI. It was not straight forward and I had to use multiple sources to find this information, so hopefully this helps you:

Power View Power BI Excel 2013 License

Please Note:

Double check w/ official sources. The chart is meant to guide to find the right license for your needs. Please use this as a starting point & use official resources before making a purchase decision.

Sources Used:

http://www.microsoft.com/en-us/powerBI/pricing.aspx#fbid=ygmkLW5EEH9

http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2013/02/27/unable-to-use-excel-2010-powerpivot-workbooks-in-excel-2013.aspx

http://sqlblog.com/blogs/marco_russo/archive/2013/08/19/excel-2013-stand-alone-now-includes-powerpivot.aspx

http://office.microsoft.com/en-us/excel-help/power-view-explore-visualize-and-present-your-data-HA102835634.aspx

http://cwebbbi.wordpress.com/2013/02/01/office-2013-office-365-editions-and-bi-features/

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

 

PASS BA VC: Analyzing Road Traffic Accident Data in #PowerBI by Chris Webb #sqlpass

Standard

Join PASS Business Analytics VC on Thu, Mar 27 2014 12:00 (GMT-05:00) Eastern Time (US & Canada) for “Analyzing Road Traffic Accident Data in Power BI” by Chris Webb. In this one hour session, Chris will spend an hour going over the details on how he developed his amazing demo for the Power BI contest (see here & here).

here’s the link to Register: http://bit.ly/SQLPASSBAVC

Demo:

We hope to see you there!

Paras Doshi
VP – Marketing, PASS BA VC

SSAS MDX Query: How to Return Top N products by revenue

Standard

A really quick note about how to write a MDX query to get Top N products (think dimension!) by revenue (Think measure!). Using this query as a starting point, you should be able to write queries that meet your similar business requirements:

Here’s the query structure:

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

SELECT
{[Measures].[Revenue]} ON COLUMNS,
{TopCount([Product].[Product Name],20,[Measures].[Revenue])} ON ROWS
FROM
[Cube]

[/code]

Note the use of Top Count & it’s query syntax. Also note that you can specify other number instead of 20. I hope this gives you a good starting point.

Questions Power Users Ask about Excel: #4 of N

Standard

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”

Excel Pivot Table Value Filter3. 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”

Pivot Table Excel SSAS cube filter values measure4. On the next dialog box, you can select the measure, rule & the filter value:

Pivot Table excel ssas cubeAfter 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:

Pivot Table options Excel Move

3. select your NEW destination and click on OK.

Move Excel Pivot Table 2010That’s it!

Conclusion:

In this post, we saw how to filter measure values & how to move Pivot Tables.

In earlier articles we saw:

#1: How to sort data? How to add slicers? How to change Pivot table Layout

#2: How to add calculated measures?

#3: how to remove grand totals & subtotals and, how to configure the automatic data refresh.

How to set SSRS date parameter default value to previous day?

Standard

Setting the default value for parameters like today’s date & current month is great because that way consumer do not need to manually input the parameter value. I was working on a requirement to pass previous day to the date parameter.

So here’s the quick tip to set the default value of the date parameter to previous day.

[code type=”sql” gutter=”false”]
=Dateadd("d",-1,Now())
[/code]

Screenshot:
SSRS date time parameter previous day