Glossary of Terms Used in Microsoft BI Projects

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:

Dashboard

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.