ABC Classification With SQL Server Window Function

There are many benefits from using Window Functions with OVER clause which are available since SQL Server 2012. In this blog post, we will go through a use case example of ABC Analysis / Classification using a Window Function.

Let us examine the definition of ABC Analysis.

Extracting the definition from Wikipedia, the ABC analysis provides a mechanism for identifying items that will have a significant impact on overall inventory cost.

ABC Analysis Scenario

Marco Russo has posted a very useful ABC classification using Power Pivot here. Using a similar rule, we will classify

  • Products that make 70% of the sales are in Class A
  • Products that make 20% of the sales are in Class B
  • Products that make 10% of the sales are in Class C

So, let’s take a look at how we can apply the new Window Function to do this. In this scenario, we will use AdventureWorksDW2012 to classify all product sales for each month. The breakdown of the logic to perform the ABC classification in this scenario is

  1. Calculate the total sales of each product model.
  2. Calculate the cumulative sales of each product model in the order of the Sales amount (higher to lower values).
  3. Calculate the percentage of the cumulative sales of each product model against the total sales.
  4. Determine the classification based on the percentage of cumulative sales.

In this example below, I use CTE and to calculate the sales of each product model. Step 2 to 4 can be done in one go. Please note that there are many ways of calculating ABC Classification, below is just one of the ways.

 

 

USE AdventureWorksDW2012
GO

WITH ProductSales AS
(
	-- Get the total for each Product Model
	SELECT 
		dp.[ModelName], 
		SUM(ssd.[SalesAmount]) Sales
	FROM [dbo].[FactInternetSales] ssd
		INNER JOIN [dbo].[dimProduct] dp
		ON ssd.[ProductKey] = dp.[ProductKey]
	GROUP BY 
	dp.[ModelName]
)
-- Calculate culmulative total and categorise it based on the percentage.
-- Product Model that have high sales amount and make up the first 70% 
-- will be classified as A, the next 20% is B and the rest is C.
-- Product Models in A generate more revenue.
SELECT
	ps.[ModelName], 
	ps.[Sales],
	SUM(ps.[Sales]) OVER (ORDER BY ps.[Sales] DESC) AS CumulativeSales,
	SUM(ps.[Sales]) OVER () AS TotalSales,
	SUM(ps.[Sales]) OVER (ORDER BY ps.[Sales] DESC) / SUM(ps.[Sales]) OVER () AS CumulativePercentage,
	CASE 
		WHEN SUM(ps.[Sales]) OVER (ORDER BY ps.[Sales] DESC) / SUM(ps.[Sales]) OVER () <= 0.7 
			THEN 'A'
		WHEN SUM(ps.[Sales]) OVER (ORDER BY ps.[Sales] DESC) / SUM(ps.[Sales]) OVER () <= 0.9 
			THEN 'B'
		ELSE 'C'
	END AS Class
FROM    ProductSales ps
GROUP BY 
	ps.[ModelName],
	ps.[Sales];

 

The result looks like this (exported to Excel)

ABC Analysis on AdventureWorks Product Model

 

Have I Done This Correctly?

This example is very similar to Marco Russo’s post on ABC Analysis Using Power Pivot. To check that I’ve done this correctly, I create a Pivot Table based on the table above (no Power Pivot used). The resulting Pivot Table is very simlar to Marco’s one.

Pivot Table of ABC Classification

 

Using PARTITION BY For Additional Context

What if I’m interested in the ABC Analysis of product models for each year? This is simply addressed by using PARTITION BY on Year to get the Year context. In the CTE part, I add YEAR(OrderDate) to obtain the Year that I will be partitioning by.

ABC Analysis per Order Year

 

The code will look similar to this:

 

USE AdventureWorksDW2012
GO

