Glossary of Terms Used in Microsoft BI Projects

14 03 2015

At the beginning of many new BI projects it can be challenging to get everyone using common terminology and language.  This is especially true when people have biases with different vendor products or may have learned to use terms with different meanings.  Miscommunication can be costly on a project so getting everyone on the same page is important.  This list is intended bridge the gap for technical and non-technical members of the project effort.

Microsoft Business Intelligence Solution Components

Using common terminology and language helps stakeholders, developers and project leaders to have more effective communication. It is very common for these and other terms to be used differently because of prior experience with products and tools. The following terms are most commonly used in Microsoft BI projects:


A dashboard is a style of report and analytic data visualization that brings important data to the surface for decision-making. Like the dashboard in a car, a data dashboard tells the operator what’s happening at an aggregate level without the clutter and confusion of finite detail reporting. Dashboards may contain links and actions allowing a user to drill-through to a detailed report outside of the dashboard.

Characteristics of an effective dashboard:

· Contains the most important business metrics that may be measures across different groups of categories and time periods.

· Emphasis on simplicity and actionable information.

· Each visualization should be consumable in a few seconds.

· Data dashboards are typically comprised of common visual elements that bring data together from different systems and functional areas of the business.

· Common dashboard elements include:

  • Summary metrics (a single measurement value)
  • Key performance indicators (KPIs) that show how a metric is performing against a target or goal. KPIs are often visualized as traffic light indicators or trend arrows.
  • Column and bar charts that show segmented metric values across different categories.
  • Trend charts that show metric values trending across time (e.g. years, months, days, etc.)
  • Sparklines that reduce chart visuals to a thumbnail to show a trend for each item in a series.
  • Gauges and bullet graphs that show how a metric compares to a target, goal or threshold; on a scale.

Dashboard style reports can be created using conventional report design tools (that developers use to create operational reports), or specialized dashboard design tools. Tool choice affects the sophistication of the dashboard, interactivity behavior and the level of effort needed for design.

Drill down

A drill-down action allows the user to view more details without leaving the dashboard. This is often done by expanding the levels of a hierarchy or double-clicking on a level to show members of the next level in the hierarchy. The key to enabling drill-down reporting is to understand hierarchal structures in the data and design them into a data model which is used to serve data to the dashboard.

Drill through

A drill-through action allows a user to navigate from a dashboard or report to another report to see greater detail. The typical drill-through experience allows the user to see more details within the context of the item they selected.

A well-designed drill-through dashboard solution keeps the initial dashboard simple and high-performing, allowing users to navigate to different reports in different subject areas and levels of detail.

Data model

Most modern dashboard and self-service data analysis tools rely on a specialized semantic data model to optimize data for analysis. Microsoft’s BI semantic modeling platforms include Analysis Services and Power Pivot. A data model is different compared to a relational database (like Oracle or SQL Server). Common terms used to refer to these models are “cubes”, “tabular models” and “in-memory models”.

Model data is stored in hierarchal structures with pre-defined aggregated measures, calculations and KPIs. Reading data from a semantic model is typically faster and easier using tools designed for business analysts rather than application developers. Model data can also be used by developers to create more sophisticated reports and dashboards. In a well-designed BI solution, a relational database (called a data mart or dimensional model) is usually created as the foundational data source for the semantic model and then data from that database is processed into the model.

Data mart

A data mart is a relational database created specifically to store analytic data that will be processed into a semantic model for reporting. There are different philosophies and approaches in the industry for preparing data for analysis but the essential concepts are the same. A data mart may either be a component of, or an alternative to, a larger data warehouse. Commonly referred to (in general terms) as “star schema” or “dimensional model”, a data mart holds a copy of data intended for reporting. In many modern Business Intelligence solutions, data moves through three (or more) different storage layers: operational database systems, data mart and the semantic model.

* Prior to the proliferation of semantic modeling technologies like SQL Server Analysis Services, dashboard-like reports often queries the relational data mart or data warehouse directly. However, modern tools like Power View and Excel are optimized to query cubes and tabular models. Therefor the term “model” now refers to a semantic model or cube and the term data mart refers to a relational database.

Reporting Services

SQL Server Reporting Services is a product included in the Microsoft SQL Server suite. SSRS reports are typically created by skilled report designers or developers using one of these report design tools:

· Visual Studio (an application development tool used by IT developers)

· SQL Server Data Tools (“SSDT” is a simplified version of Visual Studio that installs with the SQL Server management tools)

· Report Builder (a simplified version of the design tool for moderate-advanced skilled designers)

Compared to self-service tools like Power View, SSRS offers the advantage of more sophisticated and intricate dashboards & reports. These reports take more effort and time to develop. A server licensed for SQL Server can be used as a report server with no additional licensing costs. A new report server must be licensed according to Microsoft’s SQL Server licensing policy.

Power View

