Articles for the Month of August 2015

Aug 28 2015

DAX Date Calculations Not Working in Power BI Desktop? Here’s a fix.

Author: Ginger Grant • Discussion: 3 Comments

Power BI offers a number of different ways to access data needed for data visualization and analysis. The reasons for selecting Power BI Desktop or Excel Power Pivot are more than likely going to change after I write this, but right now, Excel provides the ability to upgrade to a Tabular SSAS model, where Power BI Desktop does not. While Power BI Desktop and Excel both provide the ability to create formulas in DAX, only in Excel Power Pivot do you have the ability to use DAX Time and Date based calculations, but you can make Power BI Desktop do it, with help from Excel.

DAX Time and Date Calculations won’t work in Power BI Desktop

The DAX language has a number of Time and Date Calculations which contained common functions which relate to a given time frame. Here’s a list of Time and Date DAX Functions from Microsoft. These are very useful functions which you may find yourself wanting to do from time to time. None of them work in Power BI Desktop. Why? Power Pivot requires you to click on the Mark as Date Table to identify a table which contains column containing a list of unique Date and Time fields for DAX Date and Time functions to work. If you don’t have a table containing a list of dates and times in your model, you cannot use any DAX Time and Date Calculations. If you need a table like this, check out my blog post on Date table generation using a CTE to create one for you.  There is no place in Power BI Desktop, as of right now, to perform the equivalent of Mark as Date table which means that DAX Time and Date Calculations like SamePriorPeriodLastYear will not work.

Example of Power BI Desktop DAX Calculation Not Working

Here’s an example of something I tried to do in Power BI Desktop. In this first screen, I have created a new measure, which I called Prior Sales, which is designed to return the PriorPeriodnotWorkingprior period, which can be something smaller than year, of the Total Sales. The DAX for Total Sales is Total Sales = sum([Sales Amount]). Note there are no errors in the Prior Sales DAX measure.

 

Here’s what the report using the two measures, Total Sales and Prior Sales. Prior Sales shows no values but a total. This is exactly the same thing which happens in Excel when the Mark As Date Table has not been selected.

Fixing DAX Time Date Calculations in Power BI Desktop

What can you do to fix this problem? Fortunately the fix is pretty easy, but the only trick is you have to start by creating your date table. You cannot do it later, you will have to start over. Here are the steps. In Excel, create a Power Pivot Model and add in a ImportIntoExcelDate table. You do not have to have anything else in your model. Make sure that the date table is marked as a Date Table. Save the Excel Document and exit out of it. You are going to import this file into a Power BI Desktop Model. You do this by clicking on the menu button at the top Left of Power BI Desktop and selecting the menu option Import. If you do not see this option, you probably are not using the August 20, 2015 version of Power BI Desktop. Go get the latest version of Power BI from Microsoft which you can do here. Select Excel Workbook Contents, and you will get the Import window shown below. If you read the text, which to be honest I didn’t until after I hit Start PriorPeriodWorkingand ended up in a new Power BI file, you will see that the text clearly states a “A new Power BI file will be made for you”. This means when you import data into a Power BI Desktop file, you have to do it first, you can’t excel data to an  existing file. If you import the Excel file you just created with a date table marked as date table, and then add all of the other data you are interested in using, the DAX Date Time Functions will work.

Where should I model my Data, Excel Power Pivot or Power BI Desktop?

When I last wrote about where you should model your data, there was no work around to the DAX Time Date Calculation issue. That has changed with the August 20th release. If this is the only reason that you are choosing to model in Excel, I would think again. But if you want to migrate your model to a tabular analysis services solution at some point, you will need to model in Excel to be able to use the Visual Studio project which makes that process easy. I hope you found this helpful in deciding whether you should use Excel or Power Pivot. As Microsoft continues to release fixes, the reasons for choosing one tool or another change, and when they do, check back here as I am likely to blog about it.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

***UPDATE: Power BI has been updated to include the ability to mark a table as date table, which means that you can now join on integer based date keys. For more information see this post.

 

Aug 21 2015

Talking about the latest Power BI Update

Author: Ginger Grant • Discussion: No Comments

On August 20, Microsoft released the latest update to Power BI Desktop. There are some great new features, some of them little but handy like the ability to resize columns in the data view, and others are a bigger deal like the ability to import Power Pivot models. The data modeling feature which has me most intrigued is the ability to move columns from one table to another. That is something which is not a feature found in most other data mashup tools. Moving columns I think provides for a more forgiving environment, which may encourage people new to the topic to play around with it.

When to use Power BI Desktop or Excel

I’ve been writing a lot about Power BI recently and have received some questions on my blog, but I am thinking there may be some more, especially about items in the latest release. There are a lot of interesting tools in Power BI and Excel. Having two tools means there are reasons, based on your unique environments, you may wish to use one or the other. If you have questions about this decision process, or other Power BI questions, I hope that you can make time to ask them on August 31, 10:00 EDT when I’m going to be speaking at the next SQL PASS – Excel Business Intelligence Virtual Chapter meeting, when I will be giving a talk Power Pivot – the Gateway to Power BI. The SQL PASS organization, which I am a member of and I hope you are too, has a number of virtual chapters around all things data, including Excel Business Intelligence, which provide the SQL Community with great information and training resources on a number of different topics. Registration for this talk is free, and I hope you will be able to attend and ask any Power BI related questions you may have.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Aug 16 2015

