Yearly Archives

32 Articles

Top 3 of what is new in Power Query for Excel

Just recently the December update of Power Query for Excel has been published. See this blog post on the Power BI for the details: http://blogs.msdn.com/b/powerbi/archive/2013/12/13/one-step-closer-to-simplifying-data-analysis-and-visualization-new-features-added-to-power-bi-for-office-365-and-power-query-add-in-for-excel-preview.aspx.

In this post I want to give you my personal top 3:

1. Automatic detection of table relationships. Like PowerPivot if you import multiple tables from the same source Power Query now detects the relationships and brings them over to the Power Pivot Data Model automatically.

2. Connectivity to new data sources. Now supported: Exchange, Dynamics CRM Online, JSON light and Sybase IQ.

3. Fill Down transformation. Fills all empty cells in a column with the value of the first non-empty cell above them. Very handy for those matrix like Excel sheets!

I will come back to some of these in later posts. Happy Power Querying!

The wait is almost over…

It’s almost Christmas!

Well, uh, that’s not actually why I wrote this post. I noticed a note on the Power BI blog that says: “In a few days we will enable the ability to explore your own data with Q&A. You can add your own models to Q&A and begin to explore them with natural language query.” (See full post here: http://blogs.msdn.com/b/powerbi/archive/2013/12/13/one-step-closer-to-simplifying-data-analysis-and-visualization-new-features-added-to-power-bi-for-office-365-and-power-query-add-in-for-excel-preview.aspx)

Read that again! This means that you can upload your own Power BI workbooks and use Q&A to have a conversation with your data. How cool is that! Minority report anyone?

Power BI Demo Contest

I want to draw your attention to a contest that we run: the Power BI Demo Contest. All you need to do is create a short video of something cool you built using Power BI and share it. You could win great prizes!

More info is on Facebook: https://www.facebook.com/microsoftbi/app_112813808737465

Good luck!

Setup and register a Data Management Gateway for Power BI

In this post we will have a look at how to install, setup and register a Data Management Gateway for Power BI. It involves installing software on a server which will function as the gateway as well as configuration through the Power BI Admin Center. Once set up you can use the gateway to publish on-premises sources for use through Power BI.

  1. To create and register a Data Management Gateway follow these steps:
    1. In the Power BI Admin Center, click gateways and click ‘new gateway’.
    2. Specify a name for the gateway and an optional description. Then, select ‘Store the credentials securely in the cloud’ to store credentials in the gateway rather than on the machine where the gateway is installed. Storing credentials in the cloud enables you to restore the gateway to another machine if the original one is lost. Click ‘Next’.
    3. Click ‘Create’ to create the gateway. Then we need to copy the Gateway Key for later use. Click ‘Download’ to download the Data Management Gateway software. Install the software on your machine and start the Gateway Setup wizard.
    4. In the wizard paste the Gateway key you copied. Finally go to the Power BI Admin Center and click ‘Finish’. Your gateway should now be listed as ‘Online’.
    5. Switch back to your setup wizard and choose whether you want to use an existing certificate or a Power BI generated certificate to protect the credentials. Click ‘Next’. On the specify endpoint access page specify if you want to use HTTP or HTTPs and make configuration settings accordingly. Click ‘Next’ and ‘Finish’ to close the wizard. Make sure the gateway name matches the name in the Admin Center and that is shows as Registered and Started.

That’s it! You have now successfully installed and registered a Data Magement Gateway for Power BI. Next up is creating sources. I’ll discuss in a future post how to create an OData feed through the Power BI Admin Center.

Publish SSIS Packages as OData Feed Sources with Power BI

With the upcoming advent of Power BI on-premises data sources can be published as OData feeds to users. By default, you can only register SQL Server data sources here. With a clever trick you can register SSIS packages as data sources here, using the new Data Streaming Destination and Microsoft OLE DB Provider for SQL Server Integration Services (SSISOLEDB).

How it works is that you use a special destination (Data Streaming Destination) in the SSIS package. Then, we you publish the package to the SSIS Catalog you can run the SSIS Data Feed Publishing Wizard which enables you to create a view. That view can then be published as OData feed as can any other view.

In this post we will walk through an example. You will need SQL 2012 and the SSIS Data Feed Publishing Components, available here: http://www.microsoft.com/en-us/download/confirmation.aspx?id=39931 (pre-release).

  1. Install the SSIS Data Feed Publishing Components. After you installed it make sure it is successfully installed and configured by using SQL Server Management Studio. Connect to your SQL Server, expand Server Objects, Linked Servers, Providers and makes sure you see the SSISOLEDB provider. Double click SSISOLEDB here and enable ‘Allow Inprocess’ if it is not enabled and click OK.

     

     

  2. Create a new Integration Services project (or of course you can edit an existing one), I’ll work with a new one just to keep it simple. I named my project SSISODataPublishing. Drop a Data Flow task onto the control flow and double-click it.
  3. In the data flow, define a source (I connected to an AdventureWorks database). Also specify the table you want to publish as OData feed (I used DimProduct).
  4. Now drag and drop the Data Streaming Destination to the data flow. Of course in real life the package would not be this simple: you would get the data from sources, transform it and then finally use the destination. For demonstration purposes I am keeping it simple here. Connect the OLE DB Source to the new Data Streaming Destination like so:

  5. What I noticed is that the Data Streaming Destination does not support all data types. For example, LargePhoto is a varbinary and is not supported. To fix this we need to unselect it from our OLE DB source. Do this by editing the source. On the Columns page unselect LargePhoto and click OK:
  6. Now there is nothing else to do but to deploy the package. Right-click the project in the Solution Explorer and choose Deploy. Click Next and use browse to enter the destination SQL server name to publish the project to the Integration Services catalog. Then click browse to enter the path. If you run into this information message you have not correctly set up the SSISDB catalog yet:
     

    To fix this go to SQL Server Management Studio, connect to your SQL Server, right-click Integration Services Catalog and choose ‘Create Catalog…’. In the next dialog enter a password for the encryption key protection and click OK.

    After this return to your Integration Services Deployment Wizard and choose browse to enter the path again. Now you can create a new folder in the catalog and select it. Click OK to enter the path:

    Click Next and choose Deploy to finish the deployment.

     

  7. When the deployment finishes, make sure all items have resulted in Passed and no errors or warning exist. Then click Close.
  8. Now that the deployment has been done, we need to publish the package as view in a SQL Server database. To do that we need to use a new wizard called the SSIS Data Feed Publishing Wizard, which can be started by running the ISDataFeedPublishingWizard.exe from C:\Program Files\Microsoft SQL Server\110\DTS\Binn or just search for Data Feed Publishing Wizard in Windows.
  9. In the wizard, click Next on the welcome page.
  10. On the package settings page enter the name of the server where you deployed the package and enter the package. If you have any parameters configured you can enter values here or bind them to environment variables:
     

    When done click ‘Next’.

  11. On the Publish Settings page select a database where you want the view for the SSIS package to be created and enter a name for the view. Make sure to choose a LinkedServer. If it does not exist already, the wizard will create it. Note you can also specify Use32BitRuntime and Timeout options here:
  12. Before clicking ‘Next’, choose ‘Advanced’ and specify the Schema you want to use, if you want to use encryption to encrypt data before sending it over the network and what certificate you will use for the encryption. I left everything at the default (schema dbo, no encryption). Click ‘Next’.
  13. On the validation page you will see a warning if no linked server exists yet. That is not a problem since the wizard will create the server automatically. Make sure that no other warnings exist or errors exist and click ‘Next’.
  14. Review the selections you made and click ‘Publish’ to actually create the view.
  15. Review the progress and make sure no warnings or errors exist. Note that the query you will need to use to get the output data of the package is displayed at the bottom of this screen:
  16. Click ‘Finish’.
  17. Test the view by running the query above in SQL Server Management Studio:
  1. So far all is OK. Last step is to create a data source and enable the OData feed in the PowerBI Admin center. You will have to have a working Data Management Gateway configured in order to do this. When done you can use the feed in Power Query. See my post on how to configure the Data Management Gateway.

Managing Azure Virtual Machines using PowerShell

I use Azure for my demo machines and needed a way to easily shut all VMs down and also to start my BI demo machines. What better way to do this other than with PowerShell!

In order to be able to talk to Azure with Powershell you will need to install Windows Azure Powershell (download link: http://www.windowsazure.com/en-us/downloads/ ).

Also you will need to get your Azure Publish Settings file which is specific to your subscription. You can get it from the Azure portal once you are logged in: https://windows.azure.com/download/publishprofile.aspx .

Let’s start with stopping all VMs. Start the PowerShell ISE (search for powershell_ise on Windows 8) and create the following script:

Make sure to enter the path to where you stored your publish profile that you have downloaded.

Basically what this script does is iterate over all your subscriptions if you have more than 1 and look for VMs that are in a running state using get-AzureVM. Then for each VM that is running it will echo its name and then stop the VM using stop-AzureVM.

Save the script and then you can just run it and all of your VMs will be turned off. Pretty easy huh?

For my BI demos I use a maximum of four VMs and I made another script that starts them in the correct other (first the domain controller, then the SQL server and then finally the two SharePoint servers I need):

This script defines a function that wraps a check if the VM is already running and otherwise starts it. The bottom part of this script uses that function to specify which VMs to start in which order. I replaced the original names for security reasons.

This saves a lot of time. It saves me from logging into the Azure portal and starting / stopping each VM by hand. The best part is I can let this run in the background while presenting and nobody sees it J


Five years at Microsoft

I was hired as FTE at Microsoft in November 2008 so recently celebrated five years at Microsoft! Still going strong and enjoying every day J

Below is the glass award I received:

How to use a configuration (.config) file in SQL Server CLR

Config files (web.config, app.config) are a great way to store configuration settings outside of your programs’ code. If the configuration changes (for example you move from development to test or from test to production) you do not need to go and change your code and re-compile. You simply change the config file and you are good to go. In SQL CLR you can also use this concept, however there are some gotcha’s.

First create your SQL Server CLR project. Note that you will have to have the correct versions of SQL Server Data Tools. You can download what you need from here: http://msdn.microsoft.com/en-us/data/hh297027. I have SQL 2012 installed and am using Visual Studio 2012, so I installed the SQL Server Data Tools for Visual Studio 2012 from the link above.

To start create a new database project and add a new SQL CLR C# User Defined Function (you can also use VB if you like). Add a reference to System.Configuration to your project. Make sure you include the following line in your cs file: ‘using System.Configuration;’.

Now, the config file needs to be stored in the \Binn folder under the root path of the SQL Instance. To get the root folder run the following code in SQL Server Management Studio:

 

 

Add ‘\Binn’ to the result of this query. My path was: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn. There create a file named ‘sqlservr.exe.config’ (make sure to get the name right!) You can use Visual Studio for this. A sample config file would be as follows:

 

Of course you can specify anything you want in the config file. To keep things simple I chose only to configure a sample connection string.

In your cs file all you need to do is create a function that retrieves your configuration for the config file (in my case just the connection string).

Here is the code you will need to put in your CS file:

 


 

As you can see this code retrieves the value of the configuration value MyConnString from the config file.

If you, after the code has been deployed, made any changes to the configuration file that you would like to be picked up you will have to either restart the SQL Instance (not really an option in most cases) or free the system memory caches which will unload the CLR memory clerk by running the following in SQL Server Management Studio:

 

This is unlike normal .NET applications where configuration file changes take effect immediately.

When done right-click your solution in the solution explorer and choose ‘Publish’ and publish your solution to the SQL Server. Then in Management Studio you should be able to find your function in the database you deployed to:


Now we can test our function by executing it:

 

 

Be sure to execute this against the database you deployed to.
Your connection string should be returned:


There you have it: you can now use configuration files from SQL Server CLR.

 


Working with Azure and HDInsight from SSIS

A while ago a whitepaper was published on how to work with Azure and HDInsight (Hadoop on Azure) from SSIS. In that whitepaper some code samples were given. That code is also available here (including some components): http://code.msdn.microsoft.com/SSIS-Packages-Sample-for-2ffd9c32 . After you have downloaded the zip make sure you have Visual Studio installed, start the Developer Command for Visual Studio as Administrator and run the ‘deploy_SSIS_packages_and_components.bat’ file which is included in the zip. This will install some of the DLLs included into the GAC. Then, you can open the solution in Visual Studio.

The solution includes the following sample SSIS packages:

  • PigSqoopPackage: shows how to work with Pig and SQOOP tasks
  • HadoopJobAutomation: shows how to start jobs on Hadoop and how to consume results
  • ComplexSourceDestination: shows how to get data from Azure Blog Storage and save the results into various targets, including Azure Blob Storage.
  • BlobSourceTestPackage: sample package showing how to read data from Azure Blob Storage.
  • BlobDestinationTestPackage: sample package showing how to write data to Azure Blob Storage.

The first two packages essentially contain some script tasks with complete samples on how to work with Piq, SQOOP and Hadoop jobs respectively. The other packages use the components provided and provide a quick start on getting data from Azure Blob Storage and getting data into Azure Blog Storage using SSIS.

Speaking at SharePoint Connections Amsterdam 2013

600-jeroen-ter-heerdt

Just a quick post: I will be speaking at SharePoint Connections Amsterdam 2013 (19th-20th November 2013) on Power BI for Office 365.
More info on: www.nccomms.com/sharepoint_connections.
I can get you a 10% discount, just reach out to me.

200-jeroen-ter-heerdt

 

 

%d bloggers like this: