Skip to content

Year: 2017

Power BI Time Values in the Browser are Different Than Those in the Desktop

Have you ever had this experience? You build up your Power BI report using Power BI Desktop, and then when you publish and view the same report in the service, the date/time values are off by a set number of hours. I certainly run into this often enough that it warrants a blog post.

That you’ll notice is that this offset corresponds with the difference between your time zone and UTC (Coordinated Universal Time), the universal time standard. Now, you’ll notice that I didn’t say GMT (Greenwich Mean Time) which many people take to be the time standard, but the difference is that UTC is a time standard, and GMT is a time zone. GMT observes daylight savings time, and UTC does not – it remains constant. In my case, my time zone is Eastern, which is 4 hours behind UTC in the summer, and 5 hours behind in the winter. It is always 5 hours behind GMT. This distinction becomes important as you’ll see.

Coming back to our problem, we will see this problem if we have used the Date/Time/Timezone property in a Power BI Query (or a Power Query in Excel). When one of these column types are converted to Date/Time, the value is automatically localized. The reason that we see the different values is that when it is localized in Power BI Desktop, the machine’s time zone is used to perform the conversion. When this conversion happens in the Power BI service, UTC is used, and this results in different times being used in the browser. This difference can easily throw of any measures that depend are based on time values.

Column as Date/Time/Timezone

Same column as Date/Time

Further complicating things is the fact that DAX (Power Pivot) has no concept of Date/Time/Timezone and all columns of that type are brought into the model without the automatic conversion to local. So, how do we deal with this discrepancy?

We can’t rely on the Power BI service to automatically show the viewer the correct time for the location they happen to be in, so we need to be specific about the time zone that we’re working with. In my case, I need to do this for my tyGraph for Twitter reports, like this one for Microsoft Ignite 2017, or most recently in this session browser for Ignite. For these types of reports, I adjust the times to match the time zone of the events themselves. To do this, I use the modelling capabilities in Power BI desktop to create a new calculated column in the same table that contains the UTC based date.

In DAX, time calculations are done in decimal fractions of a day. Therefore, to create a new column named StartLocal by subtracting 4 hours from a date/time column named StartUTC in a table named Sessions, the formula is:

StartLocal = Sessions[StartUTC]) - 4/24

If the source column contains null or blank values, they will be returned as 1899-12-30 4:00 AM using the formula above, so it’s a good idea to test for this case and return nulls when appropriate. The above formula then becomes:

StartLocal = If(IsBlank(Sessions[StartUTC]),BLANK(),Sessions[StartUTC] - 4/24)

This calculation will reliably return the time in Eastern Daylight Time (UTC -4) for a column that has UTC date/time values in both the Desktop and in the service. If all subsequent time calculations are based on this column, then it is a simple matter of substituting in the appropriate UTC time offset to the calculation above to show the time in a desired time zone.

It would be nice if the report would allow us to specify a “home” time zone with which to base all conversions on. Time zone could then be a property of the report. Until such a time as that happens, this should prove a suitable, if complicated workaround.

4 Comments

Turkish Airlines Fail – A Case Study in Terrible Customer Service

This will be one of my rare non-technical travel posts. After departing the European Collaboration Summit 2017, my wife Olivera and I headed to South Africa for a holiday, and to attend the Share Conference in South Africa. Our travel plans got suddenly turned upside down after leaving Zagreb, and I felt that it would be useful (and entertaining) to share this cautionary tale. I’ve never experienced anything like it.

Upon landing at the Istanbul airport at approximately 11:00 PM, I turned on my phone, and very quickly received a text message from Turkish Airlines that our connecting flight (Turkish Airlines TK38 to Johannesburg at 1:40 AM June 2) had been cancelled. The message included a phone number. Not wanting to be “that guy” on the phone on an airplane, I elected to wait until we were in the terminal to talk to someone. As we deplaned, we were met by a representative who was there to divert us away from the “connecting flights” route. We asked what we were supposed to do, and were told that the “hotel check in” desk would help. Apparently it was just past passport control.