Group Workspaces in Power BI and Licensing

Author: Ginger Grant • Discussion: No Comments

PowerBIWorkspaceWorking on data is often not something performed alone. There is no technical reason that not to do it all by yourself, it’s just that if there is only one person with all the answers it may make taking a day off now and again problematic. If using data sourced from anything other than an Analysis Services cube, as I talked about in a previous post, I highly recommend that you create your model in Excel Power Pivot and use Power BI on the Web to create your visualizations. When creating data models and visualizations in Power BI as part of a team, there are a few steps required to make sharing the files and visualizations with others possible.

OneDrive – The place to store Excel Files used in Power BI

People working to provide a data solution require an environment where multiple people collaborate on a single data model. If it is not a problem to replace the data model in Excel every time you make a change to it, and never want to update the data, continue to store the Excel file containing your data model locally and upload from a Local File. Using a local file also means new visualizations will need to be created when the model is changed too. If at some point, you find it necessary to refresh the data stored in Excel, the file needs to be stored on an OneDrive. It is possible to share an OneDrive with others, which means the owner is responsible for adding new people and the OneDrive is tied to that owner. Sharing a file will also not permit a team to develop reports and dashboards, as that information is stored separately within Power BI. I’ve provided an example scenario to explain Power BI team development within an organization.

Collaboration within Power BI with Group Workspaces

At Initech, two people are working with Power BI Pro to create visualizations, me and someone named Jason. If you live in the US, statistically speaking your odds of working with a Jason are quite high. Jason and I are working on a data model and a number of reports, and we both want to see what each other is doing, as well jointly modify the data model. These features are available from a Group Workspace. To create a Group Workspace, within Power BI, open up the menu item My Workspace, then click on the plus sign next to Create a Group. The pop-up form includes a space to name the group and add users by adding email addresses to include group members when the group is created. It is possible to add or subtract users later, but it is not possible to PowerBIWorkspaceWithAGroupcreate a workgroup with anyone who does not have the same company email suffix; i.e. everything after the @ must be the same for all users. The menu under My Workspace will then change to show the Group Workspace listed, as shown here where the Development Team workspace has been added. When the Development Team Group Workspace is selected, the Dashboards, Reports and Data Models listed are shared with everyone on the team. To add files, from within the Group Workspace click on Get Data button then Files to see a new OneDrive Location created with the name of the workspace, which I have called Development Team. The workspace OneDrive provides the ability to share the model and if with Power BI Pro, schedule automatic updates. Now Jason and I can collaboratively create reports and dashboards within Power BI as the reports created will be shown to everyone within the Group Workspace. Items created with My Workspace are not visible to others, so it is possible for either Jason or I to create reports or dashboards each other cannot see.

Licensing: The answer to Why Group Workspace Features Don’t Work

Generally speaking I don’t discuss licensing, as that is a topic best covered by Microsoft as the topic is complex and reminds me of a Jim Henson movie. All of the licensing information listed here came directly from Microsoft, which is why I have included a number of links to their pages. For teams which want to use Groups Workspaces, and don’t care about sharing files, then all that is required is a Power BI Pro license. With a free Power BI account, Group Workspaces are not available.PowerBI New OneDrive

Groups are part of Office 365, and are designed to work with Exchange, as a key part of a groups is to have group emails. For Example, at Initrode they hold licenses for Office 365 ProPlus, and have email hosted on premise. Office 365 ProPlus licenses do not include Exchange. People working at Introde with Power BI Pro Licenses, cannot add any files to the OneDrive Created with Group Workspaces, as with this kind of licensing combination it is not possible to do so. Clicking on the OneDrive Icon created for the Group Workspace, will open up a file location which does not allow any files to be added. Furthermore, it is not possible to access any other OneDrive you may have from within the Group Workspaces, which means that none of the files in this Group Workspace can ever be updated. To resolve this problem, Introde needs to purchase Exchange 365 licenses for all members of the Group Workspace who need to be able to edit files, even though they plan on continuing to use Initrode’s on premise email servers to create and receive mail.

If your company has purchased E3 Office 365 license and Power BI Pro, Group Workspaces will work with no additional purchase required. At this time there is no bundled Microsoft License which can be purchased which includes Power BI Pro. Power BI Pro must be purchased separately for everyone who needs to use it. All users in your organization do not have to have Power BI Pro. Using the free version, it is possible to access visualizations created by people who have Power BI Pro, including accessing the visualizations from your phone with the free mobile apps as long as they are not accessing any Power BI Pro features.

Let me know if you have found this information helpful. Group Workspaces are great tool when creating reports in a team environment, and I would be interested in reading comments from anyone else who is using them, or thinking about it.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Aug 06 2015

Using the Vertipaq Engine in Power BI

Author: Ginger Grant • Discussion: No Comments

