Questions Power Users Ask about Excel: #4 of N

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?

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.

=Dateadd("d",-1,Now())

Screenshot:
SSRS date time parameter previous day

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

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?

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:


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

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?

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.

Quick note on evolution of Business Intelligence & Microsoft’s vision for BI space:

I attended “Enabling Familiar, Powerful Business Intelligence hosted by PASS BA VC last week & I got to listen to Microsoft where they shared their vision for the BI space, so I thought of posting this quick note about it:

“Corporate BI” has been around for may years. This space has established players like Microsoft, SAP, IBM, Oracle. But in recent times, “Self Service BI” space has been gaining momentum. Players like Qlikview & Tableau that lead the Self Service BI space have been ranked as leaders in the Gartner 2014 magic quadrant. Microsoft has also been making serious advancements in this space since last few years & with their latest offering called “Power BI” they have shown that they putting their bets on Self Service BI space. So, as Microsoft said in the event, they view themselves as the only player that offers a full suite of Corporate BI as well as Self Service BI:

Evolution of BI

you can watch the recorded session here: http://www.youtube.com/watch?v=0yKhxSPlykg

Business Analytics VC event on 2/27: Microsoft SQL Server 2014 Countdown event!

Here are the PASS Business Analytics Virtual Chapter meeting details:

Topic: Microsoft SQL Server 2014 Countdown: Enabling Familiar, Powerful Business Intelligence

Date: 2/27

Time: 12 PM EST

If you’ve not registered for the virtual event, please use the following  URL:

http://bit.ly/SQLPASSBAVC

Event Description:

There has never been such an abundance of available and useful information as there is today both across the web and across your organization. However, users are challenged with effectively discovering and connecting to this information so that they can gain the meaningful insights they need. In this session we will discuss how to empower users with business intelligence through familiar Office tools, and how to manage all your data needs with a powerful and scalable data platform. See the full Microsoft SQL Server 2014 Countdown Webinar Schedule at: http://www.sqlpass.org/SS2014Launch. Please join our experts from engineering and technical marketing teams as they discuss new functionalities and enhancements in SQL Server 2014.

We hope to see you at the event tomorrow!

Regards,
Paras Doshi
VP – Marketing | PASS BA VC

TSQL Script: How to get list of all tables or view in a database?

I was documenting the list of tables/views in a data mart & staging databases & I found the following scripts useful:

TSQL To get list of all tables:

SELECT * FROM <DatabaseName>.information_schema.tables
where TABLE_TYPE='BASE TABLE'

TSQL To get list of all views:

SELECT * FROM <DatabaseName>.information_schema.tables
where TABLE_TYPE='VIEW'

Alternatives (for SQL 2005 onwards):

SELECT * FROM SYS.TABLES

Business Analytics VC: 13 Tips for KPIs with the Microsoft SQL/BI Stack

Online @ http://BAVC.sqlpass.org
12:00 – 13:00 (GMT-05:00) Eastern Time (US & Canada)
OnlineMeetingURL: https://attendee.gotowebinar.com/register/7675239130599046913
Date: Thursday, February 20

Abstract:
In this webcast, I’ll present 13 tips for designing and creating Key Performance Indicators (KPIs) using the Microsoft SQL BI Tools. I’ll cover a bit of practical theory on KPIs and balanced scorecards, and then dive into some specific examples of basic KPIs, trend-based KPIs, data-driven KPIs from actual applications. I’ll also cover some recommended practices for creating KPIs. Along the way, I’ll use SSAS OLAP, SSAS Tabular, Reporting Services, Excel, and PerformancePoint Services to demonstrate the creation and surfacing of KPIs.

About Kevin:
Kevin S. Goff (http://www.kevinsgoff.net) is a Microsoft SQL Server MVP Database architect/developer/speaker/author Regular columnist for CoDe Magazine on .NET, SQL Server, and Business Intelligence topics Frequent speaker at community events in the Mid-Atlantic region. Host of BDBI Radio (http://www.bdbiradio.com), a weekly webcast on SQL/BI topics

Microsoft Self Service BI mentoring for an American Healthcare company

Microsoft Self Service BI mentoring for an American Healthcare company

Business Summary: Need custom reporting based on FWA (Fraud, Waste & Abuse) cases data.

Technical Summary:
- Led a class for reporting analysts to walk them through the process of creating custom reports based on FWA (fraud, waste, abuse) cases data
- Assisted in developing a dashboard for the Team using Microsoft Self Service BI Tools

Mock up:

The underlying model made it possible for the reporting analysts on the FWA team to create mockup’s like one shown below to satisfy their custom reporting needs:

mockup fwa case tracking health care american company