Now, I had no idea what a hotel would have to do with making alternative travel arrangements, but it was pretty clear that no more information was forthcoming from this guy. OK, we can talk to a Turkish Airlines desk, or so I thought. We then proceeded to passport control, and we came upon the Turkish Airlines Transfer desk, with several employees. “Aha” I thought – here’s where we’ll be able to sort things out. We waited a few moments for our turn, and I presented our boarding passes. I was then informed that the flight had been cancelled (no kidding). I started to ask some clarifying questions, and was only told that I would be helped at the “hotel desk”. I asked about our luggage, and was told that we could pick it up or not – it was up to us. Trying to figure out whether or not we should, I kept asking questions, and finally the rep literally stood up, and walked back into the office.

I tried asking his colleague additional questions, and it was quite clear that he didn’t want to be helpful either. The next step was laid out quite clearly to be passport control. We joined the very long line for passport control. As we proceeded to the line, I noted a large sign across the hall indicating countries for which a visa was required. Canada was not on the list, so I thought little of it. Being Canadian, I’m lucky enough to not require a visa for most destinations. After about one hour, we reached the officer, and he asked for our visa. I simply told him that we didn’t plan on entering Turkey, but he didn’t seem to care much. He pointed across the hall, so off we went, feeling pretty frustrated.

We then joined the line for the electronic visa. Looking around, I couldn’t help but notice the special passport area for Turkish Airlines, and how there was absolutely no staff in it. In fact, there was no staff or help anywhere. I then looked past the electronic visa machines and saw a visa counter. Curious, I walked up to it to find out what it was all about. As it turns out, Canadians need a paper visa, not an electronic one. I waved Olivera up to the counter thinking we had caught a break, there was no line here. I then requested a visa from the lady behind the counter. To put it nicely, it didn’t seem like she felt like being particularly helpful. Firstly, it would not be possible to use a credit card. At this point, I wasn’t all that surprised. I then asked how much I would need, and was told $70 US, and was directed to the ATMs nearby.

The ATMs didn’t work to well (one button consistently required 10 presses to activate) and it turned out that it could only dispense $50 at a time. I then made 2 transactions, incurring two transaction fees, and walked quickly back to the counter to try again. The lady then asked me for $140. Why? Well, that’s for two people of course. To be clear, Olivera was standing beside me when I asked the original question. Off I go, make another transaction, and we finally got our Visas, even though we had never wanted to enter the country. Finally, I should point out that the passport stamp actually says that the visa costs $60, but that’s a minor issue.

We then wait in line again, and this time we get through passport control to the luggage area. Of course, our luggage is nowhere to be seen. Given how long it had taken to get through, it had been stored to be put on the flight that we would ultimately be on. To be clear we still had no idea as to when that might be. We went to the baggage office to get our bags, and were told that they would be “sent along with us” and that we couldn’t retrieve them. At this point, it was past 2 AM, so we felt resigned. We were beat, now missing at least a day, and very tired.

We then proceeded to the “hotel check in”. As it turned out, this was actually a Turkish Airlines office in the airport. Apparently this happens so often that they have an office staffed just to put people up in a hotel overnight.

After standing in line again for about half an hour, we were given a slip of paper and told to wait until our names were called, and then we would be driven to a hotel. We took the opportunity to ask a few questions, like when do we get new boarding passes, and why was the flight cancelled. This began a comedy of answers. Every time someone asked a question, we received a different answer. We were told that there were “operational issues” and that “South Africa had refused entry”. We were also told that the flight was not cancelled and would happen sometime tomorrow, and we would have the same seats. We were also told that no, we would be on the next available flight, whenever that might be and would be assigned new seats. We were told a number of other things as well.

As an aside, when I had checked in, the flight was showing as less than half full. Now combine this, with the fact that hotel rooms in Istanbul are insanely cheap, and a suspicious person would conclude that it was cheaper to put us all up in a hotel, than to fly to Johannesburg less than half full. That doesn’t explain the total lack of information however.

The one clear message was that we must go to the hotel, and wait for a phone call from the front desk advising us further. Under no circumstances should we leave the hotel as the flight could happen anytime, and they would not contact our cell phones directly. Finally, after waiting for another half hour or so we were taken to the hotel, where we again got to wait another half an hour to check in. After being given food vouchers, we got a room, and were told that we would be picked up at 11:00 PM the following night, but don’t leave because it could be sooner.

