Organizing a Power BI Site

There's several important differences between a site set up in Power BI for Office 365 (SharePoint Online) vs. a Power Pivot Gallery in SharePoint on-premises. One key difference I want to focus on now is how you can organize the Power BI site.

With your Power BI site, you can set up various document libraries within the site and they will all show up on the Power BI app page (i.e., the "app" view in the browser rather than the regular doc library view - see this if you don't know what I mean by that statement). Different document libraries can be used to organize content based on subject area, functional area, or whatever makes sense. The document libraries can also have separate permissions (which might allow you to set security at the document library level which is far less tedious than managing it at the individual file level). When it comes to content arrangement & permissions, the same planning you would have put into structuring SharePoint document libraries still applies. Here's an example of how a Power BI site looks with various document libraries within the same site:

With a Power Pivot Gallery in SharePoint (on-premises), it's associated to one single document library. So, the ability for the Power BI site to display content across various document libraries in the site opens up lots of possibilities.  For details on how to create a document library, see my short video on MSBIAcademy.

You can also create subsites if that would be helpful from an organizational or permissions perspective. Individual subsites do get their own unique Power BI app page, but they share the same Power BI Admin Center.

One last thought for you - notice in the screen shot above how there is a document library for "FAQ and Help." It's got a > symbol at the beginning of the name to ensure it's sorted at the top. Something like this is a great way to deliver documentation to your users. (Hint, hint.)

You Might Also Like...

Creating Document Libraries in Power BI (7 minute video)

Ways to View Content Published to a Power BI Site

Navigating the Power BI Site in Office 365

When is a Data Management Gateway Needed in Power BI?

Recently I've been chatting with some fellow BI professionals about the Data Management Gateway (DMG) in Power BI and when exactly it's required to be used. 

Before we answer this question, let's go through a quick bit of background...

What is the Data Management Gateway?

There are 3 components involved, all of which are set up by the Power BI administrator:

  1. Data Management Gateway (DMG). This is a client agent installed on the on-premises server. The DMG handles encryption, compression, and transmission of data using a service bus. Communicates with O365 using a key provided by the Power BI Gateway.
  2. Power BI Gateway. This is set up in the Power BI Admin Center (in O365). Handles communication with an on-premises server.
  3. Power BI Data Source. This is set up in the Power BI Admin Center (in O365). Connects to a database via the Gateway.

When the administrator creates a Data Source in the Power BI Admin Center, you have 2 choices: To allow cloud access and/or to create an OData feed. If cloud access is allowed, that will permit users of the Power BI app to schedule data refreshes that point to that database. If you create an OData feed, that will expose tables and views to organizational search using Power Query.

When is the Data Management Gateway Required?

SQL Server Database On-Premises:  Yes
Oracle Database On-Premises:  Yes
Windows Azure SQL Database (WASD):  No*
SQL Server Database in an Azure VM:  No*
Public OData Sources:  No

Each of the * items are cloud services associated with Azure. For those types of data sources, Power BI permits the scheduled data refresh to occur using stored credentials in the workbook, or using the secure store service, without requiring the 3 components listed above to be set up by the administrator.  As of now (March 2014), it's currently just the on-premises data sources which require the 3 components listed above.

The advantage of not having to set up the DMG is that it's less steps to set up. If you're using a cloud service as a way to quickly and cheaply set up a data source for temporary use or a one-time analysis, the simplicity is nice.

One disadvantage of not having to set up the DMG is that the Power BI system administrator isn't seeing the data refresh activity occur in the Power BI Admin Center.  As of this release of Power BI, I'm not seeing where data refreshes from cloud sources are being logged. In a managed self-service BI environment, this visibility to what's really happening in the system is very important.

Another disadvantage is that it's a different user experience. Let's say User Emily upload a new workbook which has a data source from SQL Server on-premises. Her scheduled data refresh will fail unless the Power BI administrator has set up the 3 components listed above. However, if User Emily uploads a new workbook which has a data source from an Azure SQL Database, her scheduled refresh will be successful without these extra steps. Since users aren't always aware of where data sources are, this can lead to an inconsistent experience where they're not exactly sure when it "just works" versus when to contact the Power BI administrator for additional help.

Having said all that, I'm sure we all expect that much of how things work will evolve as Power BI matures and grows. It is, after all, in its first release. Am just passing along how I've observed that it's working currently so administrators and users are aware of the behavior.

Finding More Information

Power BI for Office 365 Admin Center Help

Scheduled Refresh FAQ

You Might Also Like...

Overview of Power BI Features End-to-End

Creating an OData Feed in Power BI to Access Corporate Data Sources (7 min. video)

Top 2 Features of Power Query

Power Query is one of the 4 add-ins for Excel which integrate with Power BI for Office 365. I'd like to share what I see are the 2 biggest features of Power Query.

Repeatable Steps


We've heard lots of times that cleaning up data to get it in the correct format can take many hours, and sometimes this work has to be done routinely such as every month. Here's the perfect situation where Power Query can help.

Each step you perform to change the data in some aspect is shown in the "Applied Steps" pane. If you click on each step, you can actually see the state of the data before and after each step was applied.

What makes this ultra cool? The fact that these steps are repeatable the next time you need to go through the same exercise. This is similar to the idea of an Excel macro, but much easier to use.

To me, the time savings and efficiency of repeating steps to cleanse and restructure data is *the* biggest benefit of Power Query.

Shared Queries

One of the inherent downsides to asking business users to publish their own datasets and reports is that the quality and integrity of the data can be questionable depending upon knowledge of the person preparing the data and how thoroughly it's been verified. There's also an increased chance of duplicating work that's already been done by someone else which isn't efficient.  Here's where sharing Power Queries comes in.


If you have a Power BI license, and you are signed in to Power BI from within Excel, you can share a Power Query (once it's complete & validated of course).

When you share a Power Query, the name and description become all searchable.  You can also specify who to share the query with - this would usually include other people, but you could just specify yourself if that makes the most sense.  And, you can even specify a URL with more information about the query (which is a truly excellent feature that I fear will be underutilized).

After it's been shared, you and other users can discover this query when using the Online Search functionality in Power Query. You would search for "Organization" data to discover a shared Power Query.


When you are logged into the Power BI for O365 app (via a web browser), if you click on "My Power BI" at the top right you will be able to view how often your shared queries show up in search + how often your queries are selected for use and by whom. 

What's really great about sharing Power Queries is the underlying logic is done just once and repeated potentially over and over - and you have visibility into who else is using your shared queries - now that takes things to the next level. 

You Might Also Like...

Power Query Data - Should It Be Loaded to Worksheet?

Overview of Power BI Features End-to-End

Overview of Power BI Features End-to-End

The following diagram is an end-to-end overview of the major features of Power BI for Office 365.


The Excel Add-Ins + Office 365

Excel is the environment for data access, preparation, modeling, and visualization of the data.  In addition to consuming data sources directly, there are Enterprise Data Search features to search for shared queries which have already been published to Office 365 - this makes the above diagram become very cyclical in nature as Excel is used to publish as well as consume.

Although you can use just the 4 Excel add-ins and get a lot of capabilities, there are some major pieces of functionality which become "unlocked" by integrating with Power BI in Office 365. SharePoint Online within O365 is the place for collaboration & sharing of workbooks, for scheduling data refreshes, as well as using Q&A to search for data across workbooks much like using a search engine. The ability to share and certify Power Queries also becomes available when the Excel user is signed into an O365 account with Power BI.  The Mobile App, available from the Windows Store, also becomes a possibility when used with O365. 

Within O365, there's primarily two main areas:  the Power BI App where business users will view and interact with workbooks, as well as the Power BI Admin Center which is used by the system administrator to manage security and data connectivity to corporate data sources.  To access corporate data, a business user needs to work with a system administrator to request a gateway and data source to be established; optionally, the administrator can set up an OData feed to expose corporate data for purposes of Enterprise Data Search.

The above diagram represents my understanding of the overall system - if you have any feedback or suggestions on it, I'd love to hear them in the comments.

What's the Additional Functionality Power BI Provides Over an E3 O365 Subscription?

It seems there's a bit of confusion on just what additional functionality Power BI provides over an E3 subscription.  Essentially, you cannot create a Power BI Site without purchasing the Power BI subscription.  When you have provisioned a Power BI Site in Office 365, the additional things you can do include:

  • Use the Power BI Site (which is really just a specialized document library) to share workbooks with coworkers
  • Perform Q&A searches on the workbooks
  • Schedule automatic data refresh
  • Utilize shared Power Queries (part of Enterprise Data Search)
  • Utilize certified Power Queries (part of Data Stewardship)
  • Open up corporate data sources via an OData feed (part of Enterprise Data Search) 
  • Use the Mobile BI App

The chart on this Power BI Pricing page is another way to see how the Power BI subscription is an "add-on" to the E3 subscription.  In the diagram above, the Office 365 box in the middle which contains the Power BI Site and Power BI Admin Center are opened up via the Power BI subscription.

You Might Also Like...

Types of Projects to Consider for Microsoft Power BI

Why Power BI is a Big Deal

Power BI Error - Provider Microsoft.Mashup.OleDb.1 is not registered

In Power BI for Office 365, you can schedule the data in the Excel data model (Power Pivot) to be refreshed daily or weekly. This is one of the additional pieces of functionality available when the Excel workbook has been uploaded to your Power BI App in Office 365.

However, as of this writing (February 2014), scheduled data refresh for data loaded to Power Pivot via Power Query is not yet supported. It will be supported - it just didn't make it in time for the initial release.

If you see an error such as this...

The following system error occurred: Class not registered. The provider 'Microsoft Mashup.OleDb.1' is not registered.

...that tells you that Power Query is present in the Excel file and so data refresh cannot yet be scheduled.


More information about which data sources can be refreshed automatically can be found here:

Scheduled data refresh for workbooks in Power BI for Office 365

Shortcut to Script Individual Columns from a Table-Valued Function in SSMS

I noticed recently that if you use SQL Server Management Studio to script out a Select statement from a table-valued function, SSMS generates a Select *. In the following screen shot, I'm scripting out a Select statement from a table-valued function that's in the AdventureWorks2012 database (this TVF is just an example, not my real function).


In my situation, the Select statement being generated from my inline table-valued function will be going into an SSIS package as the source query for the data flow.  A Select * is generally not thought of as a good thing in most cases anyway, but in an SSIS package usage of a Select * does introduce risk.  If a new column gets added, deleted, or changed in database object - but not in the corresponding SSIS package - then the SSIS package will error out. If your change control processes and regression testing procedures ensure that the function and the SSIS package will always remain in conjunction with each other, then maybe the Select * isn't a big deal. However, ever since my phone rang in the middle of the night about 7 years ago because I made this exact mistake, my preference has been to reduce the risk of the Select * by specifying the columns going into the SSIS pipeline.

So, at this point I was (a) curious about why SSMS scripts out the Select * the way it does for a table-valued function, and (b) thinking about how to get all the individual columns scripted out from my inline table-valued function in a quick way.  Here's where the super duper awesome SQL community comes in. I asked about this SSMS behavior on Twitter with the #sqlhelp tag. Robert Davis (@SQLSoldier) responded that a table-valued function is a routine, like a procedure, and so the output can be variable. This was good insight for me. And MartinSmith (@MartinSmith_) gave me a handy dandy tip about using an SSMS shortcut to script out the Select statement with columns specifically named.

Highlight the "Select * From TVF()" in the SSMS query window and then "Ctrl + Shift + Q" as shown in the following screen shot. The Query Designer window will pop up.  Just copy the new Select statement from the Query Designer window.


And, with that SSMS shortcut, we have a quick way to get the full "Select Column1, Column2, Column3 From TVF()" for use as the source query in SSIS (or whatever other purpose).

Any thoughts?  I'd love to hear them in the comments.

Resources to Study for Exam 70-461

Exam 70-461 is the SQL Server 2012 Querying exam.  There's some aspects of T-SQL that I've not used at all like XML, and certainly a few that I will want to brush up on before heading to this exam.  Here's a few places I found for studying:




Practice Questions

Don't forget that Microsoft often offers the Second Shot program where you get an exam retake for free. Current offers are available here:

Power BI Pricing Has Been Announced

It seems we are a step closer to the Power BI preview becoming ready for release to GA (general availability).  Pricing has been announced here:

I had to look at the following pricing chart for a minute to understand it – usually these kind of charts list the features you have by edition or something to that effect.  However, in this case, the features you have end up being the same (more on that below).  The differences shown below are only related to what prerequisites you already have in order to determine pricing.

Pricing for the E3/E4 plans is shown in the next screen shot.  The importance of E3/E4 is that they come with Office 2013 Professional Plus and SPO Plan 2 – both of which are important prerequisites for Power BI features and functionality.

Let’s say you are an E3 subscriber at $20/month.  Add the $20/month special offer price for Power BI and it’s a total of $40/month per user.  That’s much better pricing than the $52 shown in the far right hand column of the pricing chart.  Hmmm…sounds like a price break to entice more customers to the Enterprise Office 365 plans.

If you do subscribe to the E3 (or E4) plan prior to adding Power BI, you get a *lot* of features which are listed here:  With this pricing model, Microsoft is offering significantly more benefits and value if you purchase Power BI on top of the E3 plan versus standalone Power BI.  Purchasing as an E3 (or E4) add-on is the best deal for those companies who will utilize a good chunk of the extra features.  There's not a price distinction for the producers of information (i.e., the data modelers and report writers who will publish workbooks to Power BI) versus the consumers (i.e., those who may just view content on the web or mobile app) which may be a hurdle in large organizations.  My understanding is that Microsoft has been trying to simplify pricing, so it will be interesting to see if the amount of flexibility for features or users will evolve at some point.  

Keep in mind that if you only have the Office 2013 Professional Plus software, you can still use the Excel components:  Power Pivot, Power Query, Power View, and Power Map.  But…self-service BI becomes far more powerful when you integrate the Excel workbook with Power BI.  What you gain by adding Power BI is the ability to share and collaborate using the BI Sites in Office 365, the ability to schedule data refreshes, and usage of the Mobile BI app.  You can also search for other datasets published to Power BI in order to reuse existing logic (features that minimize duplication of work and reduce the potential for error get a big thumbs-up from me!).  You also get new functionality released “cloud-first” such as the Q&A natural language query ability.

Finding More Information

Office – Power BI Pricing

Chris Webb’s Blog – Power BI Pricing Announced

Office – Power BI for Office 365

ZDNet - Microsoft Pins a Price Tag on its Power BI Business Intelligence Tools

Getting New Power Pivot Features & Other Office 2013 Updates

New Power Pivot Features Released

Have you heard that Power Pivot just got a new Synonyms feature?  Synonyms associate column (attribute) names in a data model with alternate terms so that the Q&A Natural Language Query functionality in Power BI will be able to return results when users search for data using various terminology. Metadata like this will continue to to be more and more important in terms of making the user experience better, but metadata actually isn't what I want to write about today…today I want to focus on how to get new features like the Synonyms in Power Pivot as quickly as possible.

Starting with Excel 2013, Power Pivot is integrated into Excel’s data model. That makes Power Pivot currently dissimilar to Power Query which requires updates to be downloaded and installed. 

Just how do you get Office 2013 updates anyway?

How you get updates depends on how you installed Office 2013. There are two ways to install Office 2013:

  • The Click-to-Run type of installation, or
  • The traditional MSI Windows-based installer

I was inspired to investigate further because, when I checked for the new Synonyms feature in my version of Power Pivot, I didn’t have it yet.  This is because I currently have an MSI-based installation rather than Click-to-Run.

Most installations these days are Click-to-Run by default.  The MSI download media is typically only available to Microsoft customers who have a volume license agreement for Office 2013 or MSDN subscribers.

What is Click-To-Run?

Click-to-Run is a mechanism to install and update Office 2013 software products. It has been around a very long time, but has evolved to be much more sophisticated these days.

Like a traditional software installation, Click-to-Run still installs the Office programs on your local machine and uses local machine resources. However, the installation process is done via streaming which is broken up into small chunks to take it easy on bandwidth.  Even if you use Office 365 or Office on Demand sometimes, if you download the software package it does install and run from your machine.

By default, if you installed Office 2013 using the Click-to-Run method, the machine will receive automatic updates for Office 2013 (such as the update to Power Pivot mentioned at the very beginning of this blog entry). It handles installing the updates in the background during a time when the user isn't actively using components which need to be updated.  Although, depending on the kind of update, it’s possible the user will see a notification or a request for the app to be closed so it can proceed.

Interestingly, Click-to-Run is considered to be virtualized which means that all the Office 2013 program files are isolated.  This isolation allows programs to co-exist (such as Office 2010 and Office 2013 side by side).  The virtualization also allows it to continue to receive updates independently of Windows updates.  The streaming and virtualization behavior of Click-to-Run is based on Microsoft Application Virtualization (App-V) technologies.

The biggest benefit of Click-to-Run is that the updates are pushed automatically which means you don't need to install updates, service packs, or patches.  Users will probably love this – system administrators, well, maybe not all of them will love it. Click-to-Run also touts the benefit of being able to start using the software before it's finished installing.  It can do this because it sets up the most commonly used features first and continues to finish in the background.  These commonly used features are known as the “First Run Experience.” 

How to Check What Office Version You Have and if It's Click-To-Run or Not

To check the version that is installed:

  1. Launch Excel ( or any other Office app) and create a new workbook
  2. Choose File > Account

This is what it looks like if you do *not* have Click-to-Run:


If you do have Click-to-Run, you will also see this option on the Account page:


If automatic updates are enabled in the Update Options, then you should be getting the latest Office 2013 updates as they are released.  Although, new functionality like Synonyms that works in conjunction with Power BI for Office 365 will not arrive via updates (**See update at the bottom of this entry).

What if You Don't Have the Updates You Want?

If you’re in a corporate environment, chances are you won’t have much control over this as IT policies will dictate how Office is installed and updated.  System administrators are likely to handle installations using the Office Deployment Tool (ODT). 

If you do have the Click-to-Run version of Office 2013, make sure that updates are turned on (File > Account > Office Updates section).

If you have the MSI version of Office 2013, you have two choices:  One is to wait until the updates come through via the Windows update process (if it doesn’t conflict with your IT corporate policies, you can allow Windows updates to install automatically under Control Panel > Windows Update > Change Settings) or until you upgrade to the next version for certain updates like Synonyms (**See update at the bottom of this entry).  The other choice is to uninstall Office 2013 and reinstall it using a Click-to-Run version.  From what I’ve read, I believe you are able to keep your same product key.

Before you choose to reinstall Office 2013 with the Click-to-Run method, beware of the following limitations:

  • Not all add-ins and apps will behave the same way
  • Some functionality isn't available (such as SharePoint BCS or Edit in Datasheet functionality)

The full list of known issues can be found here:  On the positive side, search functionality in Outlook is more full-featured with the Click-to-Run.

There are multiple choices for where to download the Click-to-Run software from depending on how it was purchased.  You probably want to start with “My Office” found here: or this link:

Another way to download the Click-to-Run version of Office 2013 would be to log onto your Office 365 account.  Click the gear symbol at the top right, then Office 365 settings, then Software.  If you have the type of O365 account that provides the Office suite, then you’ll see the software listed there.  Additional FAQs can be found here:


** Update on 2014-02-26

Service Pack 1 for Office 2013 has been released.  However, the Synonyms functionality is not part of SP1 for MSI installations as described above. All indications point to Synonyms not being part of the MSI installation until the next version of Office.  Theoretically this makes sense for most customers because the Synonyms only works with Q&A which is part of Power BI for Office 365.  And, if you have Office 365, you probably have the Click-to-Run installation.  

Ways to View Content Published to a Power BI Site

There are multiple ways to view content published to a Power BI site.  Four are mentioned below.  These screen shots were taken during the Public Preview of Power BI (during which I cleverly called my site “PowerBISite”).

Power BI Site

The Power BI Site that we see in the first screen shot is an app that was installed from the SharePoint Store when the site was initially provisioned and set up.  Apps are considered “mini applications” which add and extend functionality.  Even though this view is using the app, I haven’t seen where it’s typically referred to that way (i.e., because the mobile app is the one called Power BI App).  This view seems to be typically referred to as the Power BI Site.

The Power BI Site is what I’d expect most users and report consumers will prefer to use because it offers the thumbnail previews.  Think of this as the front door.

My URL for the Power BI Site in the USA is as follows:

Power BI Site Home Page

The home page for the SharePoint Online site looks like the next screen shot before it is customized.  In the Public Preview, this is where navigation takes me if I click the “Return to SharePoint” hyperlink in the top right-hand corner of the Power BI Site (i.e., the screen shot shown above).  This home page is where you can access the Page properties via the ribbon.

The URL for this page is:

Power BI Site Document Library

There’s also the document library itself.  You can think of this as the traditional “All Documents” view of the first screen shot above – just without the thumbnail previews.  Same content, just a different entry point.  This is where you can get to the File and Library properties via the ribbon, so this view will be useful occasionally.

Think of this as the back door.  The first screen shot above is an endpoint which allows access to this document library.

The URL for the document library is:

Power BI App (aka Power BI Mobile App)

Last but not least, there’s the Power BI App (aka the Power BI Mobile App) which can be downloaded from the Windows Store.  If you hear someone refer to the “Power BI App” most likely they are talking about the Mobile App and not the browser-based Power BI Site (i.e., the app that was initially installed from the SharePoint Store).

Just a sidenote:  A “Featured” report in the browser-based Power BI Site (first screen shot above) isn’t the same thing as a “Favorite” report. Featured reports appear at the top of the Power BI app page, whereas Favorite reports appear in "My Power BI" which is your personal set of reports.  At this point, Favorites in the Power BI App (browser) are not sync'd with Favorites in the Mobile App. I hope that happens at some point.

Getting Started with a Career in BI

imageIt’s unbelievable how often I get asked about where to begin with a career in Business Intelligence (BI).  So, here’s my thoughts on it.  If anyone has additional thoughts I’d love to hear them in the comments.

Are You a Fit for BI?

First, what is it about BI that appeals to you?  I think that’s really important to know yourself well enough to feel confident whether or not this it’s a good fit for you personally.  The following strikes me as important attributes:

  • Desire to solve problems
  • Willingness to understand business needs (and I mean really understand, really “get it” beyond a superficial level)
  • Ability to interact really well with both technical people and business people
  • Capable of learning quickly, and almost constantly considering the technologies are always evolving
  • Natural aptitude for learning “technical stuff”

Notice that it’s really only the last bullet where I mention “technical stuff.”  In BI technical skills are very important, but the soft skills are easily just as important.  How technical you want to go is really up to you.  Wayne Eckerson calls us Purple People and I think it’s true.  In the world of BI we vary from predominantly business users to quasi-technical folks to extremely skilled technical IT people.

Choosing an Entry Point Position for Getting Exposed to BI

I believe the entry point to doing BI work is lower (i.e., easier) than many other IT jobs out there.  The types of jobs I’ve seen most often as an entry point to BI are:

  • Report Writer.  The appeal of starting off as a report writer is that you will learn some querying, such as SQL and MDX, as well as other important things like change control, deployment, and probably even some performance tuning skills.  You’ll also get exposed to some data modeling principles as you work with the underlying data sources.  It’s also quite likely you’ll interact with the ETL team and/or Cube development team (for instance, you wouldn’t want to derive a new measure in 18 reports – you’d want to ask the downstream team if it’s possible to centralize it).
  • Data Analyst.  This term is used a lot and can mean a lot of things.  I’m thinking a role like this would usually be a business-oriented power user that does a lot of Self-Service reporting, data modeling, and data analysis.  This type of person would probably have opportunities to interact with the Corporate BI team quite a bit if they choose to.  This is basically what we usually think of when we throw around the term “power user.”
  • Business Analyst.  I always say that a good BA is hard to find!  To me this is a quasi-technical, quasi-business role.  If you are in a role like this, collecting requirements for BI and analytics type of projects, you would get exposed to all of the concepts.  You would be exposed to the business need, as well as helping to scope and define rules for the data model, ETL, Cube, and Reports.  You would probably work with a project manager to properly define and manage scope (always a huge challenge with BI work!) as well as prioritizing planned enhancements and change requests.  Basically this role could give a good end-to-end experience.

To Specialize or Generalize?

Figuring out if you want to focus on doing BI work as a business user (i.e., those analysts who do Power Pivot models and reporting are doing Self-Service BI) or evolve your skills to become a Corporate BI developer job is a decision you might find yourself considering at some point.  If you do decide to pursue a BI developer job, you’ll also want to give some thought to specializing or not.

Depth vs. breadth is a constant challenge for me personally – both are important.  Some people feel more comfortable specializing and therefore having much deeper knowledge in their area.  Sub-specialties for BI professionals could include:

  • Data Modeling and Data Warehousing
  • ETL and Data Integration
  • OLAP Models Semantic layer
  • Reporting, Dashboards, and Presentation Layer
  • Master Data Management
  • Metadata Management
  • Predictive Analytics and Data Mining
  • Big Data and Streaming Data
  • Mobile Delivery
  • System Architecture

Whatever you choose, if you don’t have a broad background or a grasp of the big picture, please try to start with the basics.  Understand what a good data model is, how data warehousing works, what all the components are.  You might read that data scientists are the hot new thing, or that big data is catching on, but I can’t imagine that starting with these more advanced niches would work for very many people (unless you’re a statistician).

To Focus on a Platform?

I have focused on Microsoft BI for the last several years.  Before that I did some work with Cognos, a bit of WebFocus, and a sprinkle of Hyperion and a couple others I don’t even remember anymore.  There’s so much to continually learn with the Microsoft BI platform that I am personally very happy with focusing on the Microsoft platform.

You’ll want to give some consideration to platform choice, especially if you’re starting an endeavor to ramp up skills.  For a lot of people I think this might happen just due to what you are exposed to in the workplace and that’s ok (that’s how it happened for me).  You might refer to the Gartner Magic Quadrant to get some familiarity with various BI software vendors.  The leaders in the top right will generally have more market share, thus more jobs available.

Suggestions for Getting Started

Here’s some thoughts regarding ramping up your skills.  These are not ordered in any particular way.  Which items make sense for you depends very much on your entry point and your particular focus.

  • Work vs. Formal Education.  Decide if you value work experience or formal education more, or what feels best to you.  There are BI programs available (such as CPCC’s Reach IT program here in Charlotte).  Or, you might feel like getting a “foot in the door” type of job suits you more.
  • Certifications.  The value of certifications is constantly debated.  I think they are immensely valuable for learning the basic concepts, and for getting introduced to a breadth of material that you may not deal with normally.  For Microsoft BI professionals, you would take this certification path to focus on BI.  There is also a certification from TDWI that is platform-agnostic.
  • Build a Solution.  There’s nothing better than real hands-on experience.  Create a virtual machine that has all of the software you need and build a solution.  Evaluation software is free.  Developer editions of SQL Server are very inexpensive.  Codeplex has many starter solutions and sample databases.  Public datasets are becoming easier and easier to find.  All you need is the initiative to go build something that you could use to demonstrate your skills to a potential employer.
  • Read the Kimball books.  The Kimball Group methodology is used significantly at many, many organizations.  Bill Inmon is also very influential in this space.
  • Attend Events.  In the Microsoft community, we are very lucky to have lots of opportunities to learn.  Many of them are free.  There are local user groups, virtual user groups, SQL Saturday events.
  • Talk to People.  Talking to people in the industry could help give you perspective and help you decide what your focus should be.  Heck, it might even help you get a job.
  • Volunteer.  Perhaps a really small organization or a nonprofit would be willing to let you learn as you build something for them.

What did I forget to mention?  Have other suggestions?  Please leave me a comment…

Good luck with your decision! 


Overview of Microsoft Capabilities for Self-Service BI Users

From time to time I'll be contributing to the BlueGranite blog which has lots of great BI information published by my coworkers.

This week I published a blog entry titled "Overview of Microsoft Capabilities for Self-Service BI Users" on the BlueGranite blog.  It provides a quick peek into which tools, features, and capabilities are likely to be used for what purpose and which type of power user the tool may appeal to.  Please check it out!


How Often are Thumbnail Images Refreshed in the Power Pivot Gallery?

Overview:  A brief discussion of how and when the thumbnail preview images in a Power Pivot Gallery in SharePoint get refreshed.

The Power Pivot Gallery is a specialized document library in SharePoint which utilizes Silverlight to render preview images of the report.  These previews are very helpful for users to quickly see if they have the correct report selected before executing it.


Ways to Refresh Thumbnail Images

Originally I had thought there would be a timer job which refreshes the thumbnails at a regular interval.  However, that is not the case.  There are 3 ways I’ve found to get thumbnails refreshed:

1.  Upload a new workbook.  The act of uploading a new workbook causes an event handler which will populate the thumbnail image for that workbook.

2.  Modify a workbook. The act of saving an existing workbook causes an event handler which will update the thumbnail image for that workbook.  Even if all you do is Edit Properties and then Save, that’s enough.

3.  Manually execute GallerySnapshot.exe.  This is a Windows service that runs on the app server where Excel Services is installed.  This exe gets called automatically when a file in the Power Pivot Gallery has been added or changed (an itemAdded or itemUpdated event, respectively, as mentioned in #1 and #2 above).  To run it manually, refer to this information:  Note that this service was called GetSnapshot.exe in SharePoint 2010, and has been renamed to GallerySnapshot.exe in SharePoint 2013.  The SharePoint 2013 GallerySnapshot.exe can be found at:  C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\BIN.

The takeaway:  Since the thumbnails only get regenerated in the above circumstances (and not at a regular interval), the preview images shouldn’t be thought of as something that is intended to coincide exactly with data updates.

Finding More Information

Technet – Refresh a Thumbnail Images

MSDN – Refreshing Power Pivot Gallery Thumbnails

Power Pivot Geek – General Problems with Gallery Snapshots Not Being Taken


Getting Started with Parameters, Variables & Configurations in SSIS 2012

Overview:  This is a high level review of the terminology for configurable items like parameters and variables in SQL Server Integration Services 2012.  This discussion is applicable to the Project Deployment Model only.

Following is a high level flowchart of how values can be passed to parameters, variables and connection managers in SSIS 2012.  The left side represents the SQL Server Data Tools environment in Visual Studio (i.e., during development before the project has been deployed); the right side represents the SSIS Catalog in the Management Studio environment (i.e., after deployment).


The remainder of this blog entry will discuss individual components of the above flowchart.

SSIS Project & Package Parameters

imageProject parameters are new with SSIS 2012.  A project parameter can be shared among all of the packages in an SSIS project.  You want to use a project parameter when, at run-time, the value is the same for all packages. 



imagePackage parameters are also new with SSIS 2012.  A package parameter is exactly the same as a project parameter – except that the scope of a package parameter is the individual package it resides in.  You want to use a package parameter when, at run-time, the value is different for each package. 


Note that project parameters do *not* have an expressions property to define their value.  They are intended to hold a literal value which does not change while the package executes.

See in the image at the top of the page that project parameters can pass a value to variables?  Parameters can also pass values to all kinds of objects in SSIS – basically any property that allows an expression.

You can think of parameters are the replacement for package configurations which were used in earlier versions of SSIS.

More details on defining parameters:

SSIS Variables

imageThere’s actually not too much new here with *package* variables in SSIS 2012 (other than you can move them now which is great).  What can get confusing is that sometimes the environment variables are just referred to as variables – so you need to be aware of the context in which variables are being discussed.  (Environment variables are discussed in the next section below.) 

Within a package, SSIS variables have an expression property.  The expression property, and the ability to change values during the execution of a package if needed, are two fundamental differences variables have from parameters.  A variable can be scoped to the package or an object within the package (there’s no such thing as a project variable though). 

Variables often consume values from parameters within their expressions (an example of this is in the next screen shot).  Variables can also communicate values to objects like Execute SQL tasks, For Each containers, Send Mail tasks, and so on.


More details on SSIS variables:

More details on the differences between parameters and variables:

More details on SSIS variables:

SSIS Environment Variables

imageSSIS environment variables are new with SSIS 2012.  You actually interact with these in Management Studio after the project has been deployed to the SSIS Catalog.  Don’t confuse these with Windows environment variables – although named the same, SSIS environment variables are different than Windows environment variables.  Also don’t confuse these with “regular” variables used within SSIS packages (which are discussed in the previous section above).

An environment variable provides the flexibility to configure values for parameters and connection managers in Management Studio which are different from what was originally specified when the package was deployed.  This is great functionality for the administrator of the SSIS and ETL processes.

An environment(s) and its variables are set up in Management Studio under Integration Services Catalogs.  Once set up under the Environment Properties, these variables can be associated to projects and/or packages.  Since their purpose is to override parameters or connection managers, I propose a similar name to the value it’s intended to replace – but with a prefix (such as EV) that makes it clear where the value is coming from.


If you wish to override the value for a project parameter with an SSIS environment variable, you do this on the “Configure” menu for the project.


Overriding the value for a package parameter with an SSIS environment variable is very similar – it’s just done on the Package’s “Configure” menu instead.

Note that SSIS environment variables can specifically provide values for parameters and connection managers.  SSIS environment variables do not interact directly with the variables contained inside of SSIS packages.

More details on SSIS environment variables:

More details on SSIS environment variables:

SSIS Project Configurations

imageWith the new project deployment model, the concept of configurations is mostly gone.  There is one exception, however.  While in SQL Server Data Tools (Visual Studio), you can specify if any parameters are dependent upon a particular deployment configuration being selected.



This reference to “deployment configurations” are not the configurations you might be thinking of from previous versions of SSIS – rather, these are the deployment configurations available in the project properties.  This deployment Configuration Manager has been available to manage different deployment scenarios for a long while now.  The piece that is new is the ability to associate parameters to these configurations.


This functionality is only available in the Visual Studio development environment, and only applies to project & package parameters.

Hope that helps clarify some of the terminology in SSIS 2012!


PASS Summit Meeting Rooms at the Charlotte Convention Center

imageThe schedule for the PASS Summit in Charlotte has been released.  Since I’m lucky enough to have a session on the schedule, I looked up a map of the Charlotte Convention Center to get the lay of the land.  Having an idea of the room size ahead of time helps me to feel mentally prepared. 

The schedule for the 2013 PASS Summit can be found here:

The layout for the Charlotte Convention Center can be found here:

The rooms highlighted in yellow are the convention center rooms used per the Summit schedule.  I’m guessing the rooms will be set up theatre-style, but that's just a guess.






By the way, we would love to see you at our SQL Saturday the day after Summit concludes.  It’ll be a fun day with 50 sessions on BI, Data Warehousing, Big Data, and Database Administration -- so please join us!  Info can be found here:


Power Query Data–Should it be Loaded to the Worksheet?

Overview:  This post discusses situations when you may *not* want to load Power Query data to the worksheet, but instead to the data model only.  One reason relates to file size limits in Power BI for Office 365; the other reason is to simplifying & de-duplicate the fields displayed in the Pivot Table Field List.

Power Query Settings

When you bring data into Excel via the Power Query add-in, you have some options on the Query Settings pane. 

Enable download:  Needs to be set to On in order to retrieve or download the data from the source.

Load to worksheet:  Specifies whether the data downloaded will be stored in an Excel table within the worksheet.  Default = Yes.

Load to data model:  Specifies whether the data downloaded will be stored in the data model (i.e., Power Pivot).  Default = No.


There’s a couple of reasons why you might want to flip-flop the above settings.  Specifically:

Load to worksheet:  Change to No.

Load to data model:  Set to Yes.

Following are a couple of reasons why you may want to consider loading to the data model only.

File Size Limits for Browser Display in Power BI for Office 365

The default file size limit in Office 365 for browser display is 10MB.  That limit for browser display has been increased to 250MB if all 3 of the following are true:

  • The workbook is enabled for Power BI (which requires SharePoint Online Enterprise E3 or E4 plan), and
  • The workbook contains a data model (i.e., an embedded PowerPivot model), and
  • Data in the workbook outside of the data model doesn’t exceed 10MB.  <--includes Power Query data residing in an Excel table

This means you want to be very careful when working with Power Query to not necessarily import its data to the workbook, particularly if the volume of data is high.  The two-step process to import only to the data model would be:

  1. Move the slider “Load to worksheet” to Off.  <--Important to click this one 1st before it becomes greyed out when you click #2.
  2. Click the “Load to data model” option.


The reason this distinction makes a big difference in the file size is because the data model uses xVelocity compression.  This is a much higher compression level than standard xlsx data.

Duplicates in the Pivot Table Field List

Loading the data to only the data model and not the accompanying workbook has another benefit.  You won’t see the set of data twice in the pivot table field list.


It’s much cleaner having one clean set of fields to choose from.  You can still use the “Filter & Shape” functionality of Power Query without loading the data to the worksheet.

Also, be aware that there’s some situations where Power Pivot will implicitly convert data to the data model, such as:

  • When Power Map is used within an Excel workbook.  Power Map has a dependency on the data model for its functionality, so if you have not explicitly created a Power Pivot data model, it will do so on the fly when you insert a Power Map worksheet.
  • When you import two or more tables at once.
  • When you insert a Power View worksheet using a Table range of cells.

Finding More Information

Office – File Size Limits for SharePoint Online


Navigating the Power BI Site in Office 365

Overview:  This post includes links for getting around Office 365, specifically as it pertains to the use of Office 365 for the Power BI Preview (i.e., not all O365 menus will be explored here).  Intended audience is the system administrator.  Purpose of this blog post is not to discuss functionality, just links and locations within the O365 portal.

Please note that this was published on 9/2/2013 – the look and feel of the menus and page structure will undoubtedly change as O365 progresses.  All screen shots below presume you have administrator privileges to the site, so you may see less things based on your O365 plan and your permissions.

Office 365 Contextual Menus and Links

Power BI is delivered using SharePointOnline within Office 365.  You can think of it as SharePoint in the web using HTML5.  A lot of SharePoint concepts will be very familiar.  However, navigating around is a bit different.

Depending on where you are at in the site, all menus are not visible.  The menus are contextual (i.e., they change based on current context - where you are located in the portal).  Also, breadcrumbs are only minimally used.  So until you have figured out the site and bookmarked some key pages, you might feel like you are hunting around for where you were or where you need to get.  The remainder of this blog entry outlines some of the key places I’ve located while working with the Power BI Preview in O365.

All links below need “YourDomainName” replaced with your valid name for them to work properly.

Admin Centers and O365 Site Settings Links

The Power BI preview uses the E3 (Enterprise 3) plan, which has nearly all features.


Since I have the E3 business plan, I find the following Admin & Settings links to be relevant for my purposes (i.e., testing out the Power BI Preview):

Power BI Admin Center
SharePoint Admin Center
Site Settings
Site Contents
Office 365 Admin Center
Office 365 Settings

(Note that I omitted the Exchange and Lync Admin pages because they aren’t relevant for what I’m doing with the Power BI Preview.  They are of course very important for an Enterprise implementation of Office 365.)

Power BI Admin Center


SharePoint Admin Center


Site Settings


Site Contents


O365 Admin Center


Office 365 Settings


Accessing Sites, Document Libraries, and Documents

The previous section covered accessing various pages for setting up the system.  Now let’s focus on where we will store our content.

Use the “Sites” menu option located in the top toolbar of some higher level pages.


All Sites

The launch page displays sites you have permission to see. 


Power BI Site Home Page

I created a new site called “PowerBISite” because for me it’s just for learning and doing a bit of playing around.  In reality you’ll want to think of it like any other site and give it a good name that suits its purpose.


Power BI Documents

Documents within a Power BI-enabled site are displayed with a preview of the report which looks like this:


Within the site, the contextual menus have additional choices.


Tracking Usage Analytics and Queries

Within the Power BI-enabled site, there is a menu option called “Manage Data.”


Manage Data


Additional Resources for Getting Started with the Power BI Preview

Office 365 – Getting Started with Office 365

Office 365 – Community (Blog, Forums, Wiki, Troubleshooting) 

TechNet – Power BI Forums

Office – Power BI Sites on Power BI for Office 365

Office – Power BI Admin Center Help


Schedule for SQL Saturday 237 in Charlotte

imageThe schedule is posted for SQL Saturday 237 in Charlotte to be held on 10/19/2013 (the day after the PASS Summit concludes).  This BI Edition will have a primary focus on Business Intelligence topics, with some DBA topics as well.  There will be 10 tracks in 5 time slots for a total of 50 sessions (we actually added a track while finalizing the session schedule because we wanted to be able to select more speakers, have more content, and make sure we have enough classroom capacity to hold > 300 attendees).

The topics on the schedule break down into these categories:


What?  You caught that there’s only 48 sessions listed in the chart when I said at the beginning there would be 50?  Got me there.  That just means we might have a surprise or two coming later. 

The levels break down as follows:


The types of speakers breaks down as follows:


All session details can be found here: – Please keep in mind this is the first draft of the schedule, so a few of the time slots are likely to move around a bit before it’s finalized.

If you are able to attend, please register soon!  We already have more than 200 registrations, which is really exciting this far out…so be sure to reserve your spot soon.  If you’re attending PASS Summit, we’d love to see you one more day!  And if you’re near Charlotte and not able to attend Summit, well then, all the more reason to come and join us at SQL Saturday 237!

Any questions?  Send them to SQLSaturday237 at SQLSaturday dot com.


The Role of Power Users in a Self-Service BI Initiative

From time to time I'll be contributing to the BlueGranite blog which has lots of great BI information published by my coworkers.

This week I published a blog entry titled "The Role of Power Users in a Self-Service BI Initiative" on the BlueGranite blog.  It provides a brief overview of Self-Service BI and discusses how business users possess varying needs, abilities, and preferences for working with self-service BI tools and techniques.  Please check it out!