Power View is a self-service dashboard and analysis tool that is highly interactive and easy to use. Dashboards and reports can be created by a report designer and saved to SharePoint for users. Users with moderate data skills can create their own dashboards and reports. Power View requires Analysis Services cubes, tabular models, or Power Pivot models; and cannot be used with data in a relational database.

This tool emphasizes interactive data exploration, and slicing and filtering data in a semantic model but it doesn’t have the same level of sophistication to customize reports as Reporting Services. The built-in data slicing, interactive filtering and quick design experience are superior to SSRS.

Power View is currently (as of march 2015) integrated into four different Microsoft products:

  • Using SharePoint that has been configured for BI integration, Power View reports can be created by SharePoint users without installing special software (just the Silverlight add-in for your web browser).  Reports are saved to a Gallery type library and can be shared and used by many users.
  • Power View is a pre-installed add-in for Excel 2013 ProPlus edition that can be used on a desktop computer by one user at a time.  The data analyzed in this way can either be a Power Pivot model stored in the same Excel workbook or an SSAS Tabular model on a shared server.
  • Using SharePoint within your organization or SharePoint Online (Power BI option for an Office 365 Enterprise subscription), an Excel Power View report can be published and shared by users in your organization.
  • Although not referred to as “Power View”, the next version of Power View features are part of the new Power BI subscription that is currently in preview.  It is a less-expensive cloud-based subscription that doesn’t require an Office 365 subscription or Excel.

SQL Server

Microsoft relational database platform (similar in many ways to Oracle, IBM DB2 and MySQL) is used to manage data in enterprise-scale databases. SQL Server is used for transactional/operational databases (supporting applications to run the business.) In a BI solution, SQL Server is typically used for ETL data staging, data warehouses and data marts.

Analysis Services

Also a component of the SQL Server product suite, Analysis Services is a data storage and semantic modeling technology used to optimize data for dashboards, analytic reporting and self-service analysis. SSAS now comes in two flavors: multidimensional and tabular. The conventional multidimensional product, which was introduced with SQL Server 7.0 in 1998, is both sophisticated and mature. The tabular option, introduced with SQL Server 2012, is less feature-laden but can be faster to develop and easier to implement. Tabular models run entirely in-memory which makes them fast, typically without the need for advanced tuning.

Tabular is available in SQL Server Developer, BI and Enterprise editions. Standard edition includes SSAS multidimensional with a limited feature set. SSAS Tabular runs on the same service platform as multidimensional and has a similar management experience. Both options have pros and cons due to the relative maturity of the development tool, advanced features and existing design skills that may favor the established tool.

New Power BI Designer Tutorial

3 02 2015

When the Power BI Designer Preview was released a few months ago I set out to reproduce a solution I had created using the Excel 2013 Power BI tools and hit a couple of road blocks.  With a little help from my friends, community members and some bull-headed persistence, I’m proud to say that I’ve prevailed and I’m pretty hyped about what this thing can do.  The PBID includes all the goodness of Power Query and a supercharged version on Power View but it currently lacks some of the advanced modeling capabilities of Power Pivot.  Since I’ve been using Power Pivot for a few years now, I’ve figured out how to solve certain problems using DAX but hadn’t really left the shallow part of the pool with the Power Query M language.  Well, there’s nothing like a good challenge to force you into the deep water.  I reached out to some folks in the community and really appreciate the assistance from Chris Webb, and Matthew Roche & Curt Hagenlocher from Microsoft.  I’m certainly not an ‘M’ guru yet but it’s been fun working with this powerful data transformation tool.  The new video uses the UFO sightings data from the National UFO Reporting Center that I used in the Excel tools.

I’ve created another video demo/tutorial from my experience.  It’s on my YouTube channel and embedded into this page on my blog:

So far:

  • Power BI Designer Solution (part 1 of 2)
  • Power BI Designer Solution (part 2 of 2)
  • Power BI Complete Demonstration – Part 1 Power Query
  • …Part 2 Power Pivot
  • …Part 3 Power View
  • …Part 4 Data Correlation
  • …Part 5 Publish to Office 365

BASIS Band is Almost a Great Device… Almost

19 10 2014

Basis Band

I started shopping for a wearable fitness band several months ago.  I looked at several and was most impressed with the BASIS Band concept.  It was different because it combined a device that had advanced tracking capabilities with a cloud-based service that could be expanded and improved with only server-side upgrades.  What a great business model, I thought.  reading the forums, I could see that the BASIS customer service folks were very responsive to feedback and feature requests.  A lot of people were also very excited to see this service expand and were eagerly asking for more features and capabilities that could be added to the back-end service or software.  BASIS didn’t yet have integration with popular fitness apps but that was coming.  You couldn’t yet download your data to analyze it yourself but, given their centralized approach to synchronizing the device and managing all this data, it would only be a matter of time before they would add this capability.  They offered an iPhone and Android app but nothing for Windows or the Windows Phone, but again, a simple API would enable the developer community to do to some great things.  Since the BASIS collected more sensor data than most other devices, I was excited at the prospect of being able to analyze my own fitness data with my own tools.  This just seemed like a slam dunk with some much opportunity.  I could just imagine being able to chart and trend my heart rate, perspiration and calories during and after after workouts; over weeks and month of progress.