We got to sleep at about 5:30 AM that night.

The next day, I set about making alternative arrangements, notifying our destination, etc. At lunch, we started speaking with some fellow passengers who had had various communications indicating that it didn’t look good for the next nights flight, and that even if it did go, it was already full. We ran back to our room, and I called Turkish Airlines. Olivera started looking for one way flights to Johannesburg. I got through to Turkish airlines, and the rep there (who wasn’t rude – I mention this because that was the exception) told me that the flight was NOT cancelled, and was showing as active. As such they could do nothing for me (including a refund) and that I would have to call the office at the airport. For the next half hour, I tried, and not once was the phone picked up. Just constant ringing. Later on, we talked to someone that did get through and was told that it was a political issue, it came from head office, and there was nothing they could tell them at all.

In the meantime, Olivera found $400 flights on Emirates air that night to Johannesburg. We bought them – at least we knew we’d get to our destination. The Emirates flight was at 7:30 that night, and it was currently 1:30 PM. Given the way that things moved, I figured that we had just enough time to make it.

We jumped in a cab, and headed to the airport. “International arrivals” I told the cab driver, and explained why. He nodded and took us on a rather adventurous ride to the airport. Once we got there, I could see that he was taking us to departures. Next he tried to take us to domestic. I wound up reading signs and telling him where to go.

Upon arrival and a security check to enter the building, we headed back to the hotel desk to retrieve our luggage. We were then directed to an information desk where we were handed a number for Turkish Airlines baggage and told to call it. They were apparently needed to come out, walk us back through customs to retrieve our bags. We tried calling, and got no answer. However, there were two other ladies there in the same situation that we were, and one of them had gotten through about 15 minutes previously, and were told someone would be there in 5 minutes. After waiting a further 10, “Mr. Helpful” showed up.

Mr. Helpful

Mr. Helpful took everyone’s passports and baggage tags, and then led them all back through. There were two of us so I elected to stay behind to watch everyone’s carry ons, and Olivera went with the ladies and Mr. Helpful. Then time passed. A lot of time – 45 minutes in total. Finally, Olivera came back out concerned that I had to go upstairs to finalize the Emirates ticket sale. I had received email indicating that it was already finalized, but she had no signal in the area. She needed to go back in, so we called the number. No answer. Another couple had shown up and were also trying to get through, but to no avail. Olivera decided to go ask a nice man with a machine gun to let her back in. Incredibly, this worked.

After another 15 minutes, she emerged with our bags, and one of the ladies. However, we were still watching the carry on for the other lady, so we needed to stay put. We were anxious to go check in, so we asked the new couple if they would mind taking it to her when they went in, and they agreed. In the meantime, they had gotten through to the office and Mr. Helpful showed up, and went through the same routine. I then asked them if he could take the bag to the lady, given that he knew who she was. He literally looked at me and shrugged his shoulders. I said, ok, fine, could you just point her out to this couple? He shrugged his shoulders again.

I am not one to lash out at service workers, but apparently that was the final straw. I had my luggage, and he couldn’t slow me down any longer, so suddenly a stream of profanities escaped me in his direction as he shuffled down the hall. The other couple had the lady’s luggage in tow, and presumably that worked out well.

As a final insult, we went upstairs to departures, to check in with Emirates. We couldn’t find their desk, so we asked am employee where we might find it. She looked, shrugged her shoulders, pointed at the information board (which was no help) and walked away.

Finally, we found the Emirates counter at the end of the hall, and it was as if we’d walked into a meadow with fresh air. The staff were genuinely helpful and friendly, they got us checked in, found us exit row seats, and even gave us tickets for the express line for our trouble, even though it wasn’t their fault. We had no more incidents after this point.