WITH ProductSales AS
(
	-- Get the total for each Product Model and Year
	SELECT 
		YEAR(ssd.[OrderDate]) AS OrderYear,
		dp.[ModelName], 
		SUM(ssd.[SalesAmount]) Sales
	FROM [dbo].[FactInternetSales] ssd
		INNER JOIN [dbo].[dimProduct] dp
		ON ssd.[ProductKey] = dp.[ProductKey]
	GROUP BY 
		dp.[ModelName],
		YEAR(ssd.[OrderDate])
)
-- Calculate culmulative total and categorise it based on the percentage.
-- Product Model each year that have high sales amount and make up 
-- the first 70% will be classified as A, the next 20% is B and the rest is C.
-- Product Models in A generate more revenue.
SELECT
	ps.[OrderYear],
	ps.[ModelName], 
	ps.[Sales],
	SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear] ORDER BY ps.[Sales] DESC) AS CumulativeSales,
	SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear]) AS TotalSales,
	SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear] ORDER BY ps.[Sales] DESC) 
		/ SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear]) AS CumulativePercentage,
	CASE 
		WHEN SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear] ORDER BY ps.[Sales] DESC) 
				/ SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear]) <= 0.7 
			THEN 'A'
		WHEN SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear] ORDER BY ps.[Sales] DESC) 
				/ SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear]) <= 0.9 
			THEN 'B'
		ELSE 'C'
	END AS Class
FROM    ProductSales ps
GROUP BY 
	ps.[OrderYear],
	ps.[ModelName],	
	ps.[Sales]
ORDER BY 
	ps.[OrderYear],
	CASE 
		WHEN SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear] ORDER BY ps.[Sales] DESC) 
				/ SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear]) <= 0.7 
			THEN 'A'
		WHEN SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear] ORDER BY ps.[Sales] DESC) 
				/ SUM(ps.[Sales]) OVER (PARTITION BY ps.[OrderYear]) <= 0.9 
			THEN 'B'
		ELSE 'C'
	END ;

 

The result will look similar to this (I have updated my AdventureWorksDW to start in Year 2001 instead of the default one).

ABC Analysis on Product Model Per Year

 

Wrap Up

A number of Window Functions are very useful for analytics. We use SUM with OVER and PARTITION BY to generate Cumulative Total and to calculate percentages,which then can be used derive the ABC classification.

This post is part of #SQLCoop series of Window Function.

#SQLCoop

I would highly recommend other Window Function articles by #SQLCoop bloggers -

A Date At The End of The Month by Mickey Stuewe

Windows Functions: Who Knew by Chris Yates

Write readable and high-performance queries with Window Functions by Jeffrey Verheul

 

 

 

Further Reading

ABC Analysis in PowerPivot by Marco Russo

ABC Analysis on Wikipedia

SUM (Transact-SQL) on Technet

 

 

 

SQL Saturday #296, Melbourne 2014

You may have probably heard that SQL Saturday is more than spending a whole day at a university, a Microsoft campus or class rooms learning SQL Server. Yes, you have heard it right. It is a community celebration – celebrating about how much we care for our learning and career path with a group of like minded people – at least on the fact that we want to learn more, outside business hours. It is also a reunion with friends or present/former colleagues and a way for you to meet a potential employer, employee or colleagues.

Luckily, the next and closest SQL Saturday in Asia Pacific area is in Melbourne. It’s just less than one week from now.

When? Where?

Visit SQL Saturday #296, Melbourne Australia official website.

When: 5 April 2014

Where: Monash University, Caulfield

Schedule: 5 Tracks, 20 sessions all in one day. http://sqlsaturday.com/296/schedule.aspx

Special Features:

  • Keynote session by Microsoft on SQL Server 2014, Power BI and Azure!
  • MVP Round Table Discussion at lunch time – bringing you 9 Australian SQL Server MVPs all in one location to answer questions you’ve been pondering about and to network with you.

 

SQLSAT296_web

Register here (please pay $15 for your lunch – I promise it won’t be pizza! And $15 for quality sessions for a whole day is really good value!)

Once you have registered, you can use the Schedule Builder feature to choose the sessions that you are planning to attend.

I’ve Registered, Now What?

You can follow on progress of the event by checking the website or following / tweeting with hashtag #SQLSatMelbourne on Twitter.

Please feel free to spread the words to your colleagues via social media or your blog. Below are some handy examples:

LinkedIn – I am looking forward to SQL Saturday #296 in Melbourne, Australia …

Why attend?  20 sessions on SQL Server 2014, BI, Data Warehousing, DBA, Database/Application Development and Database Administration.  International, regional, and local speakers to learn from.  Exhibitors with useful products, information to share, and job opportunities.  Interaction with other data professionals.  Free swag and lots of fun.

Twitter –  I am looking forward to #SQLSaturday 296 Melbourne, AU … 

