<< VIEW FULL RESOURCE LIBRARY

Microsoft for BI and DW

November 05, 2015

Microsoft

Using the Right Tool for the Job

UPDATE: During this webinar, we discussed a Feature Set Comparison chart that lists case uses and the best tool for the job. View our updated Microsoft Tools for BI and DW Reference Guide for 2018.

Organizations that use the Microsoft platform to build their BI and data warehouse solutions have a wealth of resources at their disposal. Understanding these assets and how they can work together allows users to build an efficient, integrated BI solution.

This webinar recording peels back the lid on Microsoft’s offering of its BI and DW related products, including:

  • A review of product features and benefits
  • Discuss use cases
  • Demo of product capabilities

In addition, we touch on the core functionalities of these six products:

  • Power BI
  • Azure
  • Office
  • SQL Server
  • SharePoint

Knowing these functionalities allows users to come away with a solid understanding of the role of each product and how they work together to create an integrated BI solution. 

Watch our webinar recording, Power BI: Beyond the Buzz, to get the straight talk on Microsoft’s data visualization tool for BI. Learn how Power BI fits into Microsoft’s overall business intelligence framework for 2018. We review what it is (and what it is not) and demo some of its more prominent features.

TECHNOLOGIES COVERED

Microsoft, all database, data warehouse, and business intelligence related products (front and back end)

RECOMMENDED AUDIENCE

BI Report Authors, BI Report Consumers, BI Power Users (Developers, Support Staff), BI Managers, IT Managers, IT / BI Executives (CTO), Program or Project Managers, anyone involved with the creation or use of a BI or data warehouse system

PRESENTER

Paul B. Felix
Senior BI Architect
Senturus, Inc

Paul is an agile business intelligence and data warehousing junkie living in Austin, Texas. He spends his time helping organizations use data to make better decisions by implementing BI/DW solutions.

PRESENTATION OUTLINE

Microsoft for BI and DW Tools

  • Power BI
  • Azure
  • Office
  • SQL Server and related products
  • SharePoint

The Microsoft BI/DW Ecosystem
SQL Server

  • Relational DB
  • Tabular SSAS
  • Multidimensional SSAS
  • Reporting Services
  • Integration Services
  • Master Data Services
  • Data Quality Services

Power BI

  • Data
  • Reports
  • Dashboards

Azure

  • HDInsight
  • Database as a Service
  • Data Factory
  • SQL DW (MPP)
  • Stream Analytics
  • Machine Learning

Office & SharePoint

  • Excel
  • Power Pivot
  • Power Query
  • Power View
  • Power Map
  • Data Mining
  • Access
  • SharePoint

POWER BI
BI front-end SaaS application

Notes

  • Easy to use
  • Nice visualizations
  • Mobile support (multi-platform)
  • Limited feature set

Cost

  • Subscription schedule: free to $9.99 user per month

AZURE – HDINSIGHT

  • Managed Hadoop clusters

Notes

  • Easy to get started
  • Well integrated with toolset (Hive, Pig, Spark, Excel)
  • Multiple development languages (java, .net)

Cost

  • Subscription schedule: $60 to $1,832 node per month

AZURE – DATA FACTORY
SaaS ETL Tool

Notes

  • Well integrated with other Azure services
  • Built-in monitoring
  • SaaS benefits

Cost

  • Activity frequency and location based (on premise or cloud)
  • Subscription priced monthly per activity

AZURE – DATABASE AS A SERVICE
Relational database as a service

Notes

  • Easily scaled
  • No need to maintain host hardware
  • Familiar SQL Server environment

Cost

  • Database throughput units
  • Subscription price is based on use

AZURE – SQL DATA WAREHOUSE (MPP) PREVIEW
Data warehouse as a service using massively parallel processing architecture

Notes

  • Easily scaled and easy to get started
  • Polybase (structure and unstructured SQL to Hadoop)
  • Fair amount of T-SQL support

Cost

  • Database throughput units

AZURE – STREAM ANALYTICS
Stream processing as a service

Notes

  • Real time analysis of internet of things – easily scaled
  • SQL based language

Cost

  • Pricing based on volume of data and number of streams

AZURE – MACHINE LEARNING
Predictive analytics platform as a service

Notes

  • Easily scaled and shared
  • R and Python support
  • Customizable

Cost

  • Free and standard pricing options

SHAREPOINT
CMS (content management system)

Notes

  • Huge array of features
  • Easy to use and customize
  • Plenty of skilled resources

Cost

  • Office 365 subscription or Office licensing

OFFICE - EXCEL
Spreadsheets and much more

Notes

  • Easily connect to large array of data sources
  • Highly customizable
  • Nice set of built-in data navigation and presentation tools

Cost

  • Office 365 subscription or Office licensing

OFFICE – POWER PIVOT
Collect, model, transform, integrate

Notes

  • Integrate on the fly
  • Handle datasets of fairly large size
  • Powerful columnar structure and DAX language
  • Path to enterprise deployment (tabular)

Cost

  • Excel add-in, no additional cost

OFFICE – POWER QUERY
Discover, transform, refresh, analyze

Notes

  • Easily collect web-based datasets
  • Simple and powerful transformations
  • Integrated in PowerBI

Cost

  • Excel add-in, no additional cost

OFFICE – POWER VIEW
Interactive data exploration, visualization, presentation

Notes

  • SSAS tabular and multidimensional data sources
  • Easily to build and share interactive reports
  • Self-service or enterprise standard

Cost

  • Excel add-in, no additional cost
  • Enable options > com add-ins

OFFICE – POWER MAP
Interactive mapping

Notes

  • Easily plot geographic data
  • Play time lapse
  • Share via Excel or video formats

Cost

  • Excel add-in, no additional cost

OFFICE – DATA MINING
Excel-based modeling and predictions

Notes

  • Powerful set of tools
  • Leverage SSAS built models or self-service
  • Simple to get started and use

Cost

  • Excel add-in, no additional cost

OFFICE – ACCESS
Business user database, reporting, querying, writeback

Notes

  • Wizard based
  • Easily connect to ODBC
  • Enable feedback and sharing
  • Excel web apps

Cost

  • Office 365 subscription or Office licensing

SQL SERVER RDBMS
Relational database management system

Notes

  • In-memory OLTP
  • Columnar indexes
  • Always-on availability groups

Cost

  • SQL Server product

SQL SERVER ANALYSIS SERVICES - MULTIDIMENSIONAL
Multidimensional cube engine

Notes

  • Rich set cube of features
  • Data mining

Cost

  • SQL Server product

SQL SERVER ANALYSIS SERVICES - TABULAR
Tabular model engine

Notes

  • Subset of cube features
  • In-memory columnar (VertiPaq)

Cost

  • SQL Server product

SQL SERVER INTEGRATION SERVICES
Generic data integrations tool

Notes

  • Powerful ETL tool
  • Highly customizable
  • C# and Visual Basic language support

Cost

  • SQL Server product

SQL SERVER REPORTING SERVICES
Canned reporting

Notes

  • Enterprise canned reporting
  • Close to pixel perfect
  • Developer and analyst designers

Cost

  • SQL Server product

SQL SERVER MASTER DATA SERVICES
Master data management

Notes

  • Model master data
  • Maintain entities, attributes, members
  • Excel- and browser-based interface
  • SSIS integration

Cost

  • SQL Server product

SQL SERVER DATA QUALITY SERVICES
Manage and enforce data integrity / quality rules

Notes

  • Knowledge-driven data quality product
  • Domain management, matching policies
  • Thick client

Cost

  • SQL Server product

OPEN