Now, to be clear, the experience that we had with Turkish Airlines staff on the ground in Toronto and Zagreb was just fine. The experience in the air was above expectations – really quite good. But the experience at the Istanbul airport was the worst I have ever experienced, by a long stretch. Not all of it was Turkish Airlines’ fault either. Some of it was the airport staff. Things happen when flying, even cancellations, and I can understand that. However, it is how these events are handled that matter. They should have had staff walk all connecting passengers through the process, confirmed what was happening, and set our expectations for the following day, That would have led to a completely different experience. However, the airline’s complete lack of communication and lack of any assistant whatsoever has cost us the price of a flight on a competing airline, and has cost them any business from us, and potentially anyone reading this in the future.

3 Comments

The Power BI Premium Pricing Model – The Good, The Bad and the Ugly

*Update – October 2017* – This post is still valid but more options are now available. I have another post outlining the changes here

On May 3, Microsoft announced sweeping changes to the pricing of Power BI by introducing a new “Power BI Premium” SKU. The announcement itself can be found here, and there a number of other related resources worth that I am listing here for convenience:

Distribute to large audiences with Power BI apps
Changes to Power BI embedded
Power BI Premium White Paper
Power BI Pricing
Premium pricing calculator
Introducing Power BI Report Server

Power BI Premium is intended to address deficiencies in the current pricing model primarily with respect to sharing content. In my opinion, the new model succeeds in this goal for the most part, but it leaves a significant number of customers behind, and it also leaves many unanswered questions. These problems need to be addressed for Power BI to succeed in its goal of bringing BI to the masses. Overall, I like what Microsoft is trying to do with this new pricing model, and with a few tweaks, I think that it can resonate.

First, we need to understand the new model, and to do that, we need to understand the former model and the need for Premium. Given that the former model (consisting of free and Pro licenses) has not been replaced (although it is changing significantly), we will refer to it as the original model, and when Premium is added to it, we will refer to that as the Premium model. The original model is still completely relevant moving forward.

The original model and the need for change

The original model is relatively simple, and relatively unique to the industry. Power BI users are licensed for either free or Pro features. If a report contains any Pro capabilities, any consuming user requires a Pro license. A free user can create a report that uses Pro features, but that same user will not be able to consume that report in the free service. This is a very important distinction to understand. The author of a report (using Power BI Desktop) could be a free user, use a Pro feature, and after deploying the report to the service, be unable to use it in the service.

Difference between free and Pro from a feature standpoint is no longer (as of this writing) available on the Power BI pricing page, however, prior to June 1, 2017 it is the list below.

Therefore, if a report is configured to be refreshed more than once per day, or even if the time of day is specified, or if the report uses on-premises data, then all users accessing that report require a Pro license. Given that Power BI is all about bringing Business Intelligence to the masses, when each one of those masses needs to pay $10/month, it tends to constrain adoption, particularly if a report’s audience is organization wide, and you are in a very large organization.

Report sharing is also relatively limited. Reports can be shared anonymously, which is insecure. Dashboards and their constituent reports can be shared either internally or externally, but they are read only. Finally, both dashboards and reports can be shared through Group workspaces (now app workspaces). Currently, Group workspaces do not allow for external sharing, but they are the preferred means of sharing. However, they too require Pro licenses, which constrains adoption. For the free user, anonymous and dashboard sharing are the only real options.

New model

The introduction of Power BI Premium aims to solve some of the sharing issues listed above, and therefore to help drive adoption. Premium capacity is an add on to a Power BI tenant (organization), and is different that free or Pro licenses which are assigned to users. An organization can purchase Premium capacity, and then a Pro user (this is restricted to Pro) can move or publish content to the Premium capacity. Once the content is in Premium storage, all users can utilize all the features in the dashboards and reports. Premium effectively removes all feature barriers from the reports.

Premium storage also brings many performance enhancements, such as the ability to refresh data up to 48 times per day (vs the previous 8), and the effective removal of data model size limits.

Without Premium, there are also several changes to the original licensing model. According to the May 3 Announcement FAQ on the Power BI community site:

Beginning June 1, the free service will have capabilities equivalent to Power BI Pro. This includes the same 1 GB workbook size limit, up to 8 daily scheduled refreshes for datasets, and maximum 1 million rows/hour streaming data rate. We’re also providing access to all data sources, including those available through the on-premises data gateway. Peer-to-peer dashboard sharing, group workspaces (now called app workspaces), export to PowerPoint, export to CSV/Excel, and analyze in Excel with Power BI apps are capabilities limited to Power BI Pro.”

