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

Choosing the Right Microsoft Reporting Technology Part 2: Excel

  • 2 May 2012
  • Author: DevinKnight
  • Number of views: 5127

This post is a continuation of a series started to help you understand each of the Microsoft presentation layer tools purpose and when you should choose each for your reporting solution.  The decision to pick the appropriate tool is difficult but so very important as I detailed in part one of this series.  As I continue the discussion here again are the different tools I will detail:

  • Reporting Services
  • Excel (From the perspective of building PivotTables without PowerPivot)
  • PowerPivot
  • PerformancePoint
  • Power View (Yes Power View is supposed to be considered part of Reporting Services in SQL Server 2012, but it really is an entirely different tool than SSRS is traditionally thought of as)

In part one of the series you learned about Reporting Services and the role it plays mainly as a static reporting tool.  Here in part two we will discuss the polar opposite, which is Excel. 


What it is

As you can see in my list I’ve made sure to differentiate Excel and PowerPivot as two separate tools even though PowerPivot is part of (free add in) Excel.  So to be clear when I say Excel I’m referring to Excel PivotTables off of an Analysis Services data source.  Of course any business user will tell you that PivotTables are possible without an OLAP data source, but to keep this relevant to Business Intelligence I will focus on Analysis Services as a data source.  With that focus in mind let’s discuss the tool.

Excel is customarily thought of as an ad hoc reporting tool that is great for getting a quick answer from your data source.  This is where quick analysis can be done without having to wait days for a report developer to define specs, write a source query and then finally build the report.  The report below for example was literally built in less than a minute. 


Usually when I teach Analysis Services to a group my first question is, “How many of you have dozens of reports waiting for you or someone else to build when you get back to work?”  Predictably about half the room raises their hand and I go on to explain that part of the goal when building an OLAP database is to take much of the reporting burden off of you and move that to your more savvy end users that are comfortable in Excel.  This is possible because after you have built the Analysis Services layer Excel can consume it in a way that give users the ability to drag and drop fields into a PivotTable with very little training required.  This was one of the earliest forms of Self services BI.  So now many of those reports that are waiting in your queue can likely easily be created by the users themselves.  Of course this doesn’t solve all reporting needs for users but it can definitely relieve some stress. 

Because Excel is most end users comfort zone you will find they need little training.  In fact admittedly I have learned a thing or two from end users about reporting in Excel.  Your diving into the end user’s bread and butter and that is why this is such a popular option for reporting.  Be wary though because end users love it so much they may start going beyond Excel’s intended purpose as a reporting platform.

What it isn’t

While Excel is great for cranking out reports quickly many think it lacks some customization features.  Now I happen to think this is partially a myth because we’re IT folks and don’t play in Excel all day.  Like I said earlier I’ve had too many experience where an end user has taught me something I didn’t think was possible in Excel.  Even though I identify it as partial myth I would still argue that tools like Reporting Services are far more customizable with with reports they produce. 

Excel also is not necessarily the right tool for static reports.  As mentioned earlier it’s purpose is for ad hoc reporting, but if you use it for more permanent reports it’s not the end of the world.  You may be just miss out on the benefit of other reporting tools.  For example, let’s say your sales team looks at a weekly report of their previous weeks sales compared to last year.  If this report was in excel they could either open the file each week or view it in SharePoint with Excel Services, but the better solution may be to have it emailed directly to the sales team members using a Reporting Services report and subscriptions.  Having it in Reporting Services also opens the doors for features like report caching if performance becomes a problem.

Who Uses it

No long explanation needed here because this tool is for end users as previously detailed. 

A couple caveats I’ll mention here are:

  • Excel is a far superior tool for testing KPIs than the built in Analysis Services KPI browser.
  • In SQL Server 2012 Excel becomes the primary way that developers test tabular BISM designs.

How is it consumed

The two typical ways of consuming Excel PivotTable reports are opening the .xls or .xlsx file itself or SharePoint with Excel Services.  Ideally your company would go the Excel Services route otherwise an unorganized chaos of workbooks being emailed or placed on shared drives can become a mess. 

Excel Services allows you to place the workbook in a web facing version of Excel.  It does not require that Excel is installed on the users machine because they can view the Excel workbook directly through their web browser.  Any content deployed to Excel Services is managed through SharePoint content databases.


One major limitation some find is depending on the version of Excel you are running that row limits exist.  I would argue that you are not using the Excel PivotTables for their intended purpose if you get anywhere near those limits. 

I have also found that occasionally Excel can write some rather poor MDX against a cube with the drag and drop interface and unfortunately you do not have much control over it.  There is a nice free tool available called OLAP PivotTable Extensions which allow you to right-click on a cell and it provides the MDX that led to the results.  This can be pretty helpful when you’re first learning MDX (the query language for Multidimensional Analysis Services).


As we go through this series remember these high level characteristics about Excel:

  • Most end user preference
  • For ad hoc report development
  • Can be used for static reports but may not be best fit
  • Excel Services with SharePoint best way to store and share reports

I hope you’ve found this helpful and stay tuned for the Part 3 in this series on PowerPivot.  To read any of the other parts to this series follow the links below.

Rate this article:
No rating


Other posts by DevinKnight

Please login or register to post comments.