Please take the opportunity to say “Thank You” for SQL Saturday #296 Melbourne Sponsors. They are the reasons why this fantastic learning and networking event is offered for free (with low cost of Lunch). First and foremost, I would like to personally thank Microsoft for sponsoring us and opening the event with a Keynote session.  as well as Varigence Australia for sponsoring us and speaking during lunch time. Next I would also like to thank Wardy IT, LobsterPot Solutions, DWS, CGI, CU2 Global and Wrox.

See You Soon In Melbourne!

Hope to see you there and perhaps at my “Building Your Myth Busting Lab With Power BI” session on the day.

 

 

VS2012 Ultimate SSIS Start Up Error

If you are using Visual Studio 2012 (VS2012) for Business Intelligence solutions and have SQL Server 2012 SP1 installed, you may be running into a VS2012 Start Up issue. This happened to me this morning when trying to open a non BI solution in VS2012.

It came up with “Microsoft Visual Studio 2012 has stopped working” error message as shown below. I also tried running devenv /safemode as well, but this does not help.

VS2012 Crashed At StartUp

Essentially, the main issue was related to:

System.TypeLoadException: Could not load type ‘Microsoft.SqlServer.Dts.Design.VisualStudio2012Utils

This issue happened on on my machine with SQL Server 2012 SP1 with KB2793634 (v11.0.3128) installed.

After tweeting it online, Jeff Rush (@JeffRush) mentioned a link that suggests SQL Server 2012 SP1 CU4 may need to be applied. Applying SQL Server 2012 SP1 CU4 (and also restarted my machine) didn’t seem to do the trick.  I did some more research and eventually found out that the problem was only solved in SQL Server 2012 SP1 CU5.

Applying the patch (part of hotfix included in SQL Server 2012 SP1 CU 5), 465914_intl_x64_zip.exe has solved the issue. After the patch, my SQL Server 2012 version is 11.0.3373.

Massive thanks to Jeff Rush for giving me a guidance that the issue was probably fixable with a hotfix for SSIS – not sure why I did not think of that. (That’s why Twitter and #SQLFamily rocks!)

Further Reading

Fix: The type initializer for ‘Microsoft.DataTransformationServices.Project.SharedIcons’ threw an exception. (Microsoft Visual Studio 2012) by Roel van Lisdonk

System.TypeLoadException: Could not load type ‘Microsoft.SqlServer.Dts.Design.VisualStudio2012Utils’ on SQL Server Forums

Cumulative Update package 5 for SQL Server 2012 SP1 on Microsoft Support

 

 

WASD with SSMS Tools Pack 2.7.2 Review

I have been using Windows Azure SQL Database (WASD) for a little while now, and I think it would be a good idea to start a WASD Tools Review series on tools that work well for WASD. To kick off the series, I would like to explore top 8 features of SSMS Tools Pack 2.7.2 that have worked well for me in WASD development / administration (which also works well for on-premise SQL Server instances too).

As you may already know, SSMS Tools Pack was originally built for SQL Server 2005 and has since then been enhanced to keep up to date with later versions of SQL Server. If you have not used it before, download it from here and read a comprehensive review from Grant Fritchey.

All the options that are offered by SSMS Tools Pack 2.7.0 work for SQL Server, but not all of them are relevant for WASD instances. For example, the pre-configured “DB – DBCC SHOWCONTIG” custom script. At the time of writing, this option is not automatically checked by the tool for its validity to be run on WASD instances. Running this script would result in the following error:

Msg 40518, Level 16, State 1, Line 1
DBCC command 'SHOWCONTIG' is not supported in this version of SQL Server.

This may change in the future though – if you find that you would like to suggest some enhancements on any parts of the tool, I would highly encourage you to please provide the author (Mladen Prajdić) with some feedback.

 

A Quick Introduction

Once you installed SSMS Tools Pack add-in for SQL Server Management Studio, you can right click on items under the WASD instance on Object Explorer similar to below.

WASD - STP Server Options

Options / configurations are usually accessible from the SSMS Tools menu.

SSMS Tools Menu

 

Below are my top 8 favourite features (not in any particular order) that I use quite frequently or find most useful when working with WASD (equivalently for SQL Server instances too).

1. Create CRUD

This is useful for generating a stored procedure for each Create, Read, Update and Delete actions on data pertaining to each table in the database.

WASD - STP Server Options

Generate CRUD scripts

 

2. Generate Insert Statements

This is a very handy script to export data out as Insert statements.You can also choose select tables instead of the whole tables in the database.

STP - Generate Insert Scripts

 

Below is an excerpt of the generated Insert scripts:

STP Insert Scripts

 

3. Search Database Data

This is probably one of the most handy features out there. As a Consultant, sometimes I have to search a specific keyword and it could  be like looking for a needle in a haystack. This makes my job easier – although probably best not to be executed on a large database that does not currently perform well.

WASD - STP Search In Database

Fortunately, while executing the search, should you find it is taking too long, you can cancel the search.

WASD - STP Searching Database Dialog

Once the search completes, it returns the SELECT statement of where the keyword is found in the data. In the below example, I am looking for any record (in any table / view) that contains the word “Daenerys”.WASD = STP Search Database Results

 

4. Run Custom Scripts

Do you have custom scripts that you run regularly against a server? Well, you can do this for WASD server too, as long as it is relevant. Below is an example of Update Statistics script that has been pre-configured upon your SSMS Tools Pack installation.

WASD - STP Update Statistics Output

It is possible to add / modify custom scripts and where they can be executed against. To do this, go to the toolbar and choose SSMS Tools > Run Custom Scripts > Options.

 

5. Window Connection Coloring

Using Window Connection Coloring, you can quickly find out what server the active script is connected to. As an example, the blue one below is configured to connect to my WASD instance, while the fuchsia is connected to my local database instance.

WASD - STP Window Colouring

The Window Connection Coloring > Options on the SSMS Tools menu allows you to configure more colours for more database instances,

 

6. Show / Hide EPA

If you are into performance tuning, Show / Hide EPA (Execution Plan Analyzer) may be just for you. When Include Actual Execution Plan is enabled, you can further show (or hide) EPA. It gives a suggestion list on how you can improve the performance of your query. Please note that this is just a suggestion, and that you should perform some testing on how your proposed action(s) will effect the environment as a whole.

WASD - ShowHide EPA Suggestions

By the way, there are many more options within EPA. Make sure you right click on the EPA area to check them out on your free time.

 

7. SQL Snippets

I have to admit, I’m a typist. I love my keyboard. But this particular feature is just super cool. If you are with an application / web developer background, you are probably familiar with Sublime Text’s Code Snippet feature. Well, this works almost just like that.

When I put on my Data Analyst hat, in a day I could be typing a lot of “SELECT COUNT(*) FROM .. ” or “SELECT TOP 10  * FROM .. ” for quick investigations. (No, I don’t use SELECT * FROM in my stored procedures / any production related activities :D). SQL Snippets feature quickly becomes my favourite. It saves me time by just typing SSC or SST. You can configure more SQL Snippets too or change ones that have been preconfigured for you.

STP SQL Snippets

To access this options, simply go to SQL Snippets > Options from SSMS Tools menu.

 

8. Tab Sessions History

Every now and then your machine shuts down unexpectedly or SSMS stops functioning. Then you lose your queries. (Yes, they taught us to save those queries since we were young) or perhaps you accidentally closed the query without saving it thinking that you don’t need it again. Well, guess what? SSMS Tools Pack Tab Sessions History feature is your little insurance for you.

SSMS Tools Pack Tab Sessions History

You can access it by simply pressing Ctrl+K Ctrl-T. Or simply go to SQL History > Tabs Sessions Management. From the SQL History menu, you can also configure the interval between the tab session saves.

 

How Much Does it Cost?

It depends on which version of SSMS you have installed. Prices may also change after this post’s publish date, so it would be wise for me to refer you to the simple Licensing page from the official site.

 

Wrap Up

Remember the saying “Work smarter not harder”? Well, SSMS Tools Pack can definitely helps you towards that. You still need to ensure that you customise it the way you work or want to work.

 

Further Reading

SSMS Tools Pack 2.0 by Grant Fritchey

SSMS Tools Pack (official site) by  Mladen Prajdić

 

 

 

SQL Tools Review: SQL Search

As a SQL Server database developer / DBA / consultant, at some point in your daily routine, you’d probably be asked to investigate a bug or to make an enhancement that is specific to a particular object. A common example is to remove a column from a table, and find all objects that refer to that column. Obviously you want to remove the references to the column (including in dynamic SQL statements*) before you drop that column from the table.

An easy way to find out all the objects in the Server that may refer to that column is by using SQL Search tool by Red Gate.

Price

It doesn’t cost a dime. At the time of writing, SQL Search 1 is free for download.

SQL Search 1 - Free

 

Easy to use

The tool is an add-in to SQL Server Management Studio. The user interface is self-explanatory.

SQL Search - Searching on a SQL Server instance

 

It’s at most 4 simple steps:

1. Type in the text that you want to search

2. Specify whether it’s an exact match or not

3. Choose which object you want to search and on

4. Specify the server. The server drop down list is populated based on the database servers that you have connected on SSMS in Object Explorer.

SQL Search - Server Selection is based on Object Explorer connections

 

Limitations

1. Windows Azure SQL Database

If you look carefully in the screenshot above, there are a couple of hints that show I have 2 connections to Windows Azure SQL Database (WASD) on my SSMS Object Explorer. You are right. Unfortunately, searching on WASD instances is not supported in SQL Search 1. Hopefully Red Gate will add this feature in.

SQL Search does not support WASD

 

2. Dynamic SQL Statement

Dynamic SQL Statements in objects (stored procedures, triggers, etc) that contain the text in plain.

 

CREATE PROCEDURE dbo.usp_DynamicSQLSimple
AS
/**************************************************************************
 *
 * Purpose: Update all NULL values to "Blank" on Title in dbo.Game.
 *			This is for a demo used in
 *			http://www.mssqlgirl.com/sql-tools-in-a-jiffy-sql-search.html
 *
 * Parameters: None
 *
 * Update History:
 * 20140209 - Julie Koesmarno - v1.0 - Initial Release
 *
 **************************************************************************/
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQLStmt VARCHAR(1000);

	-- Construct a dynamic SQL Statement that updates the Column values.
	SET @SQLStmt = 'UPDATE [dbo].[Game] ' +
				   'SET [Title] = ''Blank'' ' +
				   'WHERE [Title] IS NULL '; 

	-- Execute SQL Statement
	IF @SQLStmt IS NOT NULL
	BEGIN
		PRINT @SQLStmt; 
		EXEC (@SQLStmt);
	END;

	-- Print number of rows updated.
	PRINT QUOTENAME(@@ROWCOUNT) + ' rows(s) updated';

END
GO

 

 

On the contrary, if you have a stored procedure with a dynamic SQL statement that constructs / resolves a reference to a text that you are searching, during execution, SQL Search will not be able to find it. Suppose you are interested in updating a first column in dbo.Game where its name starts with “T” and has a NULL value. Obviously, if “Title” is the first column that starts with T in dbo.Game, the dynamic SQLStatement in the below stored procedure (dbo.usp_DynamicSQLComplex) will be the same as above (dbo.usp_DynamicSQLSimple)..

Below is the stored procedure example on a more complex dynamic SQL Statement, where @SQLStmt only contains a reference to “Title” at the time of execution. Therefore SQL Search is not going to be able to find it, which is fair enough, but it is something that you have to bear in mind when finding dependencies.

CREATE PROCEDURE dbo.usp_DynamicSQLComplex 
AS
/**************************************************************************
 *
 * Purpose: Update all NULL values to "Blank" on first column that starts 
 *			with T in dbo.Game. 
 *			This is for a demo used in
 *			http://www.mssqlgirl.com/sql-tools-in-a-jiffy-sql-search.html
 *
 * Parameters: None
 *
 * Update History:
 * 20140209 - Julie Koesmarno - v1.0 - Initial Release
 *
 **************************************************************************/
BEGIN
	SET NOCOUNT ON;

	DECLARE @ColumnName VARCHAR(255);
	DECLARE @SQLStmt VARCHAR(1000);

	-- Get the first Column that starts with T in dbo.Game.
	SET @ColumnName = (SELECT TOP 1 COLUMN_NAME 
						FROM INFORMATION_SCHEMA.COLUMNS
						WHERE TABLE_NAME = 'Game'
						AND TABLE_SCHEMA = 'dbo'
						AND COLUMN_NAME LIKE 'T%');

	-- Print message
	PRINT ISNULL('First column in dbo.Game that starts with T is ' + @ColumnName, 
			'No column starts with T found in dbo.Game');

	-- Construct a dynamic SQL Statement that updates the Column values.
	SET @SQLStmt = 'UPDATE [dbo].[Game] ' +
				   'SET ' + QUOTENAME(@ColumnName) + ' = ''Blank'' ' +
				   'WHERE ' + QUOTENAME(@ColumnName) + ' IS NULL ' ;

	-- Execute SQL Statement
	IF @SQLStmt IS NOT NULL
	BEGIN
		PRINT @SQLStmt;
		EXEC (@SQLStmt);
	END;

	-- Print number of rows updated.
	PRINT QUOTENAME(@@ROWCOUNT) + ' rows(s) updated';

END
GO

 

Alternatives

Another alternative is to create your own script that inspects sys.sql_modules and iterate through your instances. You will then need to look up the object_id to get more details on them. So, for a free tool, SQL Search is much easier to do. There is more than one way to skin a cat, but using SQL Search is a user friendly and time saving way.

 

Wrap Up

I think SQL Search is one of a must have tool for database developers. You may not use it on your daily basis, but boy, when you do need it, it really saves you time, especially if you don’t have your own reusable custom script to search text.

 

Further Reading

Not convinced on how SQL Search can help you out? Check out other #SQLCoop blog posts here:

On a SQL Quest Using SQL Search by Red Gate by Mickey Stuewe

Headache + Pain <> Red Gates SQL Search by Chris Yates

How SQL Search saves you time by Jeffrey Verheul

 

SQL Collaboration

2014 has kicked off and the four of us, SQL professionals, around the globe have decided to do a collaboration work.

 

Who are we?

1. Mickey Stuewe (@SQLMickey | blog)- based in Orange County, USA

2. Chris Yates (@YatesSQL | blog) – based in Kentucky, USA

3. Jeffrey Verheul (@DevJef | blog) – based in Rotterdam, Netherlands

4. Julie Koesmarno (@MsSQLGirl | blog) – based in Canberra, Australia

 

What’s our mission?

It’s very simple. We have decided, despite the different time zones and physical locations, to get together and to help each other out in growing as SQL Professionals. There are a lot of amazing SQL professionals out there, and we look up to them a lot. We feel that as a team, within SQL Collaboration, we can support each other and learn independently as well as a group.

 

What are we going to start with?

Simple. We are all Friends of Red Gate. We believe it’d be a great way to contribute back to the community and to say thank you to Red Gate for creating useful tools that SQL Professionals can use. We are starting with SQL Search! So, look out for our posts

Friends of Red Gate

 

How can you follow us?

Check our blogs regularly or follow us on twitter using hashtag #SQLCoop.

….

 

Power BI In A Jiffy: Schedule Data Refresh

Schedule Data Refresh allows Windows Azure SQL Database, OData and SQL Server data sources configured in Power Pivot to be refreshed. Any of the data sources that are retrieved via Power Query are not supported for Schedule Data Refresh. Any WASD, OData and/or SQL Server database retrieved using Power Query then added to the Data Model (Power Pivot) cannot be refreshed either. That’s the “in a jiffy: part. The rest of the post is a walk through of the Schedule Data Refresh feature.

 

As Power BI features are updated fairly frequentlly, the following walk through is valid at the time of writing. Future enhancements will be followed up in newer posts.

Schedule Data Refresh Feature

I believe this is a new addition to Power BI that I might have missed recently. So, I decided to check it out a little bit a few minutes before my presentation at SQL Malibu on Feb 12, 2014 on “Building Your Myth Busting Lab With Power BI“. When preparing for this presentation, I purposely left out any specific Data Sources, Data Management Gateway and Schedule Data Refresh features as I know that the Power BI Product Team is releasing new features and fixing bugs quite frequently. The cloud offering makes it possible for them to do so.

The features around the “Data Management” side part of it was OK-ish a few months ago and it has been great, because it means that the Product Team has lots of opportunity to shape the features to meet our needs through feedback that the public give them from their preview / trial offerings.

Now that Power BI is in GA in most countries (except for Australia, where I am), I think it’s a good idea to look into Scheduled Data Refresh. When I first read the article about Data Refresh, I initially thought this would also work for Power Query queries that have been added to Data Model. I was mistaken.

Data Sources in Power Pivot

Only Windows Azure SQL Database (WASD), SQL Server and OData data sources created in Power Pivot are supported for scheduled data refresh. If you are using SQL Server database, you will need to make sure that you configure Data Management Gateway and that the server is up and running at the scheduled data refresh time. Below is a snapshot of Power Pivot Data Sources that I have scheduled for refresh, excluding the Power Query one (as it doesn’t work).

Power Pivot Data Sources

 

If you are accessing a local database (presumably for testing), make sure that you fully qualify the name of the server, i.e. <Server Name>\<SQL Server Instance name>. If you use a shortcut such as “.\<SQL Server Instance name>, the Data Management Gateway will not be able to resolve the name and result in an error.

 

Enabling Schedule Data Refresh

Once the workbook is uploaded to Office 365 and enabled, you can schedule data refresh as shown below from the Power BI Site.

Accessing Schedule Data Refresh

You can select the data sources that you want to schedule the data refresh for. Please note that in my example, I also have Power Query connection to my local SQL Server database. The Power Query data source cannot be refreshed. So, I’m only enabling the OData, WASD and SQL Server data sources for scheduled data refresh. At the time of writing, you cannot schedule each data source at different time. So, either they are scheduled for the same time or not excluded in the schedule at all.

 Schedule Data Refresh Settings
Once you set the data refresh schedule, you can click on “save and refresh report”. If the report has not finished refreshing and you click on it again, it will come up with an error message similar tot the following:

Refresh cannot be performed on already running refresh

Schedule Data Refresh History

If you go to the History page, you can check the status under the “Next Refresh” line. Below is a screenshot of history log after a few on-demand and scheduled refreshes. The list is quite comprehensive and lets you know which part of the data sources that cannot be refreshed. When the current data source hit an issue during refresh, the rest of the data sources enabled for refresh in the workbook will not be refreshed.

Schedule Data Refresh History Log

 

Email Refresh Failure

In the Settings, you can also configure one or more email address to send refresh failure notifications to. This send email feature is definitely handy. The error notification sent by email is similar to the following.

Power BI Schedule Data Refresh Error Email

 

What could be improved?

1. The ability to configure different data sources in the workbook at different times or different refresh rate. This can be a double-edged sword though. Data source synchronisation is never easy.

2. Lower latency for status update to reflect the refresh process. At the time of writing, I find that I need to wait for about 10 – 30 minutes before I find out that the 30 second refresh is successful.

3. When the data source names in Power Pivot have been updated, the Schedule Data Refresh should ideally reflect that. At the time of writing this doesn’t happen at all.

Do you have your own suggestions for improvement / bugs to report? Please comment below and I will do my best to pass them on to the Power BI team. Alternatively, you can write to them in the Power BI forum.

 

Hope this walk through has been useful.

 

Wrap Up

In summary, Schedule Data Refresh allows users to refresh workbooks in Power BI with the latest data at a defined date / time as well as on-demand. You can schedule all the data sources in the workbook to be refreshed or only a selection of them, for the same schedule date and time. Although the feature is a little simple at the moment, it is much anticipated to match its cousin’s, on-premise SharePoint’s “automatic refresh Power Pivot“.

 

Further Reading

Keeping Your Reports Up to Date with Scheduled Data Refresh by Power BI Team

Automatically Refresh PowerPivot Data in SharePoint on Technet

Announcing the General Availability of Power BI for Office 365 by Power BI Team

 

 

 

Power BI In A Jiffy: Embedded Power View

Power BI team has been very busy the past month or so. There have been a few interesting posts around Power BI, some of which related to Power BI Contest (Congratulations to the top 5 winners!). Two blog posts that I find interesting in February 2014 around Power BI are:

1. Let Power BI tell you a Valentine’s Day Story

2. Making Big Data Work for Everyone by Quentin Clark

I find these posts interesting because you can interact with the Power View visualisations embedded on a website that is publicly accessible.

That’s it for the “in-a-jiffy” part. Read on for more details on how the embedded Power View visualisations perform on various devices.

Embedding Interactive Power View Visualisations – Early Samples

I was very excited about it when I first saw the two posts mentioned earlier. I have then decided to test them on a few different devices and asked help from friends / families to help testing out on their device of choice. Below are some screenshots taken from these devices along with a short review on each device.

1. Nokia 1020

The visualisations work well on Nokia 1020.

Note: Clicking on “Sort” to change the sort order can be a little tricky due to the size. Zoom pinch in/out on Power View frame is not recognised, instead it is being treated as “click” / “touch” action. The screen size is big enough to interact with it, but it’s not going to be as easy as with larger real estate.

Nokia 1020 Screenshot of Valientines Visualisation using Power VIew

 

 

Nokia 1020 Screenshot of Big Data Visualisation using Power VIew

 

 

2. Samsung Galaxy Tab 7.7

It doesn’t work after a few attempts. When it is still loading, it looks like it is going to render OK, but then the Power View charts disappear.

 

Samsung Galaxy Tab 7.7 - Not loading properly

Instead of showing another example of the Power View visualisation for the second article (Making Big Data Work for Everyone), below is a screen shot of the corresponding Power View on Excel Web App on Samsung Galaxy Tab 7.7, which works 50% of the time.

Excel Web App

 

3. Surface 2

It works fabulously (as one would expect), better than on Nokia 1020.

Surface2 - Population

 

Surface2 - Valentine's

 

4. iPhone 4s

I’m happy to say that it works on iPhone 4s too!

iPhone 4s Power View

 

5. iPad Mini (iOS 7)

The first article crashes the browser. I have tried a few times unfortunately it yields the same result.

The second article seems to be loading OK, but not interactive. It looks like it would do the highlight filter when you touch on a bar chart column, however it resets back to normal as shown on the screenshot below.

Screenshot on iPad Mini

 

Thank you to friends and families in lending their devices to me for testing this feature.

 

Whats The Big Deal?

From mobility point of view, it’s about time … :) As you may know, other “pretty” data visualisation competitors out there have had embedding features for a whlie. Check out this Would you like extra germs with that? article on Sydney Morning Herald that embeds engaging data visualisations on Food Safety Offences.

