This post is really a collection of field notes and some lessons learned from recent project experience. I’ve done plenty of SSAS Tabular projects over the past few years – usually starting with a Visual Studio project rather than Power Pivot. I’ve also done a bit of Power Pivot work for clients. These projects were either delivered to an analyst using Excel on their desktop or some business users through SharePoint. But, authoring in Excel Power Pivot and deploying to a server-hosted Tabular model has been mainly theoretical up to this point so I thought I’d share my experience. Read the rest of this entry »
I’ve recently seen a wave of questions from clients and peers about difficulties exporting reports to Excel lately. Every few weeks I get a call or question about this. This topic has been a recurring theme for a very long time and one that I have encountered many times over the past – oh, eleven years or more – using SSRS. Business users like Excel because it’s what they know and they can reformat and manipulate data in a workbook. People like Reporting Services because all the hard work of connecting to data sources, writing queries, totaling, grouping and formatting the results gets done once and then all they need to do is run the report. Users want the best of both worlds and they expect that when they export a report to Excel that they should have their cake and eat it. In other words; they should be able to get a report, with all the goodness of headers, scrolling regions, pagination, interactive sorting – you name it – to work exactly the same way in Excel. Many improvements have been made to all of the report rendering extensions over the years and the Excel export story is better than ever but here’s the deal – bottom line – the more rich formatting and advanced functionality that gets designed into an SSRS report, the less likely it is to export all that functionality to Excel or any other rendering format. Read the rest of this entry »
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.
Power BI Complete Demonstration
Part 1 – Power Query
Part 2 – Power Pivot
Part 3 – Power View
Part 4 – Data Correlation
Part 5 – Publish to Office 365
Is is possible to duplicate the same many-to-many relationship behavior in VertiPaq that we have in SSAS multidimensional?
Since Tabular model projects were introduced in SQL Server 2012, one of the major blocking points for adaption has been the lack of out-of-the-box support for many-to-many relationships. I’ve been using, demonstrating and showcasing SSAS Tabular and Power Pivot for at least three years and in that time it became apparent that, even though this might only be a perceived limitation in many cases, for quite a few enterprise IT shops; many-to-many support has become a litmus test for whether this technology is ready to be taken seriously. Honestly most business data models don’t need many-to-many relationships to be useful but it is still a reality. This week Chris Webb posted about bidirectional relationship support in the new Power BI designer and demonstrated an example using sales of fruit that can exist in different categories.
The Power BI Designer is the first publically-available product from Microsoft that supports M2M relationships in a VertiPaq model and I wanted to conduct a test to see if I could duplicate the same M2M functionality as we have in a familiar multidimensional model, using the Adventure Works sample data. So, here goes…
Many-to-Many Example in SSAS Multidimensional
In the Enterprise examples for the Adventure Works Cycles sample data, the Adventure Works cube contains two many-to-many dimensional relationships. Here is one of those relationships; in the dimensional usage grid of the cube editor, you can see that the relationship between the Internet Sales measure group and the Sales Reasons dimension is defined as many-to-many:
The M2M relationship involves three tables in the data source view with the Internet Sales Reason Facts table bridging any number of Internet Sales Facts records to any number of Sales Reasons records. Not in view is the DimCustomer table which is related to Internet Sales Facts.
A measure group named Internet Customers contains a distinct count measure based on the CustomerKey column in the Fact Internet Sales table. This measure will exhibit behavior unique to M2M aggregation as you’ll see in these queries:
In the first query, the Customer Count measure is grouped by Sales Reason on rows. If we were to add the result for each group, the total would be 24,135. This is incorrect because it’s a non-additive measure – the same customer is counted for placing sales orders for different reasons so some customers are counted multiple times:
This is evident by replacing the Sales Reason attribute with the All member on rows, showing that there were actually only 18,484 distinct customers placing orders:
Narrowing the results to only one customer helps to explain. This is the key value for customer “Aaron Zhang”. You can see that Aaron placed two orders with a different reason for each:
Again, replacing the Sales Reason attribute with the All member shows that there was really only one customer:
Many-to-Many Example in a VertiPaq Model
To get started, I opened the Power BI Designer and imported the same tables from AdventureWorksDW2012. I’m using the Power BI Designer Preview as of early January, 2015. Because we don’t have a Power Pivot interface in the designer (yet?), we’re essentially using Power Query to populate the VertiPaq semantic model.
The FactInternetSales and FactInternetSalesReason tables are related using two columns: SalesOrderNumber and SalesOrderLineNumber. These columns needed to be converted to strings and then concatenated into a single column to facilitate the relationship. This is a common practice in Power Pivot/Tabular.
Using the Manage Relationships dialog, the tables are related with these three relationships:
I switched to the Report page which finalized the data load and populated the model. When I started, I was concerned about not being able to explicitly define DAX measures but it was a simple matter to change the field aggregation for the CustomerKey to “Count (Distinct)”.
Using the Power View report tool, it was also a simple matter to verify the results and duplicate all of the query results as before. Here, you can see that Aaron Zhang placed two different orders for different reasons and the total reflects the non-additive behavior of this many-to-many relationship.
In conclusion, many-to-many relationships are now supported in this implementation of VertiPaq and it behaves the same as in multidimensional cubes! This is very good news and may give us a good idea of what to expect in the near future.
Requirement: Allow users to enter comments after they review results from a Power Pivot model and then show the comment text in the Pivot Table report results. Here’s a quick example of the final result. My example uses Flintstones characters in-place of employee or customer names and a simple measure value. After reviewing the results, the user moves to another worksheet and enters a comment for the date and person with an expiration date for the comment. After refreshing the report, each cell with a comment is indicated with a little “dog ear” and the comment is displayed by clicking the cell.
In practice, the data model could be in a server-hosted SSAS Tabular model, Office 365/Power BI or SharePoint-hosted Power Pivot workbook, or a desktop Excel file. In one real world application, if entries don’t meet company standards, the person reviewing the data entry can enter comments about these late entries and other policy violations. A comment has an expiration date and in the production solution, the comment expiration is also dependent on a calculated “severity level” which can be baked into the expiration logic.
My first thought when considering the need to incorporate text strings into a semantic model was that it might not be a good idea to store large text strings in a Power Pivot or Tabular model. After all, the tabular model technology is really optimized to handle numeric and redundant data. Upon carefully talking through the requirements, we’re pretty comfortable that the number of columns will be fairly low and the length of the comment strings should be conservative. The VertiPaq/VertiScan engine is actually pretty forgiving about having “stuff” added to a model that it will ignore except when it’s being used in a calculation or query. So, that’s good – my comment rows shouldn’t get in the way of core features like measure aggregation and dimension navigation. I’m really curious to find out how viable this is in a larger-scale application so if you end-up doing this with a lot of data or have the opportunity to test the limits of this technique, please share your results.
With that in mind, I took the plunge and built a simple proof-of-concept solution; which is what I’m demonstrating here. If the storage and query engine are OK coexisting with these new column fields, the big question that remains is how will the query engine react when we start to send it some unconventional long string results. Will these long values just get crunched through the query engine expressions along with the usual measures without making a ruckus.
Now remember that this is an über-simplified model and that the real one contains about 30 tables, but the major components are the same. The main fact table (tbl_fact) contains keys related to some outside tables and a measure called “Sum of Sum Value” which aggregates the SomeValue column for every Person in a pivot table report. So, here’s the solution:
The Comments table (tblComments, which I’ll call “Comments”) is essentially fact table related to Dates and People:
The source for the Comments table is a worksheet/table. Here are some of the comments:
In the production solution, we want the comment to show up until the expiration date. Rather than waiting for the system date to change, I’ve added a table of EffectiveDate values to use as a slicer for prototyping. The DAX measure expressions are added to the calculation area in the Comment table.
I’ll start with a simple version of the the CurrentComment measure before adding the expiration logic:
Current Comment:=IF(HASONEVALUE(tbl_Comments[Comment]), VALUES(tbl_Comments[Comment]))
it’s important to test the comment for only one value using the HASONEVALUE function to avoid an error. The VALUES function just returns the column value as a string. Now, I’ll add the expiration logic which uses the first measure:
Comment Until Expiration:=
CALCULATE( [Current Comment],
FILTER( ‘tbl_Comments’, [Date] < FIRSTDATE( tblEffectivedate[EffectiveDate] )
&& tbl_Comments[ExpirationDate] > FIRSTDATE( tblEffectivedate[EffectiveDate] )
Once this is all tested and working, we can just substitute “TODAY()” in-place of “FIRSTDATE( tblEffectivedate[EffectiveDate] )” to use the system date.
Now, to add the Excel comments. This is the fun part (as if it hasn’t been exciting enough thus far!)
Add a pivot table to a new sheet name “Report”. Add Dates and People on rows and the “Some of Some Value” measure on columns (which actually adds it to the VALUES). Add the “Comment Until Expiration” to columns as well. Select the last column added to the pivot table and hide it. In my example, this is column D.
You’ll need to have macros enabled and trust VBA in Options > Macro Settings.
Use Alt+F11 to open the Visual Basic for Applications editor and enter the following code into a code module (usually Modules > Module1):
‘ Paul Turley, 9-22-14
‘ Dynamically add comments to Pivot Table value cells based
‘ on an adjacent hidden column.
Dim ws As Worksheet
Dim pt As PivotTable
Const iPTRowOffSet As Integer = 3 ‘ Pivot Table starts on this row
Const iPTHeaderRows As Integer = 2 ‘ Number of Pivot Table header rows
Const iPTClearSafetyRows As Integer = 100 ‘ Number of rows beyond the last PT row that get cleared (in-case the filtered table shrinks by a large # of rows)
Dim iRows As Integer
Dim iRow As Integer
Dim CommentRng As Range
Dim ValueRange As Range
Set ws = Worksheets(“Report”)
Set pt = ws.PivotTables(1)
For iRow = (iPTRowOffSet + iPTHeaderRows) To pt.RowRange.Rows.Count + iPTRowOffSet
Set CommentRng = pt.ColumnRange(iRow, 2)
If CommentRng.Cells(0).Value <> “” And iRow >= iPTRowOffSet Then
Set ValueRange = Worksheets(“Report”).Cells(iRow + 1, 3)
If ValueRange.Comment Is Nothing Then
ValueRange.Comment.Visible = False
For iRow = (iPTRowOffSet + iPTHeaderRows) To (pt.RowRange.Rows.Count + iPTRowOffSet + iPTClearSafetyRows)
Set ValueRange = Worksheets(“Report”).Cells(iRow + 1, 3)
If Not ValueRange.Comment Is Nothing Then ValueRange.Comment.Delete
Note that there are some assumptions made in this code. I don’t think the pivot table needs to start at cell B3 but that’s where mine is. Note the constants at the top that are used to skip the header cells. These values will need to be adjusted if you make changes.
Next, open the code module for the workbook (named ThisWorkbook) and add the following code for the SheetCalculate event (three lines added between the generated Private Sub & End Sub lines):
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Now, let the magic happen. Every time the sheet is calculated (this event fires every time it is connected, filtered, sliced or the pivot table is changed), the code iterates through the measure column cells and removes all the Excel comments and then iterates through and adds new Excel comments using the values in the adjacent hidden column. The hidden column contains the calculated “Comment Until Expiration” measure text values.
You can add or remove comment text in the Comments sheet, move to the report sheet and use Data > Refresh to update the model. Use slicers to change the EffectiveDate and filters to add or remove row values. With each action, you should see comments come and go. To view a comment, click the cell and hover the mouse pointer over the dog-ear in the upper-right corner of the cell. There are more comment options on the Review ribbon. Make sure you save this as a macro-enabled workbook (.xlsm) file.
As always, I’m interested in feedback, thoughts and suggestions.
This entry was posted in BI Industry, BI Projects, Business Analytics, DAX, Microsoft BI Platform, Power BI, PowerPivot, Self-service BI, SQL Server Pro Magazine, SQL Syndication, SQLServerPedia, Tabular Models.
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!
Here’s a sample:
Primer on Power BI (Business Intelligence)
Power BI is new and emerging self-service business intelligence and business analytics framework brings together and enhances key Microsoft technologies:
- SQL Server
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.
Wednesday Precon Schedule:
Predictive Analytics for Absolute Beginners (AAT-299-P)
Real-World Small Big Data (BD-199-P)
Superhero Power BI (AV-299-P)
Wednesday, May 7
07:00 AM – 08:00 PM
07:30 AM – 08:30 AM
Continental Breakfast**, Exhibit Hall
08:30 AM – 4:30 PM
Thursday, May 8
07:00 AM – 06:30 PM
07:00 AM – 08:00 AM
Continental Breakfast, Exhibit Hall
08:00 AM – 09:15 AM
Keynote – Amir Netz and Kamal Hathi
09:15 AM – 10:00 AM
Refreshment Break, Exhibit Hall
10:00 AM – 6:15 PM
Friday, May 9
07:00 AM – 04:00 PM
07:00 AM – 08:00 AM
Continental Breakfast, Exhibit Hall
08:00 AM – 09:15 AM
Keynote – David McCandless
09:15 AM – 10:00 AM
Refreshment Break, Exhibit Hall
10:00 AM – 5:00 PM
It’s 8:00 AM Pacific Time on the 8th of May, 2014 and the keynote session is beginning…
Welcome by Tom LaRock, President of PASS
”we get paid to work with data everyday – it’s just hard to explain to our family what we do”
For over 14 years, PASS has connected data professions for all over
PASS has over 100,000 members
John Whittaker from Dell:
“Is big data really happening in the middle market?”
Outcome is successful when there is business and IT alignment
“It’s easy to be clever with big data but it’s easy to be freaky with it”
Top big challenges:
- Variety of types & structures
- Sheer volumes
- Budget limitations
- What data to use for business decisions
- Getting business to share across org silos
- Inaccurate/data quality
- Understanding where to focus decisions, metrics, KPIs, etc.
Dell has a large software division (manage, integrate & analyze data) – audience poll showed that about 20% of attendees knew that
Amir Netz & Kamal Hati from Microsoft on stage
“Satya loves data…”
This change in leadership at Microsoft will mean a greater focus on data and analytic technologies, especially cloud services.
Kamal: “Satya is making Indian accents cool at Microsoft”
- 2 million Power Pivot downloads
- 100,000 Power Query downloads
Power BI demos, using Power Q&A to explore the Power BI usage statistics
- Sept: usage spike when Q&A went public in preview
- March: huge spike when Power BI was released
- Over 1 million Q&A questions answered in April
- Power BI demo contest winner was Michael Carper – “can tweeting affect the outcome of an NBA game?” https://fb-3785439.strutta.com/entry/5477264
- Kamal demo: analyzing Internet browser usage
- 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.
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
…then add labels and numbers to add value