Therefore, after June 1, 2017, Pro features are effectively an addition to the free features, and the feature differences should be as below:

From the May 3, 2017 announcement:

“Going forward, we will improve the free service to have the same functionality as Power BI Pro, but will limit sharing and collaboration features to only Power BI Pro users.”

The only features that Pro will have that free will not are those that are related to sharing. The above feature list reflects that.

Power BI Embedded

Power BI Embedded is the way that developers can embed Power BI in their applications. Using Power BI Embedded, until now, developers build reports, deploy them to their Azure instance, and call them from their applications. End users do not need any sort of Power BI licenses, and the developers are charged per report “render session”. This charging model has been one of the criticisms of Power BI embedded in that it is very difficult to predict costs. ISVs are at the mercy of the end users viewing reports, and any measure that is put into place to curb these render sessions is by definition a disincentive to adoption.

The fact that Embedded runs in a different namespace than the core Power BI service is another, leading to differences between the capabilities of Power BI Embedded and the core Power BI service. For example, the current iteration of Power BI Embedded cannot use the On-Premises Data Gateway, which can be quite restrictive.

Power BI Embedded is changing to use the new Premium capacity model. ISVs will purchase Premium capacity, and serve reports to their end users from that space. There will only be a single namespace for all Power BI content.

What’s Good

Power BI Premium solves to sharing problem for organizations that want to distribute their BI assets across the organization. If organizations would be accessing on premises data, a key feature of Power BI for enterprises, the Pro license requirement has discouraged adoption. With Premium capacity, an report publisher can share content with as many users as necessary without worrying about licensing the target users. Even better, those target users can be external, further extending the reach of that content.

For large enterprises, this has the potential to turn Power BI from a niche solution to something that is used by everyone.

The changes to the original model also makes things clearer for report designers and publishers. These publishers can work with the full range of Power BI features while the report is being built, and while they are themselves using it. When it comes time to share the report to a wider audience, they can publish it to Premium capacity where anyone can access it. If the organization has not purchased Premium, then the original model applies, and all recipients will still require a Pro license.

On the Power BI Embedded side, switching to Premium capacity completely eliminates the unpredictability of the current model. The fact that the reports will be rendered from the core Power BI service means that it will be fully on par with other Power BI reports, and developers will be able to take advantage of the full spectrum of Power BI features as they appear in the service.

What’s not so good

If you are a large company, there is very little not to like with this new model. It was large organizations that felt most of the pain with the original model, and it is they that benefit most from the Premium model. In fact, in my opinion, they are the only ones that benefit from the Premium model. Well, they and organizations that have no sharing requirements. The issue here is cost.

The Premium pricing estimator can be found online, but at present, it boils down to this. The smallest block of capacity that can be purchased by an organization is “P1”. To publish content to Premium capacity, you must also have a Power BI Pro license. Therefore, the minimum cost of entry is $4,995 (P1) plus $9.99 (Pro) for a total of $5,004.99 per month. This is well out of the reach of most small to medium sized organizations. In fact, an organization needs to be larger that 500 users (and those would be active Power BI users) for Premium to begin to make sense from a licensing perspective. The model size limit removal and the increased refresh frequency are also compelling reasons to move to Premium, but it’s easy to see that Premium is only for larger organizations.

Compounding this issue for small to medium sized organizations is the fact that as of June 30, dashboard sharing has been removed from the free SKU of the original pricing model. Any dashboards that had previously been shared broadly to free users will cease to function as of the cut-off date. If Premium does not make sense for these organizations, then they do have the option of purchasing Pro for the consumer. To help ease this transition, Microsoft is offering a year’s worth of Power BI Pro to all active free users that signed up prior to May 3, 2017.

However, dashboards can be shared with external users, and it’s a pretty tall order to expect an external user to subscribe to Pro just to be able to read your report.

With Power BI Embedded switching to the Premium model, the ISV now needs to buy Premium capacity. Given that the entry price for Premium is so high, it is (in my opinion) out of reach of most of the services that would rely on it, not to mention those developers that simply want to get up to speed on it or do some testing. There has recently been some indications on the forums that the barrier to entry won’t be as high for developers, but even a figure as low at $600/month may still be too high for many to swallow.

