Understanding
Microsoft’s
BI Tools
The purpose of this document is to provide a high level understanding of what tools Microsoft has to support the concepts of data warehousing, business intelligence, and reporting. There are 3 areas to view their support of these processes. The tooling, overlap, and usage will be noted below.
Contents
Data at Rest ...3
SQL Server Data Engine Columnstore Indexes ...4
Parallel Data Warehouse (PDW) ...4
SQL Server Analysis Services Multidimensional Models ...5
Tabular Models ...5
Power Pivot Power Pivot for Excel ...6
Power Pivot for SharePoint...7
“Upgrading Power Pivot” ...7
Data in Motion ...8
SQL Server Integration Services ...9
SSAS Multidimensional Model Processing ...9
BizTalk ...9
xVelocity In-Memory Analytics Engine Data Refresh ... 10
SQL Server Service Broker ... 10
Power Query ... 10
Data Visualization ... 11
SQL Server Reporting Services SSRS Standalone ...12
SSRS Integrated with SharePoint ...12
SSRS Report Builder ...12
PerformancePoint Services ...13
Excel ...13
Power View ...14
Power Map ...14
Other Tools ... 15
Data Mining ...16
HDInsight ...16
Power Q & A ...16
Data at Rest
Data at rest refers to data storage techniques used to provide or host
SQL Server
Data Engine
This is the most obvious storage or data at rest component. This is a relational database management system (RDBMS) with various
optimizations used to support data warehouse and data mart solutions.
Columnstore Indexes
This capability uses the xVelocity In-Memory Analytics Engine to greatly improve query performance against large tables within a data warehouse or data mart.
Parallel Data
Warehouse
(PDW)
The PDW is an appliance that is used to provide massively parallel processing (MMP) solutions in the multiple terabyte to petabyte sizes.
The appliance starts at a half rack and can be expanded from there.
PolyBase has the ability to use a single query language (T-SQL) to query relational and Hadoop stores. PolyBase is currently only available in PDW.
Azure SQL Database is a relational database engine built on Windows Azure. It is truly implemented as Platform as a Service allowing users to create databases on a shared server instance. This supports the core relational database functionality and is continually increasing in capability. Currently Azure SQL Databases are limited in size to 150 GB.
However, these databases have high availability built in and most server level management is handled by Microsoft, thus reducing overall cost of ownership.
4
SQL Server
Analysis
Services
(SSAS)
SQL Server Analysis Services supports two types of data storage.
Multidimensional Models
The most familiar storage is called multidimensional models. This covers the traditional cube storage techniques. Multidimensional models support large amounts of data which can be highly indexed and aggregated for efficient querying and extensive analysis. The largest cube in the world has exceeded 20 terabytes in size which, due to compression, consumes a data mart in excess of 250 terabytes. Multidimensional models are intended to be used against data marts or data warehouses designed around the Kimball dimensional model – star schemas with dimensions and facts.
Tabular Models
Tabular models were introduced in SQL Server 2012 and are a direct descendant of the Power Pivot capability that was introduced previously in Excel. Tabular models are an in-memory data store also driven by the xVelocity In-Memory Analytics Engine. They can scale to the amount of memory supported on the server. These models require less design time and can perform much better than multidimensional models.
They are limited in size by the amount of memory that is available, but compression is usually 10:1 or better so quite a bit of data can be stored in this way.
Power Pivot
Power Pivot is the original in-memory storage introduced by Microsoft as Vertipaq. Vertipaq is now called the xVelocity In-Memory Analytics Engine. What makes Power Pivot and the SSAS Tabular Models sopowerful is their ability to mash up data easily. Users can open the Power Pivot window and pull data from a variety of data sources including
relational databases, SSRS reports, SharePoint lists, text files, OData feeds, and even other Power Pivot models. Once the data is pulled into PowerPivot, relationships between previously unrelated data sources can be made and analysis done in a similar fashion to SSAS Multidimensional Models. This data is loaded in memory and stored with the Excel
spreadsheet when done.
There are two flavors of Power Pivot – Power Pivot for Excel and Power Pivot for SharePoint.
Power Pivot for Excel
Power Pivot for Excel was originally delivered as a SQL Server Add-In for Excel 2010. It is still available for Excel 2010 as a separate
add-in. With Excel 2013, Power Pivot is built into Excel and the advanced features of Power Pivot can be accessed by enabling it in Excel’s option menu with no further downloads required. For all things in-memory, Excel’s Power Pivot is the place to start. Once a model is created here it can be shared in SharePoint and eventually deployed to SSAS Tabular.
6
Power Pivot
(cont.)
Power Pivot for SharePoint
Power Pivot capabilities in SharePoint are available in SharePoint 2010 and SharePoint 2013. Office 365 with Power BI also supports Power Pivot, but is limited to smaller models (up to 250 MB). When an Excel workbook with Power Pivot data is uploaded to SharePoint and SharePoint’s Power Pivot support is enabled, the Power Pivot data is moved to SharePoint’s SSAS instance. This SSAS instance is entirely controlled by the SharePoint farm and is not available for general use or access. However, now the Power Pivot data is available for others to use. When deployed to
SharePoint, Power Pivot tables can have scheduled refreshes and Power Pivot data can be used as a standalone data source.
“Upgrading Power Pivot”
The last piece of the Power Pivot scenario is that a Power Pivot model can be imported into a SSAS Tabular model with one click. This is done by creating a new Tabular model project in Visual Studio (2010 or above) with the latest SQL Server Data Tools and selecting “Import from Power Pivot.” Once this is complete, the Power Pivot model that started in Excel will now be managed in SSAS Tabular.
Data in Motion
Data has to get to a place of rest in some way. Microsoft has some basic tools for moving data.
SQL Server
Integration
Services
SSIS is the enterprise data movement and transformation tool in the Microsoft stack. It is capable of moving data from a wide variety of sources to a wide variety of destinations. Along the way, fairly complex transformations and reconfigurations of the data is possible. SSIS
operates as a batch movement tool. This means it is not the best tool for any real-time data movement, but is very efficient at moving large amounts of data. It is the ideal tool to load data marts, data warehouses, or operational data stores.
SSAS
Multidimesional
Model
Processing
The SSAS Multidimensional Models are loaded by “processing” the
objects. Processing essentially executes a number of dynamically created queries against the data sources to load the dimensions and measure groups within an SSAS database.
BizTalk
BizTalk is another method to move data to data marts or warehouses.This tool focuses on individual messages and sending that data to the appropriate destination. BizTalk should be considered if real time is a requirement and there are other uses for BizTalk within the enterprise.
xVelocity
In-Memory
Analytics
Engine Data
Refresh
All xVelocity tools, Power Pivot and SSAS Tabular models, use a concept called “refresh” to load the models. In Power Pivot, the refreshes are done at the table level. This means that in all cases, the data in the table is removed and reloaded from the source. The level of refresh granularity in this case is at the table level. Therefore, large tables will take longer to refresh. In SSAS Tabular, there is the capability to use partitions to limit the scope of the refresh to the more volatile data segments within a table.
This will allow for a much more efficient refresh scenario and is one more reason to upgrade a PowerPivot model to a Tabular model.
SQL Server
Service Broker
SQL Server Service Broker is an asynchronous messaging system allowing data to be moved in a more real time fashion. When considering real time capabilities within a data mart or data warehouse, Service Broker is a valid option and much easier to maintain and implement than BizTalk over time.
Power Query
Power Query is an Excel add-in that brings data movement andtransformation capabilities into Excel. Power Query natively supports more data sources than Power Pivot does including Hadoop and
HDInsight. More importantly, Power Query also supports “shaping” data and using a number of steps to shape the data. This is similar in practice to using SSIS to transform data through a series of tasks or packages.
Each time the data is refreshed in Excel it runs the data through the same shaping steps.
10
Data Visualization
Data visualization takes many forms including reports, dashboards, spreadsheets,
SQL Server
Reporting
Services
(SSRS)
SQL Server Reporting Services can be used as a standalone product or integrated into SharePoint. In both cases, the product is primarily used to create pixel-perfect reporting for the purpose of printing.
There is a significant amount of controls that can be used to visualize the data ranging from tables to charts to maps. When deployed as an HTML report, SSRS supports a level of interactivity including show/hide capabilities and drill through reporting using pass through parameters.
SSRS Standalone
The standalone implementation of SSRS has a lightweight reporting portal that can be used to expose reports. This portal is not easily branded or customized. It supports basic report organization and security.
SSRS Integrated with SharePoint
When SSRS is integrated into SharePoint, the reporting capability is built into a portal that fully supports security and customization. SharePoint also has support for SSRS allowing users to create reports for use as webparts for more complex dashboarding.
SSRS Report Builder
Report builder is an “Office-like” design experience for building reports. It is supported in both standalone and integrated modes. It is very effective when used with SharePoint as it allows users to create reports in a library and Report Builder understands the report location as well as any shared components used within the report such as Shared Data Sources.
12
Performance
Point Services
PerformancePoint Services (PPS) has been embedded into the Enterprise Edition of SharePoint since SharePoint 2010. PPS is used to design and deploy dashboards within SharePoint. The Dashboard Builder allows users to build reports, charts, KPIs, and scorecards. Once created, these can be deployed to a PerformancePoint Dashboard or to a PPS webpart for use within a SharePoint page. PPS supports a high level of interactivity when used with SQL Server Analysis Services, so much so that usage with other data sources is not recommended for most visualizations with the exception of KPI targets. PerformancePoint also includes one of the most sought after visualizations in the industry, the Decomposition Tree, which allows meaningful drill capability across SSAS data.
Excel
Excel 2013 is truly the first version of Excel that is a fully functional BI client tool. While always used to analyze data, the latest version of Excel has gone way beyond a spreadsheet. At its most basic level, Excel provides pivot tables, pivot charts, conditional formatting, sparklines, and OLAP tools. These features allow users to create sophisticated dashboards and reports within Excel for use within SharePoint or Office365. Excel also has added data slicers, timeline slicers, and Office Apps which expand the visualization functionality within Excel to produce compelling dashboards.Power View
Power View was created by the SQL Server Reporting Services team to allow for simple (all within 2 clicks) and compelling visualizations. It was released originally in SharePoint 2010 with the SSRS add-in. Microsoft has also made this available in Excel 2013 making this functionality easily available to the standard Excel user. At the current time, Power View only supports data stored in Power Pivot, SSAS Tabular, and SSAS Multidimensional models. The timeline view and mapping are some of the key visualization elements available in Power View. One drawback to using Power View is that it is built in Silverlight which means it cannot be viewed on iPad or SurfaceRT unless deployed through Office 365 which uses HTML5.Power Map
Power Map is a 3D visualization in Excel with Office 365. It allows users to create compelling stories over space (geography) and time. Once completed, these visualizations can be saved as movies for viewing on websites and in meetings.14
Other Tools
There are some other tools that do not fit into this scenario.
Data Mining
Data Mining is currently available in SSAS multidimensional models and in Excel as a free add-in. Microsoft has made a number of data mining algorithms freely available in these tools. While not frequently used or updated, these can be very valuable to customers who want to do some basic data mining without paying for tools such as SAS.HDInsight
HDInsight is Microsoft’s Hadoop-based service in Windows Azure.This allows users to implement big data solutions using HDFS without incurring setup and maintenance on site.
Power Q & A
Power Q & A is a part of Office 365 with Power BI. This tool allows users to ask natural language questions and search the BI solution for answers.An example is “which country did we sell the most widgets in last year?”
Power Q &A will start returning results as the question is being entered and visualize it with Power View.
16
Tweet this!
Reading Magenic’s
“Understanding Microsoft’s BI Tools”
This can be a paragraph about Magenic and
probably specifically about Magenic’s BI practices.
Maybe a little about Steve Hughes? I dunno, but its a good space for it. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donec ut nisl vulputate, malesuada urna at, tristique mi. Donec sodales pulvinar orci vitae suscipit. Proin eget
tempor nibh. Nullam eu metus ut metus sollicitudin condimentum sit amet ut sapien. Curabitur at tortor augue. Morbi vitae mauris ut ante.
Want to learn more about Microsoft BI?
Read about Microsoft BI Strategies and learn ways to utilize these tools.
Or contact Magenic and see how we can help you.