Recently I was at a client talking about Power BI. They had some questions about where to load data from their source system. After all you can just load data using the Data tab, and create a Pivot table based on that. Why use Power Pivot? The best reason that I could give is because if you load data up into Power Pivot, you are then going to use the Vertipaq, which is also called the XVelocity engine. The xVelocity engine loads data into memory and provides data compression which will increase the amount of data that you can store within Excel.

Demonstrating How the xVelocity Engine Works in Power BI

Since about 1890 Missouri has been known as the “Show Me State”. Apparently, no one really knows why, but I’ve heard the expression “I am from Missouri so show me”. I’m assuming that everyone reading this is from Missouri, so to speak, which
missourimeans I need to be able to show you how the xVelocity engine works. To do that, I am using a sample data set I got from UCI’s Machine Learning Archive, which is a great place for getting machine learning samples. The data set I selected for this test is the Online News Popularity set, which can be found here. If you don’t feel like downloading anything, do the same thing with any other large text file. In the Online News Popularity file there are 65 columns and

39,644 rows. The csv file size is 16,518 KB. If I open up that file and save it in the Excel file format and do nothing else, the file size is 18,484 KB. Saving the csv file in Excel adds about 2 MB to the file size. This isn’t surprising, as Excel adds to the text when it is saved, which can be seen if you ever open up an Excel file in Notepad.

The xVelocity File Size Test

So far we have not tested the xVelocity Engine yet. To test the xVelocity engine, open up Excel, click on the Power Pivot tab, select Manage, which will open up Power Pivot. Within Power Pivot, click on the From Other Sources icon, and select Text File, then click on the Next button. Browse to the Online News Popularity.csv location, wait until the data loads, then click on the Finish button. The data will then be loaded into Power Pivot. Save everything and exit Excel. The file size for the Excel file with the data loaded into Power Pivot is 11,386 KB, which is a 39% reduction in space. In playing around with various files, I have noticed that you get the biggest reduction if you have few columns and many rows. This works the same way in Power BI. Now I know that the xVelocity Engine does more than just shrink files, but showing someone the decreased amount of memory usage just doesn’t have the same impact as file size. This is a simple experiment which shows the improvements made when using Power Pivot for Excel or Power BI, which I hope you find useful as well.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Moving from Power BI to Analysis Services Tabular

Update By: Ginger Grant
Date: August 2, 2015

Power BI is a great tool for creating data mashups and great visualizations. There are a lot of posts on how and why to use Power BI for these tasks, including mine. PowerBIDesktopModelAfter working with a number of clients on how to make implement Power BI in their data environments, I thought it might be helpful to talk about how Power BI works internally to provide guidance to determine how best to implement it. One of Power BI’s big strengths is the ability to bring data in from a lot of different sources and put them together into a single data model. Power Pivot in Excel and Power BI Desktop in the Relationship’s page both allow you to create data models. Power BI on the web, does not, as of this writing allow the ability to create data models from multiple sources. Power BI is able to handle a large amount of data to be stored within it using the Vertipaq engine which compresses the data and loads it in memory. How much data? Well that has to do with how much memory you have.

Having 8 GB of Memory on a 32 bit Operating System is Worthless

Recently I was working at a client who wanted to load five year’s worth of operating information into Power BI. He knew that Power BI needs a lot of RAM, so he requisitioned 8 GB of RAM for his laptop so he would be able to load this much data. However, his IT department had a policy of only installing 32 bit operating systems on the computers. When 64 bit operating systems first became available, often times the computer bios or the mother board wouldn’t support it. Until very recently, outside of developers, most people didn’t have a business reason for 64 bit operating system to do their job.

Power BI will use all the memory you have available. What is available? Here’s some math to explain how much memory is available. A 32 bit Windows operating system can assess 2 32 = 4GB of RAM, and you don’t even get all of that as the system uses some of it. This math applies if a 32 bit version of Office 2013 is installed onto a 64 bit version of the operating system. The client couldn’t load five years of operational data into Excel because he didn’t have enough addressable memory, since with a 32 bit operating system he had 4 GB of RAM he can use, period.

Reasons for Data Modeling in Excel Power Pivot

Power BI is a great tool, but it is not the only tool. If you want to load up years of data into your model, at some point, even if you have a 64 bit OS and 32 GB of RAM, eventually you will run out of memory. Well then what do you do?ImportPowerPivot If you have developed your data model in Excel, no problem you can upgrade it to Analysis Services Tabular. Visual studio has a tool to make migrating to Analysis Services Tabular easy to do. I find Power Pivot model to be one of the best ways to get started on creating a Tabular Model as you can test out your data modeling concepts very quickly. If you have developed your data model in Power BI Desktop, as of this writing there is no way to migrate directly to Analysis Services Tabular. If you are familiar with Power BI, you may be pleasantly surprised at how similar Analysis Service Tabular is. Along with improved performance accessing the data, there are a number of security features which are not available in Power BI in Analysis Services Tabular. With Power BI, you can create a corporate solution from a desktop application, which is one of the neatest things about Power BI.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur