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 : 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

 

 

 

 

GeoFlow, SQL Server and Millions of Rows

Over the past few months, I have been asked if it is possible to connect GeoFlow to SQL Server database; and the more intriguing question is, why don’t GeoFlow connect to Tabular Model straight away instead of using PowerPivot. Although I do not know if there is such a plan related to the latter question, I do know that you can visualise data in SQL Server database or Tabular Model using GeoFlow.

The key ingredient for exposing this data is by storing and modelling it in PowerPivot. Why so? Perhaps one of the reasons is that most organisations, big or small, and individuals have or know how to use Excel (yes, they will need Excel 2013). Buying Excel 2013 Professional edition or higher would be cheaper to afford than the cost of buying a license for SQL Server. So, PowerPivot is the middle ground and it is the best win-win situation, for now. Those organisations which have invested time and effort converting their PowerPivot workbooks to Tabular Models however, would probably appreciate being able to expose their data in Tabular Model using GeoFlow.

Exposing SQL Server for GeoFlow

I have recently posted a step-by-step instruction to expose data stored in SQL Server database and Tabular Model to GeoFlow at Microsoft Customer Reviews of STB Products.

In the step-by-step instruction, I did mention that I was using 22 Million of records. The sample workbook is about 580 MB and shared via SkyDrive. PowerPivot in the workbook contains daily data of Internet Speed tests for 5 years and about 2,200 unique locations. The workbook cannot be opened using Excel Web App due to the size and GeoFlow is not supported in Excel Web App either. So, you will need to have Excel 2013 Professional edition or above, and have GeoFlow installed.

I have also created a tabular model based on the 22 Millions records, but only exposing the aggregated data via DAX. The PowerPivot in this workbook contains about 1 Million of records, containing monthly data for 5 years worth of Internet Speed tests conducted in about 2,200 unique locations. The workbook is about 6.2 MB and shared through SkyDrive.

GeoFlow for Excel 2013 - SQL Server Data Refresh 22M rows

 

I am deliberately making the workbooks available via SkyDrive, in case in the future and wouldn’t it be nice if, GeoFlow can be supported in Excel Web App too :)

Wrap Up

GeoFlow Preview for Excel 2013 can connect to SQL Server via PowerPivot. This article has shown how GeoFlow can render geospatial information from millions of rows stored in SQL Server database and Tabular Model.

 

Further Reading

GeoFlow for SQL Server Database and Tabular Model at Microsoft Customer Reviews of STB

Sample Workbook: GeoFlow using SQL Server database

Sample workbook: GeoFlow using Tabular Model

 

 

Measure Selection Using Slicers – Excel 2013

I enjoy reading Jason Thomas’ blog, especially on SSRS. He has very good techniques that he describes very well.

In his latest post, Measure Selection Using Slicers, using Excel 2010 he explores how you can allow measure selection using slicers, which I think is really great. I have used the technique multiple times in Tabular Model.

The Excel 2013 version of his is here, in case you are thinking of testing it out on Excel 2013. I upgraded the PowerPivot first when I opened the original workbook in Excel 2013. Below is a reusable set of steps that you can use to upgrade an Excel 2010 workbook with PowerPivot to Excel 2013.

Caveat: upgrading from Excel 2010 to Excel 2013 may be problematic Read Jason Thomas discussion here. This discussion is related to having different Excel table names to the PowerPivot table names.

Upgrading Excel 2010 workbook with PowerPivot to Excel 2013

1. Open the workbook. in Excel 2013. Ensure you click on “Enable Content”

Open Excel 2010 workbook in Excel 2013

If you click on one of the slicers, you may get an error similar to the following.

“The workbook has a PowerPivot data model created using a previous version of the PowerPivot add-in. You’ll need to upgrade this data model with PowerPivot for Excel 2013″.

 

2. Click on PowerPivot tab, and choose Manage

Click on "Manage" PowerPivot

You will get a warning similar to when you click on a slicer. Clicking OK will open up a dialog box to upgrade.

 

3. Click OK on the PowerPivot upgrade option.

Upgrade PowerPivot to Excel 2013 Dialog Box

 

4. Once PowerPivot is finished upgrading, a dialog box will appear to notify you that the workbook is still running in compatibility mode. Choose Yes to upgrade the workbook fully.

