Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

«November 2015»

DirectQuery in Power BI Desktop

In the latest Power BI Desktop a new Preview features was released that now allows you to connect using DirectQuery to either SQL Server or Azure SQL Databases.  DirectQuery is a really neat feature that allows you to point to the live version of the data source rather than importing the data into a data model in Power BI Desktop. 

Normally when you want to get an updated dataset in the Power BI Desktop you would have to manually click the refresh button (this can be automated in the Power BI Service), which would initiate a full reimport of your data.  This refresh could take a variable amount of time depending on how much data your have.  For instance, if you’re refreshing a very large table you may be waiting quite a while to see the newly added data. 

With DirectQuery data imports are not required because you’re always looking at a live version of the data.  Let me show you how it works!

Turning on the DirectQuery Preview

Now, because DirectQuery is still in Preview you must first activate the feature by navigating to File->Options and settings->Options->Preview Features then check DirectQuery for SQL Server and Azure SQL Database


Once you click OK you may be prompted to restart the Power BI Desktop to utilize the feature.

Using DirectQuery in Power BI Desktop

Next make a connection either to an On-Premises SQL Server or Azure SQL database.

Go to the Home ribbon and select Get Data then SQL Server.


Provide your Server and Database names then click OK. ***Do not use a SQL statement.  It is not currently supported with DirectQuery***


From the Navigator pane choose the table(s) you would like to use.  I’m just going to pick the DimProduct table for this example and then click Load.  You could select Edit and that would launch the Query Editor where you could manipulate the extract.  This would allow you to add any business rules needed to the data before visualizing it.


Next you will be prompted to select what you want to connect to the data. Again, Import means the data

Read more

The Big Data Blog Series

Over the last few years I’ve been speaking a lot on the subject of Big Data. I started by giving an intermediate session called “Show Me Whatcha’ Workin’ With”. This session was designed for people who had attended a one hour introductory session that showed you how to load data, to look at possible applications … Continue reading The Big Data Blog Series
Read more

Getting Started with Microsoft Data Explorer

  • 6 March 2013
  • Author: DevinKnight
  • Number of views: 7613

What is Data Explorer

Data Explorer simplifies the data discovery phase for Excel users that are creating self-service Business Intelligence solutions.  It does this by provided straightforward methods for connecting to data previously unheard of, without a developer, in Excel.  It also provides a basic ETL tool for those involved in self-service BI projects all within Excel.

What do I need

Currently Data Explorer is only available as a preview and works with Excel 2010 SP1 or Excel 2013.  You can download the Data Explorer preview from

Enabling the Add-in

Once you download and install the add-in you will have to enable it by going to File –> Options –> Add-Ins.  Then Select COM Add-ins from the Manage dropdown and click Go


Check off Microsoft “Data Explorer” Preview for Excel from the Add-Ins available list then click OK.


Once you have enabled the add-in the DATA EXPLORER tab will appear in the increasingly crowded Office ribbon.


Let’s take a look at what this new add-in has given us.

What does it do

A very detailed list of each element of Data Explorer can be found here

In this post I’ll walk you an example that i think all companies are starting to take a lot more seriously, which is social media sentiment.  In other words how does the public perceive our company.

With the built-in ability to import data from Facebook Data Explorer can very easily analyze things like statuses, likes. comments, and much more.  Let’s walk through an example:

  1. Launch Excel and ensure the Add-in is enables with the steps detailed above.
  2. Select the Data Explorer tab and choose From Facebook from the From Other Sources dropdown selection.
  3. image
  4. You will then be prompted to provide a UserName or object ID.  The default is “me”, which means it will allow you to import data from your personal Facebook account.  However, if you’re an administrator of a corporate Facebook page you could enter that page in here.  For example, I am an admin on the Pragmatic Works page.  So if change the default “me” to PragmaticWorks and set the Connection name to Posts I can see all posts on our corporate page.  Click Apply.
  5. image
  6. Now the true data exploration can begin.  My first step was to hide all the columns I don’t care about.  You can select multiple column headers at once and then right-click to select Hide Columns.
  7. image
  8. Now that we’ve got just the data we care about let’s analyze things like how many likes and comments we’re receiving on our posts.  You will notice on the columns for both comments and likes that the word Link is displayed.  This means there’s more data in a separate object that can be imported.
  9. If you click the word Link it will preview that data in that object as shown below when i clicked on likes.  From this I can tell there were 6 likes on this particular post.  There’s also another option if i click Table that will allow be to see the actual users that liked my corporate post.
  10. image
  11. This is great for exploring but if i actually want to add this data to my query then I would clear my likes search on the Steps page as shown below.
  12. image
  13. For my Marketing team’s analysis they really want to know a count of how many likes and comments we had on each status.  To do this I will navigate back to the likes column in my query and click the Expand button to check off new values i want to return.  For this example i just need the count of likes but if I wanted to see who actually made the like on our post I could return the data.
  14. image
  15. We have the data we want now so hit Done and all the Facebook sentiment data will be imported into Excel.
  16. Now that this data is in Excel we can create a PowerPivot workbook on it or even Power View report that looks something like this:
  17. image

Having the ability to create these kinds of report in a very short amount of time is exactly what our Marketing department needs to analysis our true reach.

Rate this article:
No rating


Other posts by DevinKnight

Please login or register to post comments.