Conclusions

Overall, I think that the Premium pricing model solves a problem that desperately needed to be solved. This approach opens the door to Power BI truly democratizing Business Intelligence and becoming almost as ubiquitous as Excel. The opening up of features to the free SKU and focusing the Pro SKU on sharing means less confusion for report designers.

Unfortunately, for the moment price stands in the way of that goal of many small-medium sized businesses. These businesses may be small in stature, but they are many in number. The removal of sharing from the free SKU actually represents a step backward for them. The floodgates have been opened for large businesses, but the stream has been dammed for smaller ones.

Fortunately, pricing is a simple problem to solve. My hope is that the entry point for Premium comes down to something that would make sense for even a 10-person company, and that the cost for developers using Embedded could scale with far more elasticity, starting at $0 to encourage investment. These changes would, in my opinion, truly set the stage for Power BI dominance.

17 Comments

Use Power BI to Help Manage Your SharePoint Sites

Note – This article first appeared on April 12, 2017 on the Microsoft Partner Network

When it comes to Business Intelligence, SharePoint is most often used as a platform to access dashboards and reports. With the recent availability of the Power BI web part, Power BI joins SQL Server Reporting Services and Excel as a go-to reporting tool within SharePoint.

Occasionally, list data is used as a data source for these reports. This doesn’t work for large amounts of data, but for smaller lists, this is perfectly adequate. Given that Power BI has native connectors for both SharePoint lists and libraries, it is perfectly suited for this sort of task. Combining these two results in some interesting possibilities, as the following article demonstrates.

We work extensively with modern Groups in Office 365. Each group gets its own SharePoint site, and within that, its own OneDrive, or “Shared Documents” library. Depending on the usage of the group, the storage in that library can grow quickly, and it’s not always easy to spot where all the content is being stored. By building a Power BI report that uses the OneDrive as a data source, we can create a report of storage allocation by file and folder, and then show that report on the home page of the SharePoint site.

There are several steps to building this report. It all starts with Power BI Desktop.

Get the Data

To start with, we Launch Power BI Desktop, and Select “Get Data”. Then we select the “SharePoint Folder” file source, and enter the URL of the SharePoint site. Even though we are prompted for the URL of the folder, we must enter the URL of the site itself. The query editor can be used later to filter out any unwanted folders. Only user created document libraries and folders will be returned.

The query will return a number of columns that are irrelevant to this report, and they can be removed. We need to create a column for the URL to the files themselves. The attributes column can be expanded to get the size of any files in bytes. We also use the split function to split the folder path by the “\” delimiter which will allow us to create a folder hierarchy. Finally, we set the appropriate data types on columns, and give them user friendly names.

The scope of this article does not allow for a complete step by step walkthrough of the query editor, but the code below can be pasted into the advanced editor (after replacing the URLs appropriately).

let
  Source = SharePoint.Files("https://yoursharepointsiteurl", [ApiVersion = 15]),
  #"Removed Columns" = Table.RemoveColumns(Source,{"Content"}),
  #"Added Custom" = Table.AddColumn(#"Removed Columns", "Folder", each [Folder Path]),
  #"Added Custom1" = Table.AddColumn(#"Added Custom", "URL", each [Folder Path] & [Name]),
  #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Folder Path"}),
  #"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","https://unlimitedviz.sharepoint.com/sites/Presentations/Shared Documents/","",Replacer.ReplaceText,{"Folder"}),
  #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Folder", "FolderBase"}}),
  #"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Custom", each Text.Trim([FolderBase],"/")),
  #"Renamed Columns1" = Table.RenameColumns(#"Added Custom2",{{"Custom", "Folder"}}),
  #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"FolderBase", "Date accessed"}),
  #"Expanded Attributes" = Table.ExpandRecordColumn(#"Removed Columns2", "Attributes", {"Size"}, {"Size"}),
  #"Changed Type" = Table.TransformColumnTypes(#"Expanded Attributes",{{"Size", Int64.Type}}),
  #"Renamed Columns2" = Table.RenameColumns(#"Changed Type",{{"Size", "Size (bytes)"}}),
  #"Added Custom3" = Table.AddColumn(#"Renamed Columns2", "Size (KB)", each [#"Size (bytes)"] /1024),
  #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"Size (KB)", type number}}),
  #"Added Custom4" = Table.AddColumn(#"Changed Type1", "Size (MB)", each [#"Size (KB)"] /1024),
  #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom4",{{"Size (MB)", type number}, {"Date created", type datetime}, {"Date modified", type datetime}}),
  #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type2","Folder",Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv),{"Folder.1", "Folder.2", "Folder.3"}),
  #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Folder.1", type text}, {"Folder.2", type text}}),
  #"Renamed Columns3" = Table.RenameColumns(#"Changed Type3",{{"Folder.1", "Folder"}, {"Folder.2", "Subfolder 1"}, {"Folder.3", "Subfolder 2"}})
 in
  #"Renamed Columns3"

