sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009
Today marks the first anniversary of Power BI Desktop and the Power BI Service. The effort to achieve this milestone and the on-going commitment of the teams responsible for this product is nothing short of phenomenal. Earlier this week, I reached out to a few friends to join me in sharing this Happy Birthday wish and thanks to the people behind this amazing, and ever-improving product. Adam Saxton assembled this video montage and is credited for the impressive production work.
The product released last year on this date was a major overhaul of the earlier tools, with some features based on mature technologies that have been improving for several years. Since then, the pace of development, new capabilities and feature enhancements has been mind-blowing. The Power BI service is enhanced weekly and the desktop tool is updated every month, while the platform remains stable and reliable.
To the Microsoft Power BI product team:
From those of us in the community – partners, trainers, consultants, MVP Program members, user group leaders, developers, integrators and service providers – thank and congratulate you.
Here are some out takes and clips I received after Adam finished the production:
Bring your laptop and let’s build a real business intelligence (BI) solution using Power BI! In this deep-dive session, we build a complete solution. You will need the Power BI Designer (available for free at powerbi.com). You will learn how to cleanse and transform data from multiple sources with Power Query, model data for reporting with Power Pivot, and explore the model and build interactive dashboards with Power View. You’ll leave with a working BI solution.
SQL Server Upgrade Preconference Session: October 26
I will also be co-presenting a full-day preconference session on upgrading SQL Server, along with Tim Chapman, Jim Miller, Richard Waymire & Ron Talmage from SolidQ. We will cover upgrading the entire SQL Server feature set from every version since 2005 to newer versions (2012, 2014 & 2016). I be talking about upgrading SSRS, BI design tool and implementing SSAS Tabular.
I’ve created a series of five tutorial videos for a set of workshops I’ll be doing at several events this year. The workshop is available from an hour-long quick demo to a full-day, hands-on workshop with a hosted lab environment in the cloud. The series teaches how to create a complete business intelligence solution using the components of Power BI in Excel 2013. These video tutorials, although complete, are a subset of the entire workshop. Each tutorial is about 10 minutes long.
This week Microsoft announced the availability of Power BI Dashboards and the browser-based dashboard designer. What is it and why is it important? The most significant thing about it is that report and dashboard users do not need to have Excel 2013 ProPlus edition or an Office 365 subscription to use Power BI. This is very good news as it opens these amazing capabilities up to a much wider audience; those who work for companies that don’t have Office 2013 ProPlus or who are not using Office 365 ProPlus. Power BI updates and new features are being released at a very fast pace and there is much to consider. The definition of “Power BI” and Microsoft’s larger Business Intelligence and data analytics offering continues to evolve.
First, exactly what’s new and recently available?
Web-based Power BI dashboard designer
iOS Power BI app
New REST APIs
In August, I posted this about what Power BI was and how to start using it. At that time and up until this week, Power BI required an Office 365 Enterprise (E3) account with an additional subscription option. The new dashboard designer preview is currently free and doesn’t require an Office 365 subscription. It’s essentially Power View, Q&A, a new tiles interface with mini Power View visuals and connectivity management options. Data sources can include a variety of online sources in addition to your own data in a Power Pivot model you author using Excel and then upload to a OneDrive folder or an Office 365 tenant. You can also connect Power View reports and dashboards to an on-premises SSAS tabular model. You heard me… your data doesn’t have to be hosted in the cloud. The dashboards and reports are still published to the cloud but your data can stay on-prem. This is a very significant move toward Power BI becoming an enterprise-ready technology.
The iOS app for iPad is now in the Apple Store and is also currently free. It allows users to view and interact with published dashboards in a native app on their iPad. An iPhone version of the app will soon be available. This app is similar to, but not quite the same thing as, Power View; it’s more of a dashboard view-only app with touch-friendly interactions. For Power View users, it will be familiar and it’s very intuitive and easy to use. This is another big step forward for Microsoft BI. These are some camera shots on my iPad. Even if you don’t have your own data and models to browse, the included samples are great. Check-out the details here and then go to the iTunes or App Store and search for Power BI to install it yourself.
The new REST APIS for Power BI are still very early and in preview but they show that we will be able to integrate Power BI capabilities into custom business solutions. This is a long-awaited capability that holds a lot of promise for serious developers and system integrators. Get the preview here.
Power BI – and everything related to it – is Microsoft’s big-bet for BI and this is where their resources and innovations are being invested. Given the dependence on the cloud, I don’t see all of our enterprise consulting clients jumping on the bandwagon just yet but I do see many more businesses beginning to take this option more seriously, especially with the promise of integration APIs and on-premises data support.
Here is a collection of recent blog posts and announcements:
The other day a secondary family member said “we’re still on Microsoft 2010”. Another said “My friend is using Windows 2003 and can’t open a document I sent”. They were apparently talking about versions of Office products.
I get so deeply engrained in the Microsoft Business Intelligence community culture that I have to remind myself that most people don’t spend all their time keeping up with these products that are constantly in motion. And these are just Office users! When I work with BI consulting clients and business folks in the industry, it’s apparent that most people really don’t understand where these products and services begin and end.
Mark Tabladillo has done the world a service by laying out exactly what Power BI is, the products that it includes, and how to license it and get started. This is a great resource on the Microsoft MVP Program Blog that breaks it all down and helps it all make sense. Thank you, Mark!
Power BI is new and emerging self-service business intelligence and business analytics framework brings together and enhances key Microsoft technologies:
Office
SQL Server
Azure
SharePoint
Fundamentally, Power BI is considered a premium Office option, because Microsoft licenses it that way. Yet, the technology details also comprise new collaboration technologies for SQL Server, Azure and SharePoint. A successful technology collaboration will have boundaries which could arguably belong to one or more of the contributing technology groups.
This document provides links and introductory information to Power BI. My analysis is more useful for the enterprise planner (CIO, CTO, Information Technology Architect), but also is useful for individual consumers. Power BI is a technology which extends from individual use on any device (laptop, tablet or smartphone) and all the way up to high-scale cloud or hybrid (cloud plus on premise) production architecture…
Watch below for live updates during the conference
Stay tuned and you’ll find out what’s going on during this week in San Jose. I’ll be blogging from the conference as announcements are made and as the rest of the community buzzes about new developments in the community and here at the BAC in San Jose, California.
If you haven’t downloaded the pocket Guidebook, do it now. Just go to http://guidebook.com/g/passbac/, install and then search for PASS Business Analytics.
Tuesday, May 6
I arrived this evening, flying in from Portland. I met up with my long-time friend and co-author, Grant Paisley from Sydney, Australia. It all starts to happen tomorrow with the all-day pre-conference sessions. This is where the deep learning takes place. Every year, people who have serious interest in learning to use a technology or business tool spend a full day with some of the most respected industry professionals. They go deep and thorough, usually showing how to apply bet-practice skills to build a complete solution or tackle some serious issues. Registration for the preconference sessions will still be open on Wednesday morning until the rooms are at capacity.
The Power BI iOS app will be available this summer Reporting Services will run in Power BI – by the end of summer Power BI will work with on-prem data sources without using scheduled data refresh
Forecasting & Time series analysis – line chart provides time-series prediction with error ranges & outlier correction adjustments – WOW!
Demo: Hawaii tourism survey:
Interesting insights about the spending per island and where visitors come from
Question: “why do Japanese tourists show more affection than other cultures?” – answer: More Japanese honeymoon in Hawaii
Julie, a volunteer from the audience, is doing the next unrehearsed demo…
WE HAVE HEAT MAPS in Power VIEW!!!! — Jun Underwood is hyperventilating
Drag a chart data point out to create another visual and drill-down – very cool!
Power View snaps and resizes
Bump and Shake gestures added – automatically separates a chart into multiples or combines two charts into one visual by dragging them together with two fingers on a touch screen.
In conclusion, the features shown today will be introduced in the next couple of months through Power BI in Office 365.
———————————————————————————————————
Attended a great session presented by Devin Knight for beginners about building an end-to-end BI solution using Power View
Devin is a very good presenter and was able to very clearly articulate the value of the Power Pivot and Power View toolset through live demonstrations.
———————————————————————————————————
Great quote from Gartner in Michael Trjedor’s session on Microsoft Analytics & Visualization: "By 2015, organizations that build a modern information management system will outperform their peer financially by 20 percent." – Gartner, Mark Beyer, "Information Management in the 21st Century"
———————————————————————————————————
Rob Collie: Your own Data Revolution
“It doesn’t have to be industrial scale to be industrial strength”
“The human prison”: The human brain is naturally trained to work within the boundaries established from past experience. If only certain questions could be answered by a system/report/spreadsheet/database/model/whatever, we’re not likely to ask other questions.
“apples and oranges”: Low hanging fruit…We often make unfair comparisons and establish benchmarks (e.g. KPI targets). There are often/usually more variables to consider in a comparison.
These tools & techniques allow our brains to thin the way they want to rather than the way they’ve been trained to think.
“The Data Gene”: 1/16 people have the data gene – in all demographics & at all levels of the organization.
You’re a data/business analytics conference… That’s not normal.
The people who mock those who built the pyramids are “normal”.
Data gene is an interest not a level of intelligence, no PhD necessary
There is no cure
———————————————————————————————————
Alberto Ferrari: DAX Solutions from the Field
“I’ve stopped believing in Santa Clause, the Tooth Fairy and Microsoft Marketing”
“The primary key in the fact table is your worst enemy”
The first step to optimizing performance should always be to reduce unnecessary distinct column values
Use Kasper’s memory size Power Pivot model to analyze the memory footprint of tables & columns
Use SQL Server Profiler, clear cache
Build the model with necessary columns
Find columns using the most memory
———————————————————————————————————
Dejan Sarka: Advanced Analytics in Excel 2013
if you would like to get a complete picture of the end-to-end data analytics capabilities in Excel, watch the recording of this session. Dejan covered all the essentials at an advanced level. This excellent presentation moved very quickly. Emphasis on data mining/predictive analytics.
FridayKeynote
It’s 8:05 AM Pacific Time on the 9th of May, 2014 and the keynote session is beginning…
This session is highly visual and to get the message, I will update this post with pictures that become available after the conference.
David McCandless, keynote speaker, is visualizing a billion dollars using a tree map/heat map
“Debtres” … animated tetris visual that assembles a heat map
Trendline example: what trends up in the Spring near Easter and Christmas and then a little each holiday and weekend?
…relationship breakups via FaceBook
Data must be visualize in context (e.g. pure sum vs ratio… )
Strip out the labels and leave the colors and shapes to set the visual context
This series of video sessions will demonstrate how to create a business intelligence solution with sets of business data from multiple sources. In the first session, I import business records from 5 different D&B data feeds using the Windows Azure Marketplace with desktop BI tools in Excel 2013 ProPlus. In future sessions, I will combine public data sets, use features of Power BI and migrate to an enterprise scale BI solution with SQL Server, SSAS tabular and SharePoint.
D&B data fees in the Windows Azure Marketplace can be used to get detailed, current demographic and financial information about specific businesses or companies in different regions and categories. These business records can be used for things like market analysis, sales, verification and research.
Session 1 – Getting started (48:00) Analyzing Dunn & Bradstreet business records data from the Windows Azure Marketplace using Excel 2013 Power Query, Power Pivot and Power View to create a basic dashboard
To watch this video with more playback controls, you can also view it on ScreenCast here.
Session 2 – Enhancing the Solution (32:00) Combining Dunn & Bradstreet Customer Records with Public & Enterprise Data Geographic visualization with Power Map, publishing to Power BI and using Q&A to search a model with natural language
Good technology books usually come in one of two forms. Some of the books on my shelves go deep technically, contain useful tidbits of code but read like the phonebook. Just a few are interesting and insightful. This book is a rare gem that does both. Paul te Braak is well-known in the Business Intelligence community for his expertise and contributions and he delivers a unique guide that starts with the basics and proceeds to cover all of the essentials with depth and practical examples to solve some challenging business problems.
You might expect a book that introduces fundamental concepts to gloss-over advanced topics and avoid complex applications. This book covers the essentials of data modeling and analysis with Excel and Power Pivot in language that is plain and easy to understand but it doesn’t stop with the basics. It covers practical and useful applications without insulting the reader’s intelligence. As an experienced data modeler, I found several useful techniques and new methods to use the tools and language. Paul’s coverage of practical techniques spans the spectrum of business applications and product features. This is a rare book that is not only a good tutorial with many hands-on examples that can be repeated by the reader but it’s also a great reference of useful techniques and code samples.
Highlights include:
The integration of Excel features such as ranges, tables, pivot tables and pivot charts with the additional features of Power Pivot and Power View that extend and enhance these capabilities
Examples and instructions are directed at Excel 2010 users and the author compares some the different features in Excel 2010 and Excel 2013.
Fundamentals of the DAX calculation language
Importing data as text, different date formats and implied data type columns
Beyond the basics, a schema.ini file is used to define column data types
Importing data from a database, working with database tables, views and queries, managing connections and challenges encountered running the table import wizard multiple times
Data feeds using OData and using a Reporting Services report to provide a data feed
Decisions a designer makes to enable the user’s experience when browsing a model. This includes sorting values, navigating hierarchies that enable drill-down interaction.
DAX “X” functions (SUMX, MINX, etc.) to perform row-level aggregation
Working with parent-child hierarchies using specialized DAX path functions.
Advanced browsing features, adjusting pivot table options to optimize the user experience
Building and using KPIs and using alternate table relationships
Time calculations and date functions. This chapter covers running totals and totals to date
Date part aggregate functions (MTD, YTD, etc.),
Essential data math and comparisons
LastYear and PriorPeriod functions, TotalYTD
Manufacturing calendar, working with “445” dates
Creating a dynamic relative time measure, using a shell dimension table
Using DatesBetween to show the average value for the past 10 days
Apply advanced modeling technique to bin, sort and rank values for reporting
Expand concepts introduced in chapter 3, using the DAX “X” functions to perform row iteration in advanced financial applications
Defining and working with many-to-many relationships. This is often no trivial task to completely understand many-to-many relationship requirements and to apply a working solution that provides the intended results
Addressing inventory and stock-keeping challenges
Conditional aggregation at different levels
Budgeting and forecasting vs actuals
Programming Excel to enhance the users experience
Excel VBA event programming to respond to slicers
Using cube functions
Interacting with charts and slicers
Building solutions for the enterprise
Using the SSDS Tabular designer
Migrating Power Pivot models to Tabular server solutions
What I’m going to show you is not rocket science. This one gets filed under the category of “well, duh. That was easy …now that I see how to do it.”
Power View sets the maximum value for a chart series based on the maximum unfiltered axis value; pretty simple but not too flexible. Let’s say that I want to show the progress of project completion. If I add a percentage type measure to the Value of a column chart with no fields on the Axis, it will plot a single column “gauge” chart with the maximum value (in this case 65%) somewhere near the top of the chart. I suppose that’s what we asked it to do but there is no way to manually set the maximum range of the axis scale. Oh, what to do?
Like most beyond-the-basics capabilities in Power View, the answer is to enhance the semantic model – and this one’s easy. In the PowerPivot or SSAS model, create another measure. I’ll do it right next to the existing Percentage calculation named “Project Pct Complete”. The objective is to get the gauge visual (our no axis column chart) scale to add up to 100% so what we need is another measure that when added to the first one, adds up to 100%. I’ll call that calculation “Remainder Pct Complete” using the DAX expression:
By subtracting the % Complete from the number one, the result will always be the remainder of 100%. I’ll format the new measure as a percentage.
Now I add both measures to the chart/gauge like so:
I haven’t quite got the legend to work the way I want it to. In this case, I’d probably just hide and and maybe show data labels for the two series values. That’s all up to you, of course. For extra credit, let’s add another progress gauge that shows the overall progress even when a slicer is used to filter the data. First of all, test this. I’ve added a Priority slicer and selecting one or a combination of items slices the project rows and changes the progress gauge value.
Back in the model, I add two more measures starting with the existing expressions. You can just copy one of the measure cells and paste it into an empty cell. This must be done on-at-a-time. The expression is enhanced using the CALCULATE and ALL functions, like this:
This says “Apply the Project Pct Complete measure calculation to whatever filter context is provided in the CALCULATE function. In this case, we are says “ignore the natural row or filtering context of the query and always apply the calculation to all rows in the Projects table.”
Now, when a slicer or filter is used on the report, the Overall gauge continues to show the same percentage while the rest of the report, including the Progress gauge shows only filtered values.
Power BI: a new suite of Business Intelligence tools
Over the past few months, teams at Microsoft have made several new Business Intelligence tools available for preview; some only privately and some to the public. The entire suite will soon be available for either public preview or release under the new name: “Power BI”. All of the components of Power BI are listed below but the big news is a new hosted offering called “Power BI for Office 365” and “Power BI Sites”. The announcement was made at the Worldwide Partner Conference this week. Users can sign-up to be notified when the new offerings are available for general availability, apparently in the very near future. I’ve had an opportunity to work with early, pre-released versions and it has been interesting to see the gaps being filled a little at a time. On the heals of the new suite, some of the names of existing products are also being changed. It’s hard to have a conversation about the collection of Microsoft’s “Power”/”Pivot”/”Point”…named tools and not get tongue twisted but these changes bring more consistency.
Bottom line: this is good news and a promising step forward – especially for smaller businesses. Larger, enterprise customers should know that this move is consistent with Microsoft’s “cloud first” philosophy and these capabilities are being introduced through Office365/Azure platform with required connectivity. Read the commentary on community leaders’ sites below. I have no doubt that there will be a lot of discussion on this in the weeks to come with more announcements from Microsoft in the near future.
When Power View was released with SQL Server 2012 Enterprise and Business Intelligence Editions, it was available only when integrated with SharePoint 2010 Enterprise Edition. This is a good solution for enterprise customers but it was complex and expensive for some to get started. Power View was also offered only as a Silverlight application that wouldn’t work on many mobile devices and web browsers. For this reason, Power View has really been viewed as a “Microsoft only” tool and only for big companies with deep pockets and very capable IT support groups. Even the new Power View add-in for Excel 2013 ProPlus Edition requires Silverlight which is not a show-stopper for most folks but a hindrance for multi-platform and tablet users. This all changes with this new offering as the Power View visualization tool in the hosted product come in 3 new flavors: native Windows 8 app (runs on desktop, Surface RT & Pro), native iOS (targeting the iPad) and HTML5 (works on practically any newer device). This means that when you open a Power View report on your Surface or iPad, it can run as an installed app with all the cool pinch-zoom and gestures you’ve come to expect on a tablet device. For now, this is good news for the cloud user as no on-premises option is currently available. An interesting new edition will be the introduction of a semantic translation engine for natural language queries, initially for English.
Formerly known as “Data Explorer”, this add-in for Excel 2013 allows you to discover and integrate data into Excel. Think of it as intelligent, personal ETL with specialized tools to pivot, transform and cleanse data obtained from web-based HTML tables and data feeds.
This Excel 2013 ProPlus add-in, which was previously known as “GeoFlow”, uses advanced 3-D imaging to plot data points on a global rendering of Bing Maps. Each data point can be visualized as a column, stacked column or heat map point positioned using latitude & longitude, named map location or address just like you would in a Bing Maps search. You can plot literally thousands of points and then tour the map with the keyboard, mouse or touch gestures to zoom and navigate the globe. A tour can be created, recorded and then played back. Aside from the immediate cool factor of this imagery, this tool has many practical applications.
Power Pivot
The be reveal is that “PowerPivot” shall now be known as “Power Pivot”. Note, the space added so that the name is consistent with the other applications. We all know and love this tool, an add-in for Excel 2010 and Excel 2013 ProPlus (two different versions with some different features) that allow large volumes of related, multi-table data sources to be imported into an in-memory semantic model with sophisticated calculations. On a well-equipped computer, this means that a model could contain tens of millions of rows that get neatly compressed into memory and can be scanned, queried and aggregated very quickly. Power Pivot models (stored as an Excel .xlsx file) can be uploaded to a SharePoint where they become a server-managed resource. A Power Pivot model can also be promoted to a server-hosted SSAS Tabular model where data is not only managed and queried on an enterprise server but also takes on many of the features and capabilities of classic SSAS multidimensional database. Whether a Power Pivot model is published to a SharePoint library or promoted to a full-fledged SSAS Tabular model, the data can be queried by any client tool as if it were an Analysis Services cube.
Power View
For now, Power View in Excel 2013 ProPlus and Power View in SharePoint 2010 Enterprise and SharePoint 2013 Enterprise remain the same – the Silverlight-based drag-and-drop visual analytic tool. With the addition of SQL Server 2012 CU4, Power View in SharePoint can be used with SharePoint published Power Pivot models, SSAS Tabular models and SSAS Multidimensional “cube” models. There has been no news yet about a non-Silverlight replacement for the on-premise version of Power View. The Microsoft teams and leadership have heard the requests and feedback, loud-and-clear, from the community and we can only guess that there is more is in-the-works but I make no forecast or assumptions about the eventual availability of an on-premise offering similar to Power BI for Office 365.
In March, I posted about the public preview of the “Microsoft SQL Server 2012 With Power View For Multidimensional Models”. Well, the official release is now available for those currently using SQL Server 2012. When the preview became available a couple of months ago, I was very excited to test it out so I downloaded it, quickly scanned the release notes and then proceeded to upgrade an existing SQL Server 2012 SP1 development server. What I missed in the release notes was the requirement to uninstall several existing components and then to install them from scratch. That wasn’t as easy as I had hoped but it’s pretty typical for prereleased software to not include upgrade support. After all, the product teams are focused on finishing features and debugging and not getting all the upgrades and installation details sorted out. Those steps usually happen last in the release cycle.
Not to worry, this new capability is now part of the Cumulative Update 4 for SQL Server 2012. This means that it will be fully-supported as an upgrade to an existing SQL Server 2012 installation. This is very exciting news. If you have seen Power View demonstrated with new SSAS tabular models and PowerPivot models in Excel and SharePoint, you know what a simple and powerful data browsing and visualization tool it is. Some people have been a little disappointed that Power View initially only worked with new xVelocity-based tabular models and not the multidimensional cubes built with SQL Server Analysis Services, that have become common in many Microsoft centered IT shops throughout the industry.
The Microsoft product teams have shared a lot of good news, like this, recently about BI innovations – with Power View in Excel 2013 and GeoFlow recently released. They are likely to share even more good news in the weeks and months ahead. It’s an exciting time to see some very impressive, powerful, fun to develop and fun to use BI business and IT tools all coming together to meet very real business problems.
I don’t know about you but I’m going to get this baby installed and working right away. I have clients who have been waiting patiently (and some not so patiently) to use Power View with their existing cubes. I love to be the bearer of good news.
As is usual when something noteworthy happens on the Microsoft BI community, Chris Webb has blog eloquently on the topic and with significant detail. Read today’s post on his blog here.
Today in Vancouver, British Columbia, at the SQL Saturday #198; I presented a session titled “Data Visualization Choices”. As promised, my slide deck is available for download here.
This is the first draft of the session I’m preparing for the PASS Business Analytics Conference coming up on April 11-12 in Chicago. I’ll have another update for that conference.
There has been an active discussion thread on Chris Webb’s blog about the similarities & differences between various Microsoft BI & reporting tools. This is becoming a very popular topic and I thought I’d post my reply to that thread here. I encourage you to read the entire post on Chris’s blog here: Some thoughts on what Office 2013 means for Microsoft BI
…These are some very interesting thoughts about the differences in capabilities between SSRS and Power View. Continue reading →
This is a very significant day in the progression of the Microsoft Business Intelligence platform. Earlier this year, Power View was released with SQL Server 2012; a remarkable step forward in data visualization. It wasn’t all bad news that Power View only worked with the new tabular semantic models and PowerPivot worksheets published in SharePoint but it did limit our options. We’ve quietly lived with the fact that this great new reporting tool couldn’t be used with Microsoft’s flagship analytical data (OLAP) engine without building new semantic models. Well, now it does, and that’s very good news.
One of the characteristics of a really good, classic movie is that it has a lot of memorable dialog. I could go on for hours quoting one-liners from The Blues Brothers or Princess Bride. Likewise, I think a good book leaves the reader with gems to ponder and to stimulate ideas. Such has been my recent experience reading Rob Collie’s “DAX Formulas for PowerPivot, The Excel Pro’s Guide to Mastering DAX”. Continue reading →
