Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Microsoft's "Power Stack": Self-Service Busines...

Microsoft's "Power Stack": Self-Service Business Intelligence & Beyond

Making sense of Microsoft's very good, but confusing and complicated business intelligence range of products and cloud services.

I've kept updating this presentation (latest overhaul: March 2019), contact me if you're interested in obtaining it as part of a consultation, as I've put an inordinate amount of research time into it.

Topics include:

- Licensing
- Architecture
- Integration points between Power BI, Excel, SQL Server, Azure
- Development languages and tools

Avatar for Olivier Travers

Olivier Travers

June 30, 2018
Tweet

Other Decks in Business

Transcript

  1. What’s in this presentation: bringing clarity to crowded field of

    products, architectural choices • Microsoft’s BI offering • Historical perspective and dynamics • Strengths & Weaknesses • Shared Assets: Power Query, Power Pivot • Products & Licensing • SKUs & Pricing • Components & Integration Points • Hope you liked Inception • Clarifying seemingly competing options • Client BI tools: Power BI vs. Excel • Cloud or On premises: Power BI Desktop vs. Cloud services vs. Power BI Report Server vs. PBI Gateways • Reporting UIs: Power BI Reports vs. Dashboards • Connection types: Import vs. DirectQuery vs. Live Connection • Data models: Tabular vs. Multidimensional • BI approach: self-service and/or Enterprise IT • Visualization Types • Development Options, Languages, Tools • DAX, M, MDX, R, T-SQL… • Plenty of supporting hyperlinks in slides and footer notes
  2. How We Got There And why should I care? Microsoft

    has been in the BI space for a long time with a “traditional IT” focus • Think SQL, OLAP, multi-dimensional, enterprise IT, data warehouses Circa 2007-14 MSFT seemed to leave the world move to SaaS without putting much of a fight • They let the likes of Salesforce, Google, or Tableau, make them look old and irrelevant And lots of moving parts took time to mature and gel • Power Pivot was first launched in 2009 but was relegated to add-in status until Excel 2016 • Power View/Silverlight dead-end (mobile anyone?) But since 2015 there has been a strong move to inexpensive yet powerful desktop + cloud + mobile services for business users • Think tabular, spreadsheets, SaaS, end-user friendly, within reach of smaller teams without requiring IT As of the end of 2017: still work in progress, but a compelling offer • Strong momentum behind Power BI (PBI) makes it unlikely they will drop the ball, so worth investing into its vision • Good old Excel has become a formidable yet more accessible tool, aka “Modern Excel” “Windows Only”
  3. Exec Summary: Lots to Like, Some Drawbacks Strengths • The

    broadest BI/DB stack of any vendor, with individual components strong in their own right. • Builds up on established leading products (Excel, O365, SQL Server, Azure). • Vibrant product development, iterative and interactive. • Strong user community, support, learning material. • Easy and cheap to get started. Weaknesses • Hard to keep up with all the moving parts and updates! • Still somewhat Microsoft-centric, but much less so than in the past. • Not as good as Tableau for more sophisticated interactive charts. • A lot of stuff still in beta/incomplete. Needs to continue to mature. • Licensing can get complicated, some market segments feel neglected.
  4. Components of the “Power Stack” Self-service BI, Workflows, Apps Software

    Platform What General availability Notes Excel 2016 Windows Spreadsheets Data models Viz September 2015 Until Excel 2013 (included), Power Query and Power Pivot were separate add-ins Power BI Desktop Windows Data models Viz July 2015 Authoring tool formerly known as Power BI Designer Power BI Service Cloud Viz July 2015 SaaS Power BI Mobile iOS, Android, Windows Viz July 2015 Native mobile apps PBI API PBI Embedded Cloud Integration July 2015 January 2017 Embedded merged into Premium in mid- 2017 Flow Cloud, iOS, Android Inter-app workflows & integration October 2016 Think SaaS glued with Zapier, IFTTT, i.e. “Lotus Notes for the cloud.” PowerApps Cloud Windows Visual app designer October 2016 Think Force.com “low code”, i.e. Access/VB for the cloud. PowerApps plans include Flow. OneDrive for Business Cloud Windows Mobile Cloud file sync February 2014 Think Sharepoint SaaS. Allows auto- refresh of desktop Excel files into cloud PBI.
  5. Sub-components of the “Power Stack” Two common core technologies and

    user experiences Subcomponent / Software Function Excel Power BI SQL Server Azure Common Data Service Power Query ETL Y Y SSDT 2017 Yes Power Pivot Data modeling Y Y SSAS Analysis Services Learn once, use in desktop tools, on-premises servers, and cloud services.
  6. Licensing This is Microsoft, ergo cannot be simple Software Platform

    Office 365 Dynamics 365 Separate license Notes Excel 2016 Windows (no PQ/PP in Mac version) • (Limited PQ/PP in Business plans) • ProPlus ($12/u/mo) • Enterprise E3-E5 ($20-$35/u/mo) n/a Office 2016 perpetual license (i.e. not a subscription) • O365 business plans • New ways to get the Excel business analytics features you need Power BI Desktop Windows n/a n/a Free download Power BI Service Cloud Enterprise E5 n/a • Free • Pro ($9.99/u/mo) • Premium P1/P2/P3/P4/P5 ($5K+/mo) • Pricing • Premium cost planner Power BI Report Server Windows Server On premises n/a n/a • Premium P1/P2/P3/P4/P5 ($5K+/mo) • SQL Server Enterprise Edition with Software Assurance Introducing PBI RS Power BI Mobile iOS, Android, Windows n/a n/a Free downloads PBI Embedded Cloud n/a n/a Premium EM1/EM2/EM3 ($625+/mo) Azure A1-A6 ($750+/mo) Embedded pricing Flow Cloud, iOS, Android • Business Essentials/Premium • Education / Education Plus • Enterprise E1/E2, E3/E4, E5, K1 (restricted) • Enterprise: Sales, CS, Ops, FS, PSA, TM, Plan 1/2 • Business: Financials, TM • CRM • Free: 750 runs, 15mn checks • (O365 base plan: 2K runs, 5mn checks) • Plan 1: 4.5K runs, 3mn checks ($5/u/mo) • Plan 2: 15K runs, 1mn checks, admin ($15/u/mo) • All PowerApps plans Pricing PowerApps Cloud, Windows • Free Community plan • (O365 base plan) • Plan 1: premium connectors, use CDS apps ($7/u/mo) • Plan 2: admin + build CDS apps ($40/u/mo) • Pricing • Licensing Overview OneDrive for Business Cloud Windows Mobile • Business / Essentials ($5/u/mo) / Premium: Plan 1 • ProPlus: Plan 1 • Enterprise E1: Plan 1 • Enterprise E3-E5: Plan 2 • Plan 1: 1TB/user ($5/u/mo) • Plan 2: 5+TB/user, data loss prevention ($10/u/mo) Pricing
  7. ETL Power Query Model Power Pivot Data JSON CSV, Excel

    Streaming datasets Sources Databases APIs/SaaS Files View Pivot Tables Pivot Charts View Reports Dashboards Other apps/sites Get, Combine, Transform, Model, Visualize & Share Data with the Power Stack public web
  8. Connection Inception: How Stack Pieces Interact Source(s) Destination What How

    Notes Excel Desktop PBI Service Queries Workbooks Data Catalog Publish to PBI PBI Pro subscription needed. PBI publisher for Excel needed. Excel Desktop PBI Desktop Queries, Data, Data models, worksheets Import Power Query queries, Power Pivot models and Power View worksheets. Excel Desktop PBI Desktop PBI Service Data model Local/LAN via PBI Gateway Need a gateway to refresh datasets that get data from on-premise data sources. PBI Pro subscription needed. Excel Desktop PBI Desktop PBI Service Data model Cloud via OneDrive, Sharepoint Online Any data you’ve loaded into your file’s model is imported into the dataset and any reports you’ve created in the file are loaded into Reports in PBI. Changes to your file on OneDrive will be updated in PBI too, usually within about an hour. PBI Desktop Excel Desktop Data model Analyze in Excel, Use PBI Desktop as a local server Since users will need to refresh the dataset, and refresh for external connections is not supported in Excel Online, it’s recommended that users open the workbook in the desktop version of Excel on their computer. Excel Desktop, PBI Desktop, PBI Service 3rd-party Apps Dataset PBI REST API You can import pbix and xlsx files stored on OneDrive via the API. PBI Service PBI Desktop, Excel Desktop Dataset Connect to datasets (live connection) , Analyze in Excel Reusing datasets PBI Service 3rd-party Apps Viz PBI Embedded Includes embedding in Sharepoint, Teams PBI Service 3rd-party Apps Data alerts Flow Notification triggers. PBI API PBI Service Datasets, reports, dashboards API calls PowerApps PBI Apps PA/PBI interop Embed PowerApps within PBI dashboards + Embed PBI tiles into PowerApps apps Common Data Service PBI, PowerApps, Data model Common Data Excel support via add-in.
  9. Confusion Infusion: MSFT Loves to Move, Rename Features Excel 2010:

    It’s Coming from the SQL Team, So Let’s Hide It • PowerPivot introduced as an Excel add-in, common engine with SSAS Tabular • In-memory engine introduced as Vertipaq then rebranded to xVelocity, because why not? Excel 2013: Moving Sideways • Power Pivot (now with a space, no product name can be left untouched!) remain as an add-in • Introduction of Power View and Power Map based on Silverlight dead end • Introduction of Data Explorer then rebranded as Power Query (notice a trend here?) Excel 2016: Finally Embracing the Power • Power Pivot is its own ribbon tab but also accessible from Data > Manage Data Model • Power Query renamed as Get & Transform • Power View hidden though still available, Power Map renamed to 3D Maps • Old way to get external data continued to coexist with Get & Transform until March 2017 Power BI Desktop: There Can Be Only One Power Something • Power Query found as Home > Get Data (but some documentation does talk about “Power Query”) • Power Pivot found as the Modeling ribbon tab (let’s not name it “Power Pivot” because it might look familiar to Excel users) PBI Service: Office Or Not? • “Power BI for Office 365” was an older version of the service using Excel (with Power View/Map) for authoring and was deprecated at the end of 2015. But powerbi.com user management continues to rely on O365. Azure: What’s Up with That? • Unexpected retirement of DataMarket, Data Services, Reporting Services • Unclear merger of PBI’s data catalog with Azure Data Catalog
  10. Great Momentum, But Uneven Features Between Products • Streaming datasets

    launched Jan. 2017 in PBI Service, not available in PBI Desktop • Can’t print or export to PPT from PBI Desktop • Some mapping features only available in Desktop (ARCGIS) or Service (aerial basemap) • Many online services and CDM as a native Source in PBI but not (yet?) in Excel 2016 • Calculated Tables, Bidirectional cross filtering available in PBI Desktop, not in Excel 2016 • Some UX pieces offer better QoL to author DAX in Excel than Power BI • OneDrive listed under Get Data > Files in PBI Service, but not Desktop • Some objects such as Hierarchies recognized in Excel’s data models but not in PBI Desktop • Excel imports/exports M queries to ODC files but PBI Desktop doesn’t • No Dashboards in PBI Desktop / PBI Report Server • Need to install a separate “PBI Desktop for Report Server” for On Premises work • SSDT in VS2017 was not available at launch Don’t despair: lots of monthly updates, many parts still in beta/preview (e.g. most SaaS connectors)
  11. Even More Caveats: “Pulling a Microsoft” Some questionable choices More

    Open but Still Somewhat MSFT-centric • Authoring limited to Windows and to a smaller extent browser • No Mac desktop • Flow/PowerApps connectors still focused on MSFT world • SQL, Sharepoint, Azure Abrupt & Enterprise-Focused Licensing Changes • Changes to Free/Pro announced out of the blue within less than a month of their enactment • 1-year free Pro trial thrown in to help you stomach it • PBI Premium starts at $5K/mo • Makes sense only from 500+ users • Forced migration from PBI Embedded (Premium Embedded pricing starts at $625/mo) • Why tie PBI Report Server to PBI Premium and SQL Server Enterprise with Software Assurance? • SMEs and ISVs Might Want On-Premises PBI…
  12. Power BI Desktop vs. powerbi.com cloud service Authoring vs. Sharing

    Functionality Desktop Cloud Positioning Authoring tool for analysts Consuming & Sharing tool for end users Data sets Create/Edit Consume Reports Yes ETL (Power Query) Yes No Data modeling (Power Pivot) Yes No Visualization Yes Data access based on row level security No, as a report author you get access to the whole data source Yes Dashboards No Yes Export to PPT No Yes Print No Yes Controlled sharing Not really (you can share the whole .pbix file) Yes
  13. Reports vs. Dashboards Two concepts that look similar but complement

    each other Functionality Reports Dashboards Multiple pages/tabs Yes No Drill down/up, Expand, See Data Yes No Publish to web Yes No Slicers Yes No (unless you pin the whole page from a report) Export to PPT Yes No Print Yes Subscribe Yes Automatic refresh No Yes Q&A No Yes Alerts No Yes Featured No Yes Favorite No Yes Sharing Yes (since January 2018) Yes Web/phone view No Yes Pre-computed/cached No Yes
  14. Sharing Options Depending on desired reach and admin control Sharing

    Method Pros Cons Notes Individual dashboard sharing • Most simple • Fastest to set up • Read only • Have to create dashboards (can’t share a report) • Tedious if you want to share many dashboards • Can send an email invite • Can allow recipients to re-share • Good fit for tests and small scale pilots • Think of this as “email to a colleague” App Workspace • Can host dashboards, reports, datasets • Leverages O365 user/group management • Read-only or read/write access • Underlying use of SharePoint can be confusing at first • Can’t easily move content between workspaces • No staging: changes are published directly to end users • No versioning • Formerly known as group workspaces • You have to use PBI Desktop to publish into an App Workspace • Think of this as shared folders • Recommended: version your pbix source files in OneDrive (or Google Drive) • Product roadmap not fully implemented App • Can bundle dashboards, reports, datasets • Can define the landing dashboard or report • Read-only or read/write access • Can be securely shared with business partners • Staging: changes are not seen by users until an app update is published • Can be pushed to end users • Replaces organizational content packs • Product roadmap not fully implemented Publish to Web Easy to embed Public access Not appropriate for internal/confidential data SharePoint Online page part • Makes PBI content more visible and findable on the intranet • Lets you add notes/comments alongside PBI content PBI Pro license needed Teams • Easy to set up PBI Pro license needed
  15. Data Gateway: 1 Installer, 2 Modes GATEWAY (PERSONAL MODE) Mode

    (Choose on Install) On-premises data gateway Personal installation Cloud services it works with Power BI, PowerApps, Azure Logic Apps, Microsoft Flow Power BI Runs as an app for users who aren't administrators on the computer • Runs as a single user with your credentials • Import data and set up scheduled refresh • • Support for DirectQuery to SQL Server, Oracle, Teradata • Support for a live connection to Analysis Services • Serves multiple users with access control per data source • Monitoring and auditing for gateway and data sources Coming soon
  16. Power BI Report Server Power BI on Premises, with Strings

    Attached Functionality Powerbi.com Power BI Report Server Licensing Flexible and starts cheap Inflexible and expensive (enterprise only) Updates Monthly 3x a year (server and authoring client) Authoring client Power BI Desktop Separate PBI Desktop version Variety of data sources Yes More limited, but growing (initially was only SSAS) Dashboards Yes No: only reports R Visuals Yes No On premises No Yes (well, it’s got that going for it) The key word is Report, PBI RS is not meant to emulate all the features available in the cloud service.
  17. Enterprise Offering Continues to Evolve in Parallel Increasingly Integrates, Blends

    with Self-Service • SQL Server Integration Services (SSIS) • Enterprise ETL • SQL Server Analysis Services (SSAS) • Think of SSAS Tabular as “PowerPivot Enterprise Server”, i.e. modeling tool • “Live Connection” option in PBI: no need to import data but faster than Direct Query • PBI can query SSAS Tabular and SSAS MD • Models can be imported from PBI Desktop • SQL Server Data Tools (SSDT) 2017 • Support for DAX queries against SSAS • Intellisense for M • SQL Server Reporting Services (SSRS) & PBI Report Server • Focus on On-Premises, pixel-perfect/print static reports • Paginated reports and Mobile reports can be pinned in PBI reports • PBI reports can be put in SSRS dashboards • Support for DAX queries against SSAS in Report Builder • PBI RS is basically a superset of SSRS • SQL Server Management Studio (SSMS) • Now has a DAX query editor with Intellisense
  18. PBI-SQL Connection Types Choose where storage and modeling happen Connection

    type Data storage Data modeling Refreshing Notes Import Data loaded in PBI’s in- memory engine PBI • Scheduled refreshes (every 15 minutes or more) • Limited to 1GB dataset and 10GB of data in cloud service • Can’t change connection type Direct Query Data remains in the source SQL database • Data is pushed via the Enterprise Gateway • You can schedule cache refreshes • Pinned tiles are automatically refreshed every 10 minutes • Can change connection type • No Quick Measures • Some unsupported DAX (eg. time intel) • Can’t create hierarchies • Can’t connect to several data sources • Can’t create relationships between tables • Can’t convert column types • Can’t change connection type • No calculated columns (June 2017) • Most scalable solution Live Connection SSAS
  19. Tabular or Multidimensional: PBI + SSAS Tabular Multidimensional Available since

    SQL Server 2012 SQL Server 2000 Licensing SQL Express and above, PBI, Excel SQL Server Enterprise Modeling constructs Relational: model, tables, columns OLAP: cubes, dimensions, measures Languages DAX calculations & queries, MDX queries MDX calculations & queries, , DAX queries M Expressions Yes No Calculated measures, columns, tables Yes Calculated members Actions No Yes Aggregations No Yes Custom Assemblies No Yes Custom Rollups No Yes Default Member No Yes Display folders No Yes Hierarchies Yes KPIs Yes Many-to-many relationships No Yes Writeback No Yes Compression vs. original data size Can go down to 10% About 30% Sources Largest variety Only relational databases Database size MD used to be needed for the largest projects but Tabular is catching up
  20. Mix & Match On Premises & Cloud Services galore: Infrastructure

    & Platform as a Service Service On premises Cloud Database (OLTP), Data Sync SQL Server IaaS: SQL in Azure VM PaaS: Azure SQL, Azure Data Sync Data warehouse SQL Server IaaS: SQL in Azure VM PaaS: Azure SQL Data Warehouse Data Modeling PBI RS, SSAS PaaS: Azure Analysis Services, PBI ETL PQ, SSIS PaaS: Azure Data Factory + Azure SSIS, PBI, Future Power Query SaaS stand-alone Reporting PBI RS, SSRS IaaS: SSRS in Azure VM PaaS: PBI (there’s no SSRS PaaS) And more… PaaS: Azure Storage, HDInsight, Data Lake, Event Hubs, Stream Analytics
  21. Chart types Good basics complemented by open source extensibility (HTML

    / CSS / SVG / JS / D3) Chart/viz type Source Comments Default types Power BI About 25 core viz types. Customization options getting better with monthly updates. Maximum of 3,500 displayed data points, 60 series. Custom charts NodeJS/TypeScript development Knock yourself out. Build it from scratch or leverage D3 and its offshoots like dimple. Limited to 30K rows. R charts R sideload • Powerful way to extend PBI. Limited to 150K rows. Can be the target but cannot be the source of cross- filtering/highlighting. • PBI Pro license needed for use in cloud service. Not available in PBI RS or via Publish to Web. • Only some R packages are supported in cloud service, and those that make web queries are blocked (searchable list with descriptions). R-JS hybrid charts Htmlwidgets, Plotly You can use R and JS libraries together. This gives you relatively easy access to well-known libraries such as DataTables and Highcharts. This works in Custom R charts only (i.e. not “R charts” from the row above). Certified custom charts Microsoft curated store 130+ additional types, from mediocre to great. Some chart makers: MAQ Software, OKViz, Zebra BI, ZoomCharts, dataviz.boutique. Maps Power BI / ESRI / R / HS See next slide Pivot tables Excel Power BI has a matrix but it’s nowhere near as powerful. Want pivots? Analyze in Excel from your PBI solution. Some prominent types missing Not in Power BI but seen elsewhere (Tableau et. al.) Clustered Stacked Columns/Bars, Trellis (stopgap: Infographic Designer) PowerApps Custom visual With PowerApps and Flow, you can turn your PBI report into an application with writeback, workflows.
  22. Mapping Many options from basic to sophisticated Map type Source

    Max data points & other limitations Comments Map Native PBI 3.5K data points Filled Map Native PBI 3.5K data points Shape Map Native PBI 3.5K data points ARCGIS (Free) ESRI 1.5K geocodes per chart and 100K per month 10K latitude/longitude pairs limit • Not available in Publish to Web, Embedded, or PBI RS • Doesn’t work offline • 4 basemaps • US-only demographics layers and info cards ARCGIS (Plus) ESRI 5K geocodes per chart and 1M per month • 12 basemaps • Worldwide Living Atlas maps and layers, international demographics • Not yet integrated with ArcGIS enterprise sign-in (i.e. no private ArcGIS data) Custom visuals Microsoft, 3-rd parties, you? 30K data points R visuals 3-rd parties, you? 150K data points R custom visuals 3-rd parties, you? 150K data points Mapbox (Free) Mapbox 30K data points Best control over basemap design via Mapbox Studio Mapbox Mapbox 30K data points
  23. BI & DB Languages Sorting out the alphabet soup Language

    Since About MSFT Support Strengths / purpose T-SQL (Transact Structured Query Language) 1974 (SQL) Query language for relational databases SQL Server Work with individual rows and row sets. MDX (Multidimensional eXpressions) 1997 Query language for OLAP databases SQL Server, PBI Desktop Query data tubes. Powerful. DMX (Data Mining Extensions) 1999 Query language for SSAS data mining models SQL Server, Excel Syntax looks like SQL. DAX (Data Analysis eXpressions) 2009 Data modeling language introduced with PowerPivot SSAS, Excel, PBI, DAX Studio, DAX Editor, AAS Calculate dynamic aggregates with Excel-like function syntax. Flexible and versatile. M 2010 Functional language introduced with Power Query Excel, PBI ETL LINQ (Language Integrated Query) 2007 Query language for .NET languages Visual Studio: C#, F#, VB.NET There’s a LINQ to DAX tool R 1993 Open source statistical and graphical language Excel, PBI, SQL Server, Visual Studio, R Server & Client Stats (e.g. regression analysis), LOTS of extensions, good charting. Microsoft heavily invested into it. Python 1991 Programming language popular with data scientists SQL Server 2017 The most general purpose language in this table. Good to handle text. BIML (Business Intelligence Markup Language) 2008 XML dialect to specify BI solutions (relational models, data transformation…) SSIS, SSAS, Visual Studio BIDS Helper Automate ETL creation
  24. Power BI Development Options • Power BI REST API 1.

    Push datasets/tables/rows into PBI model in language of your choice 2. Trigger data refreshes 3. Embed dashboards in your app (now using the same API) • Custom visuals • TypeScript / CSS / D3 • Data Connector SDK / Cloud content packs • PowerApps + Flow / Logic Apps • Form-based “no code” apps embedded in Power BI dashboards • Visual workflows using PBI alerts as triggers • Pushing data to PBI datasets • R: ETL and Charting 1. Load an R script as a data source 2. Run an R script as a Power Query step 3. Plot an R visual (including using libraries such as ggplot2) 4. Create an R-based custom visual (including using Plotly) • VBA • Publish Excel workbook to PBI
  25. Power BI Embedded & Premium Azure A SKUs Embedding EM

    SKUs Premium P SKU Target market ISVs & Enterprise ISVs/developers Enterprise Embed in your own app Yes Share content with PBI free users outside powerbi.com and embed in other SaaS applications (SharePoint, Teams) No Yes Share content with PBI free users through powerbi.com No No Yes Power BI Report Server No No Yes Purchase portal Azure Office (EM3)/EA Office (P1)/EA Billing Hourly Monthly Commitment None Monthly/Yearly Pricing Between $1/h (A1, $750/mo) and $32/h (A6, $24K/mo) EM1: $625/mo EM2: $1,245/mo EM3: $2,495/mo P1 node: $4.995/mo P2 node: $9.995/mo P3 node: $19.995/mo PBI Pro licenses Needed for content publishers, not viewers
  26. Power BI Embedded vs. Service powerbi.com service Embedded Authentication powerbi.com

    Your own app APIs Power BI REST API, JavaScript API PBIX upload To powerbi.com Personal “My Workspace” Yes No App workspace Yes Need to register AAD Application Yes App Type Any app Type Native App (even for a web app) Need to grant permissions on Azure portal End user will grant permissions Yes Authenticate with AAD to get auth token With user account With master account Embedding Use embed URL Call GenerateToken to create EmbedToken Capacity Not required (but then need Pro licenses for users) Required when moving to production SKUs EM or P A, EM, or P
  27. The Complete Toolset for the MSFT BI Analyst A lot

    of that stuff is free! “Self Service” / pilot “Enterprise” / production Authoring • PBI Desktop auto-updated from Microsoft Store • PBI Desktop updated manually to control deployed version • PBI Desktop for Report Server Accessing • Gateway (Personal) • Power Update (free) • OneDrive for Business • Gateway (Enterprise) • Power Update (full) Excel • Excel 2016 via O365 ProPlus (probably monthly channel, possibly Office Insider) • PBI Publisher for Excel • Excel 2016 via O365 E3-E5 (probably semi-annual channel) • OLAP PivotTable Extensions • Inquire add-in SQL Server • SQL Server Express • SQL Server Developer Edition • SQL Server Standard / Enterprise • SSAS • SSIS • SSRS • MDS, DQS, MLS… Development • Notepad++ • Visual Studio Dev Essentials • VS Code with M Query Extension • VS Community with Power Query SDK • R Studio • Visual Studio • SSDT BI: DAX & M • SSMS: DAX • R Tools • Azure Machine Learning Studio Modeling • DAX Studio • Power BI Helper; Power BI Documenter • PowerPivot Utilities • DAX Editor