Build the Report

When the Query is complete, we click on the load the data into the model. We don’t need to do a lot of model editing for this report, it’s relatively straightforward. There is only one table, and the Date Created field gives us enough time intelligence that we don’t need to create a date table. There are two edits to the model that I used that bear mention.

One thing that I wanted to show was the accumulation of storage over time. With the size of the file and the create date, I could show the total size that was added for a given day, month or year, but that doesn’t show the accumulation. To do that we need to create a calculated measure, “Cumulative Size”. The formula below calculates a running total of file size based on date:

Cumulative Size (MB) =
 CALCULATE (
  SUM ( Files[Size (MB)] ),
  FILTER (
  ALL ( Files[Date modified] ),
  Files[Date modified] <= MAX ( Files[Date modified] )
  )
 )

It’s not strictly necessary, but it’s convenient to create a folder hierarchy by dragging subfolder1 onto Folder, and then dragging in subfolder2 to the bottom of it. That allows all levels of the folder hierarchyto be managed as one.

Finally, we add our visual elements to the report. The report itself can be seen above. In this case, the Size by Folder chart uses the folder hierarchy as the x axis so that clicking on a data bar (while in drill down mode) will open a lower level folder. Marking the data category of the URL field will cause the report to display a clickable URL in any tabular visuals, and setting the “URL icon” property (in the Values section) of the table will display a link icon instead of the long URL. Doing this will allow the user to open any of these files directly from the report. The Growth Rate chart used the Cumulative Size calculated measure created above.

Embed the Report

Once completed, we publish the report into Power BI. It is important to select the correct workspace for this. Since we will be embedding the report into a SharePoint page, it is important to ensure that all viewers will have access to the report. By publishing the report to the same Power BI Workspace that is used by the SharePoint site in question, this will be automatic. In this case, we are reporting against the “Presentations” team site that is associated with the “Presentations” group, so we publish this report to the “Presentations Power BI workspace.

Once published, we need to get the embed URL for SharePoint. This can be determined by opening the report in Power BI, selecting File- Embed in SharePoint Online.

Once we have the URL, we navigate to the SharePoint site and edit the home page (note – the home page needs to be a modern SharePoint page). Once in edit mode we add a new web part, and select the Power BI web part. When prompted, we enter the embed code retrieved above. Once the page is published, all is complete.

Finally, the data source in Power BI will need to be set up to refresh on the frequency required.

With a few simple steps, we have not only gained insights into the storage patterns of our team sites, but we have made those insights available to all members of the site in a highly interactive fashion, without making them open another application.

2 Comments

The Difference Between Reporting and Analytics is 42

In his novel “The Hitchhiker’s Guide to the Galaxy”, Douglas Adams envisioned a giant supercomputer named “Deep Thought” that was built to solve the answer to the ultimate question of life, the universe and everything. For the 5 people out there that are unfamiliar with the story, I’ll relate the important bits here. Deep Thought was commissioned by a race of pan-dimensional beings and required seven and a half million years to complete its calculations. When it was finally complete, Deep Thought informed the descendants of the original creators that the answer was 42. The receivers were understandably disappointed with this response, and when they questioned Deep Thought further, the computer postulated that perhaps the problem was that they never really knew what the question was.