Back in June when I started shopping, I could go to the Nike Store and look at their bands.  The JawBone and FitBit bands were on display everywhere, and there were many others that I could touch and look at but the BASIS Band wasn’t on display anywhere; only available online.  As an underdog brand contender with all of their advertising claims, I reasoned that the device itself must be just as good as the competitors so I took the plunge and ordered a BASIS Carbon Steel directly from the company for $199.  Shortly after my purchase, the company announced that they had been purchased by Intel.  Wow! How cool was it that this small start-up was now part of one of the most innovative technology companies on the planet?  Great things must be on the way!  I felt a little slighted when they lowered the retail price of the band I had just purchased to $149 but with all the upgrades coming, I could live with that.

After nearly four months as a BASIS customer, this has been one of the most disappointing investments I have ever made.  They were so close to getting it right by listening to and responding to their customers but they just didn’t do it.  Their support forum is full of questions asking why this company has ignored such passionate customers.  I’ve bought a lot of junk in my life but the BASIS is something that COULD HAVE BEEN GREAT but isn’t.  If you’re considering the BASIS, take some time to read through their Community Forums and the comments from customers.  I’ve posted a few comments and suggestions over the past few months like this one.  I’ve also asked that the customer service people who always respond very quickly with answers like “Thank you so much. We take your feedback seriously but can’t respond with any specific information…” to ask company leaders or developers from the company who can answer these questions to respond.

I had so much hope for BASIS to be cool and useful for me and many others – and it could be, in my opinion, if the company would simply listen to their customers and respond to them.  They would earn the respect of many technologist if they would release an API so the software developer community could develop apps and integration, and the ability to download, export or feed a users personal fitness data to an app or tool of their choosing.  Until they do that, BASIS has very limited use for me.

PEAK Next Generation Band

Will the next generation PEAK be better than the B1 or Carbon Steel?  Of course it will but when you use in a BASIS, you are investing in a complete service and the whole thing has to work together any value from it.  Here’s a good article comparing the new Peak with previous models.  I expect the Peak band to address some of the shortcoming of the device but the biggest shortfall of Basis is their limited service and lack of integration.  If they can fix that, and the damage already done to their reputation by waiting so long, they could have a good product.

What Exactly Is BASIS?

Basis really consists of three different components: The band itself, the synchronization software and the web-based service.  All three must be used or the band is useless.  In my opinion there should be four components, including an API or web service to allow developers to integrate Basis with their own applications.  This is so obvious, in my opinion, that I have included it in one of the grading criteria in my score below.

The Carbon Steel band is bulky but bulky men’s watches are fashionable so that didn’t bother me.  The LCD display is dim and very difficult to read.  It feels like an LCD watch from the 1980s.  The backlight stays on for a short time and it can’t be read during an outdoor run or bike ride.

The Basis Sync application that runs on the desktop looks like a Windows application written ten years ago in Visual Basic 6 in about two hours.  It’s light on features and synchronization fails about a third of the time.  Unplug the device & plug it back in, close the app and re-open it, or wait and try later; and it will then synchronize with the Basis cloud service.

The online service and web site are quite functional.  The site is attractive and the analytic graphic and charting is actually quite impressive.  I featured both the daily fitness statistics time line and sleep analysis in this blog post:  Basis also sends alters and reminders.  They have this goal tracking and point system to keep you motivated.  All of that is well-designed and works well if you want to use your device in this way.

Where the service breaks-down is if you want to analyze more than a days’ worth of stats.  You just can’t. Period.  Since you can’t download or integrate any of “your” data, you can’t use it outside of the few features offered by Basis.  In short, it’s not really “your data” to analyze.

The accuracy of the device is constantly under scrutiny.  Basis has a lot of monitoring capabilities and tracks heart rate, perspiration, skin temperature and movement.  With this, it analyzes different activities, sleep quality and calories.  I have not measured its accuracy against other devices so I don’t a strong opinion.  I can say that it has worked well for me and from my reading, Basis seems to work as well as or better than most competitive devices.  I would rate the functionality of the device quite high but the display is very poor in my opinion.

Setting the time and time zone.  You can’t.  There are no settings at all on the device.  It sets the time when it synchronizes and you cannot change the time, date or the time zone without changing your online profile.  This makes the band useless as a watch when you travel.

Alarms, calendar, reminders – None.  This $200 “sophisticated” device lacks all of these common features of a $20 Casio watch.

Grading Basis

Device – B

Synchronization software – D

Web site, on-line analysis and daily trending – B

Integration & API support – F

Overall grade:  D+


Potential: Could have been an A.  Now a C if they get their act together.


Get every new post delivered to your Inbox.

Join 288 other followers