Month: March 2014

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

SSRS: How do I know where a report item is on the report?

Standard

Have you ever got a SSRS message saying something like textbox107 refers to an invalid measure or measure used in gaugepanel7 is not valid, etc? The problem is how do you find that “Textbox107” report item on your SSRS report? After all, you might have lots of text boxes on your report.

Here’s how you can know where a report item is on the report:

Step 1: Go to view > Properties Window

Step 2: Open the report.

Step 3: if you select a report item then it’s going to show the properties of the report item in the properties pane.

SSRS Properties Window Report ItemStep 4: And you can select a report item by it’s name like textbox107 or gaugepanel11 for which you are seeing an error, that way you can find the location of the report item and troubleshoot it!

SSRS Properties Window Report Item list

 Conclusion:

I hope this tip helps you find the report item on your SSRS reports.

SQL Server: How to insert explicit values into an identity column of a table?

Standard

In a SQL server data mart, it’s common to have an Identity column (SK columns) in a Fact Table. And it’s also common to add a -1 record to this table for “unknown values”. So if you want to insert an explicit value into an identity column in sql server table, here are the steps:

1) In SSMS, select the Table the Object Explorer > Right click > Script Table as > Insert To > “New Query Editor Window”

2) This gives you a nice starting point to created your identity insert script:

3) Add following code before the insert statement:

SET IDENTITY_INSERT <schema_name>.<table_name> ON
go

4) Add following code after the insert statement:

SET IDENTITY_INSERT <schema_name>.<table_name> OFF
go

5) Now modify the “VALUES” section of the insert statement. Also, Note that the identity column would not be in column list, you’ll have add it manually in the script.

After the values are added, here’s what the code should look like:

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

USE
GO

SET IDENTITY_INSERT <schema_name>.<table_name> ON
go

INSERT INTO <schema_name>.<table_name>
([Col1_sk]
,[col2])
VALUES
(-1,
‘N/A’)
GO

SET IDENTITY_INSERT <schema_name>.<table_name> OFF
go

[/code]

Conclusion:
I hope this gives you a good starting point to create an identity insert script in SQL server table.

How to use TSQL checksum to compare data between two tables?

Standard

In any BI project, data validation plays an important part. You want to make sure that the data is right! usually business helps in this validation. As a developer, you might also want to do some preliminary data validation. One of the techniques that I’ve learned recently is to use TSQL checksum to compare data between two tables. In this post , I’ll describe the technique & post a pseudo code.

we’ll create a pseudo code to compare all columns but you should be able to use that to tweak that if you need it.

1) Run checksum(*) on Tables:

On Table1:

select checksum(*) as chktb1 from table1
go

On Table 2:

select checksum(*) as chktb2 from table2
go

At this point, you should get two result sets each populated by checksum values computer over all columns since you passed * in the checksum function.

2) Now let’s join these tables & look at rows w/ different checksum: (in other words, it is going to list all rows that are different between table1 & table2)

select * from
(
select checksum(*) as chktb1 from table1
) as tb1
left join
(
select checksum(*) as chktb2 from table2
) as tb2
on tb1.someid=tb2.someid /* you can have more ids */
where tb1.chktb1 != tb2.chktb2

3) You can add individual column now to see what changed:

select * from
(
select checksum(*) as chktb1, columnname1, columnname2 from table1
) as tb1
left join
(
select checksum(*) as chktb2, columnname1, columnname2 from table2
) as tb2
on tb1.someid=tb2.someid
where tb1.chktb1 != tb2.chktb2

Conclusion:
I hope this helps especially if you don’t have rights to install 3rd party tools on your dev machine.