A few caveats:

  • Power View registers “mouse-hover” actions. On touch-screen devices, it is almost nearly impossible to emulate the “mouse-hover” actions.
  • Pinch actions for zooming in and out on the Power View frame is not smooth and may not work to zoom in/out properly.

 

Wanna Help?

If you’d like to help, please comment below and send me screenshots of the Power View Visualisation from the two sites mentioned earlier, using devices that are not listed here yet. You may want to try some “pinch” and “touch” actions on the Power View frame, and let me know if interaction with the chart works (for example, filtering/highlights). Thank you in advance.

You can also tweet me @MsSQLGirl and attach the screenshots with #PowerView hashtag.

 

Wrap Up

Two recent blog posts from Microsoft exhibit ability to embed Power View visualisations. The next question is, how is it done? Stay tuned!

 

Further Reading

Power BI For Office 365 – Microsoft

Power View: Explore, Visualize And Present Your Data – Microsoft

 

 

Free Business Analytics Learning #pass24hop

24 Hour of PASS Business Analytics edition starts on 6 Feb 2014 at 3 AM AEDT (Sydney, Canberra, Melbourne). There will be 12 one hour back to back live sessions where you can ask your questions, followed by 12 one hour replay sessions.

The topics covered in this event includes:

  • Advanced Analysis Techniques
  • Analytics and Visualisation
  • Big Data
  • Information Delivery
  • Strategy and Architecture

For further details on the session and to register, please visit: http://www.sqlpass.org/24hours/2014/ba/Sessions.aspx

I feel very honoured to be able to contribute as a moderator for two 24 Hours of PASS sessions with experts in the SQL Server community:

Hope to see you at the sessions!

24 Hours of PASS BA 2014

Julie

Power BI In A Jiffy : Myth Busting Driver Fatalities

Instead of posting a quick tip on Power BI, I thought I’d share my quick story about Power BI.

Power BI Contest is near at its end (just under 1.5 day as I write this blog post). Submissions started pouring in and I am very excited to see all the amazing work of BI Professionals out there. Hope to watch your video on the website and may be at PASS BA Conference!

To view the submissions entered so far, including mine, please visit PowerBIContest.com.

I suspect that the moderator needs to approve my video first before it is published. Hopefully my entry qualifies :) So, here is a slightly longer (and less talking) version of it.

Myth Busting Driver Fatalities With Power BI

 

Oh, and Happy New Year!

Julie