Undeterred, the race then commissioned a second computer (which happened to be the Earth) that would calculate the ultimate question. After a couple of 10 million year attempts, the ultimate question was determined to be “What do you get when you multiply six by nine”. Of course, Adams never claimed that the universe made sense.

To my mind, this is an excellent demonstration of the difference between reporting and analytics. The accurate answer (report) provided a result, but not meaning. Further analytics were necessary to determine context.

Like many information technology terms (Big Data, machine learning, CRM) Business Intelligence (BI) is one of those umbrella terms that many people use regularly without fully understanding its meaning. BI is comprised of many tools that help to glean information and insights from raw data. Thus, an ETL package that moves data from one location to another is just as much a BI tool as is a fancy looking infographic. Combine this lack of clarity with the overloading of the term “reporting, and we wind up with some real confusion in this space.

Reporting is the process of using data to highlight things or trends that have already happened. This can be contrasted with monitoring, which does the same for things that are happening now, and predictive analytics, which tries to predict what will happen in the future based on the same data. The difference between reporting and monitoring is only one of data latency, and as such, monitoring is often referred to as real time reporting, which further muddies the water. However, for the purposes of this article, I want to focus on historical reporting.

Reports are typically one of two types, either operational or analytical. Tools that are good at producing one type are typically not so good at producing the other. What’s the difference? Operational reports are designed to provide information that we know we need, and analytical reports are designed to help us discover things that we didn’t know, or to help answer unanticipated questions. Operational reports are typically designed to be printed. They are typically well paginated, pixel perfect, and provide a single view of the data within any given report. Analytical reports are just the opposite. They are designed with visuals as a starting point, but allow for the ability to pivot on or drill down into the data as appropriate to answer ad-hoc questions. Printing is typically a weakness for analytical reports, whereas drilldown is a weakness for operational reports.

Both report types have their place but they both have very different design point. The data that backs an operational report should ideally be relatively flat, as that best reflects the report layout and helps with performance. Conversely, cubes and data models exist simply because a flat data structure does not adequately support analytical reporting. With analytical reporting, a user may at any point decide to view quantitative data (a measure) through the lens of a different facet (dimension). This difference is so great, that we need a different type of engine to support it. OLAP cubes and tabular models are both examples of this.

Another difference is the data that is necessary to support both report types. Operational reports tend to concern themselves with various levels of subtotals per the predefined facets. In a case like that, the data mart that backs the report only needs to store those subtotals. The granularity, or resolution of the data stored in the data mart does not need to exceed that of report that references it. Analytical reporting is different. Since users will be expected to drill down on data, from on dimension to another, or to filter the data according to increasingly granular facets, it is critical to store all of the data in the data mart backing the data model. We don’t know the level of resolution the analyst will need; therefore, all detail is required.

As a simple example of this, consider the case where we want to analyze some server log data over a period of time. We can pre-aggregate the data in the data model such that it stores the total of the log entries of various entries on a daily basis. There would need to be a total based on each dimension, but the overall data storage would be less than for the raw data. Such data would allow an analyst to spot trends over several days, but the decrease in resolution means that it will be impossible to spot any usage trends within a given day. If daily trends will never be necessary, then this doesn’t matter, but the nature of analytical reports means that the designer can never be sure.

The more that the source data for the report is pre-aggregated, the less that report becomes analytical in nature, and the more it approaches operational. This is regardless of the tool used; you can build either report type with any tool, it’s just that it may not be optimal.

The issue here is one of semantics. Semantics however are important in knowing what you are getting if reports are being provided to you. Calling something “Analytics” does not make it so. If you spin up a content pack in Power BI, and find that the underlying data model provides just enough dimensions and measure to construct the provided report, and that you can’t deconstruct the data in any meaningful way, what you have is a report, not analytics, no matter what the platform. As with anything, there is a trade-off between complexity and power. Given the nuances of this topic, it’s important to look under the hood to know what you are getting.

The answer “42” is perfectly acceptable if you already knew that the question was “what is 6×9?”. But if you want to know why, that takes a little more digging. You’d also know that there might be a data problem…

3 Comments