Upgrade the Workbook to Excel 2013

 

The workbook will be closed and reopened as an Excel 2013 version.

The version I am using is Microsoft Excel 2013 (15.0.4420.1017) 64-bit; Part of Microsoft Office Professional Plus 2013.

Update: I spoke with Jason Thomas and we confirmed that we are running different versions of Microsoft Office 2013. I have downloaded mine from my Technet Subscription which is the Microsoft Office Professional Plus 2013, whereas he obtained his copy from Office Preview website.

 

Enjoy!

DAX Studio Review

One of the enjoyable and privilege components of my work is discovering new things and learning new things, especially in becoming more productive. DAX Studio is a great example of this.

DAX Studio is a result of great hard work and dedication from BI experts; Paul te Braak, Marco Russo, Darren Gosbell and Angry Koala. The product can be downloaded from Codeplex. The version in this review is 1.0.1.

This article is a review of DAX Studio based on my experience at work. It does not encompass all usage scenarios under the sun for DAX, however it should provide some insights on the pros and cons of the product.

DAX Studio is an Add-In

DAX Studio is an add-in for Excel 2010 (I believe the product would also work for Excel 2013 as well, but I am yet to test this). After installing DAX Studio, you will see that the button becomes available on the Add-In tab

DAX Studio Add-In Button

Upon clicking on the DAX Studio, a dialog box would appear, requesting for connection to either an existing PowerPivot (default) or a tabular model instance.

Connect To detects no PowerPivot model

 

It is quite interesting that when we connect to the PowerPivot model linked to the workbook, the connection will say “Microsoft_SQLServer_AnalysisServ…”. Unfortunately the name is cut off from the drop down box and it’s not resizeable. Similarly it is not easy to pick a tabular database from the drop down list when they have long name with the same first few characters as shown below.

 

Perspective is selectable from the dropdown list on the right near where the Server Name drop down box.

Similar to SSMS, the following information information is shown on the bottom left of DAX Studio window:

  • Server Name ($Embeded$ when connecting to a Power Pivot model)
  • SPID
  • Version

 

DAX Studio Shines

DAX Studio allows you to browse through the objects that you have defined on the model. The calculated measures are shown under the table that it is created, not grouped separately like in SSMS under “measures”. In the version that I am evaluating, the calculated measures are listed based on the creation time/date. This could be a good thing for some users. Unfortunately, in my case, I am implementing over a hundred of calculated measures (it’s for a specific Accounting report, hence lots of measures) within my model, making it harder to find the measure I want sometimes. But at the same time, when I’m still debugging the newest calculated measure, it’s really good as I can go to the bottom of the list and pick the last calculated measure that I want to test.

I also find that the ability to see the hidden fields or tables in DAX Studio is very powerful. Quite often, my query requires those hidden column or tables as part of calculation. In addition, it makes life so much easier in debugging what the hidden columns that were used as part of the calculation.

Based on my experience when using DAX Studio, there are two major aspects that I find very useful.

1. Performance of the DAX Studio when being used for querying

More often than not, I have a few pivot tables within one workbook. When I only need to inspect / debut a calculated measure, I wouldn’t want to refresh the whole pivot tables. So, with DAX Studio, I am able to perform a smaller set of unit testing independently from the rest of my workbook. I find that it’s a lot quicker for me to refresh the EVALUATE statement in DAX Studio than for the PowerPivot to refresh the data.

2. Analysing performance of queries

As part of unit testing, I am often is concerned with how long my query runs. I can go to the DMV tab and perform drag and drop on useful DMVs like:

select * from $SYSTEM.DISCOVER_PERFORMANCE_COUNTERS

In addition to being able to see the different types of performance counter, DAX Studio also returns a message about errors and how long the query takes.

 

Let’s take a look at the Output generated by DAX Studio. The actions that can run on DAX Studio are:

  1. Output query to Excel as a Table
  2. Output query to Excel as a static data
  3. Output query to Grid dialog box
  4. Evaluate only (no output)
  5. Clear Cache

 

There are also options on creating new sheet or overwriting existing sheet when Output query to Excel is chosen.

There is support for line numbers, saving the query and – most of all – syntax highlighting.

Where to from here?

As a BI Consultant working with Tabular Model and PowerPivot solutions on daily basis, I think DAX Studio is a superb companion. It helps in increasing developers productivity.

I think the product can be improved further. Here are some small areas of improvement which would be helpful (I’m talking based on my experience – if you share my view on areas of improvement, please vote or comment on the codeplex comment link).

1. More support for SSMS/VS short cut keys, e.g. changing case to upper case or lower case, Ctrl+K Ctrl+C to comment things

2. Keeping the column format as specified in the model. Even though the calculated measure is formatted as shown below, the output is not retaining the format.

Internet Total Sales format is Currency

 

 

The workaround for this is to use the “FORMAT” function in the DAX query, but this is not ideal.

3. Ability to sort the calculated measures – at the moment it’s in the order they’re created, which might not be the best.

4. Providing a warning dialog box – “You have unsaved query. Are you sure you wish to exit DAX Studio?”. I have been caught a few times accidentally closing DAX Studio instead of the Grid dialog box and lost some changes :(

5. Displaying DAX query plan

6. Adding integration to source control

7. Resizeable Database Name Dropdown Box.

Wrap Up

I have been using DAX Studio for over a month now and am very delighted that such product exists. This really helps in the development life cycle of Tabular Model and PowerPivot solutions. Is it overkill, given SSMS is available? No, querying PowerPivot via SSMS is not an option (not that I am aware of). Would there be more support for the product? Yes, I believe so. I would highly recommend downloading, using the product and providing feedback. The project is built and coordinated by BI experts in the world who are passionate and actively involved in implementing Tabular Model and PowerPivot solutions.

DAX Studio is a great tool for developing DAX query, unit testing and querying performance statistics. It is easy to use and can definitely help.

If you have any comments and feedback on DAX Studio, please post them on the Codeplex project so it can be tracked. I’m also happy to hear thoughts around the product or a similar one.

Further Reading

Using DAX studio to create PowerPivot measures by Javier Gullien

Introduction to DAX Studio by Paul Te Braak

DAX Studio 1.0 beta available #dax #tabular #powerpivot by Marco Russo

Gantt Reworked with ADDCOLUMNS, FILTER, GENERATE and SUMMARIZE by David Churchward for PowerPivot Pro

 

Upgrading SQL Server 2012 RC0 to RTM for Sharepoint 2010

After attending Rod Colledge’s presentation where he touched on steps in setting up Sharepoint 2010 Business Intelligence with SQL Server 2012 at SQL Saturday 138 in Sydney, I’ve decided that to be brave and tinker with Sharepoint installation and configuration a little bit more.

Recently I have this requirement to upgrade the SQL Server 2012 RC0 installation on a Sharepoint server to SQL Server 2012 RTM version. I decided to do a bit of research and typing in “upgrade sql server 2012 rc0 to rtm sharepoint” on Bing returned a few promising results. One of them was Jen Underwood’s comment posted at http://social.msdn.microsoft.com/Forums/en-US/sqldensetup/thread/cd7287b6-f587-4a87-9676-75ddc9b09800.

The worst case scneario that I could think of is that I would need to rebuild the Sharepoint server. That’s OK because luckily we’re still in an eval mode and not in live production yet. So I did the upgrade. For a non Sharepoint savvy person, the whole process took me about 1.5 hours including copying the SQL Server 2012 iso file across the network.

The good news is, it was successful! (Gee, I never thought that it would be this easy!)

Before we go ahead, I’d like to point out a few things:

  1. At the end of the upgrade, the Sharepoint machine will have 2 installation paths of SQL Server for each instance, i.e. the existing SQL Server 2012 RC0 folder and SQL Server 2012 RTM folder. I am pedantic and try to avoid this as much as possible, but for now, it’s good enough; perhaps we may even be able to clean the folders up later. SQL Server 2012 Directories after Upgrade RC0 to RTM in Sharepoint 2010
  2. The target Sharepoint environment only has SQL Server installation specific for Sharepoint – i.e. SQL Server instances on the Sharepoint machine are not used by any other Applications (if they do, then make sure that they’re no live connection to the SQL Server instances).
  3. The Sharepoint server is not an Active Directory server.
  4. Test this upgrade method in a Test machine first before rolling out to Production.

 

Let’s get into it now.

[Warning: The below steps should be used as guidelines and should be tested in a Test machine first. If you are using a virtual machine for your Sharepoint server, don't forget to create a backup prior to upgrading]

 

Prerequisites

  1. Administrator previlege on the machine as well as Sharepoint.
  2. SQL Server 2012 RTM version – and a product key for the Business Intelligence or the Enterprise Edition.

Steps

  1.  Go to Sharepoint Central Administration > Manage Services on Server (under the System Settings heading). Stop the following services: SQL Server Analysis Services, SQL Server PowerPivot System Service, SQL Server Reporting Services Service.Stop Sharepoint 2010 Services related to the SQL Server 2012 items to be upgraded
  2. Run setup.exe of SQL Server 2012 RC0 and choose Maintenance.
  3. Select the “Edition Upgrade” option (although it only lists SQL Server 2005, SQL Server 2008 and SQL Server 2008R2 upgrade) which will show you the list of instances that can be upgraded.
  4. Select “POWERPIVOT” instance to be upgraded.SQLServer2012 Upgrade RC0 to RTM Select POWERPIVOT Instance
  5. Click Next to move on to the “Select Features” which will grey out all the features installed under POWERPIVOT.
  6. Click Next to “Reporting Services Sharepoint Mode” which presents a message that Continuing with this upgrade may put your SharePoint farm in an inconsistent state. Tick on the “Continue with the upgrade.” option.SQL Server 2012 Reporting Services Sharepoint Mode upgrade message
  7. Click Next to go to “Instance Configuration” which you will need to give a new name, e.g. POWERPIVOT2100

    SQL Server 2012 Upgrade RC0 to RTM Instance Configuration

  8. Click Next until you reach “Upgrade Rules”. An error may come up stating that ‘Rule “SQL Server Analysis Services Upgrade Service Funcitonal Check” failed. The current instance of the SQL Server Analysis Services service cannot be upgraded because the Analysis Services service is disabled or not online. Please start the service and then run the upgrade rules check again‘. If this happens, go to SQL Server Configuration Manager and you will see that the Analysis Server is Stopped. Change this from Disabled to Automatic. (This may be because I stopped the SQL Server Analysis Services Service from Sharepoint earlier on).

    SQL Server 2012 Upgrade Rules Error related to Analysis Services
    SQL Server 2012 SSAS POWERPIVOT Start Mode changed to Automatic

  9. Click OK on the error dialog box and click Re-run on the Upgrade Rules window. It should rerun the upgrade checks and progress on to “Update Progress”.
  10. In the middle of the upgrade of SQL Server Analysis Services Service, a similar error will come up. “The following error has occurred: The service cannot be started, because it is disabled or because it has no enabled devices associated to it“. Again, go to SQL Server Configuration Manager and change the start mode from Disabled to Automatic.
    SQL Server 2012 Upgrade RC0 to RTM Upgrade Progress Error
  11. Click retry and the installation should resume normally to completion.
  12. Once this is done, you can verify the servers installed on the Sharepoint Server by launching SQL Server Management Studio and connecting to different parts of POWERPIVOT SQL Server instances.
    SQL Server 2012 SSMS Instances

After the upgrade, don’t forget to reboot the Sharepoint server.

Post upgrade verifications

  1. Verify that your PowerPivot gallery or Default PowerPivot Service Application is running properly, by going to Sharepoint Central Administration > Manage Service Applications (under Application Management) > Default PowerPivot Service Application
  2. Verify that Microsoft.AnalysisServices.SharePoint.Integration.dll in Windows Assemby is referring to 11.0.2100.60 (right click on the dll, choose Properties and click on Version tab). More detailed info http://msdn.microsoft.com/en-us/library/ee210646(SQL.110).aspx#checkver
  3. Verify that msmdsrv.exe version is 11.0.21.00.60 in \Program Files\Microsoft SQL Server\MSAS11_00.PowerPivot\OLAP\bin (right click on the file, choose Properties and click on the Details tab). More detailed info http://msdn.microsoft.com/en-us/library/ee210646(SQL.110).aspx#checkver

 

And that is it! OK, I bet you can do this in less than 1.5 hours!

Hope you find this instruction useful. Please let me know if you have any suggestions on the steps that I may have missed out from the above.