Articles

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

 

 

 

 

Jul 26 2015

Why Power BI Desktop and Not Excel Power Pivot?

Author: Ginger Grant • Discussion: 5 Comments

As regular readers of my blog know, I spend a time working with Power BI. Since it was released Friday, July 24, 2015, it started me thinking about all of the people who have been spending time on the previous version which, I affectionately thought of as the 4 Powers in Excel plus web. Well since the Office 365 web version is going away on December 31, 2015, I thought of the many ways you could use the new Power BI with the Excel.

Power BI on the Web is a Full Featured Application

One thing to keep in mind is that the web component of Power BI is very powerful. If you are not interested in mashing data so much as reporting on data from one source, be that SQL Server or Sales Force, select the needed data into the Dataset section of Power BI, and start working on creating visualization reports and pinning sections of those reports to a dashboard. Please note, if you create a visualization on the web there is not currently a way to download it to the desktop, meaning reports created on the web, stay on the web and you cannot download them. There is no reason to use Power BI Desktop at all. That being said, the web version is a tool not the tool, so there are times when you may need to work with either Power BI Desktop or Excel and load those models into Power BI on the web.

When do I want to use Excel for my Data Modeling and Not Power BI?

If you have created a working data mashup in Excel Power Pivot, it makes sense to load that to the web directly and use it as is. It is not possible to bring in an Excel Power Pivot model into Power Pivot Desktop, because it doesn’t read the data in Power Pivot as a data source. If you have spreadsheets containing data it will read those in, but if you have a Power Pivot model which doesn’t use any linked tables, it won’t find the data. However, if the same excel spreadsheet is loaded to the Web Version of Power BI, the data model created will be found. If you have a model created, select Add Data in the Web version to add the model and use it to generate visualizations. Power View does not have all of the modeling types which Power BI now has, so if Tree Maps or Funnel charts should be displayed either web or the desktop version of Power BI will do it. If you need to update your model in Excel, make sure that you store the model within One Drive so that the Power Pivot updates you create will be automatically migrated to the web.

When to use Power BI Desktop?

If you have anything but Excel 2013, use Power BI Desktop. Power BI Desktop provides visualizations PowerBIDesktopvery similar to Power View, which are not available outside of Excel 2013. If you would like to create a new data mashup, start by using Power BI Desktop, because it has some new modeling features which you might want to take advantage of such as Many-to-Many and Cross Filter Direction. It handles role playing dimensions the exact same way that Excel Power Pivot does, meaning you can only have one active relationship at a time. The features you need for data modeling are all their, just moved around a bit, such as using DAX to create columns or measures. Power BI Desktops allows you to create data mashups from multiple data sources as well, allowing data to be modeled from as many sources as the model will tie together.

Favorite Power BI Features

After using the previous version of Power BI, I figured I would list the features I most like in the new version, which are other reasons I would give for switching to it from Excel.

  • Colors – Getting to pick whatever color I want for my charts is wonderful. I was so getting tired of blue.
  • Data Refresh – Getting the automatic Data Refresh to work in Office 365 was complicated. The new application for refreshing Personal Gateway is wonderful as it is so easy to use. The only thing I don’t like about it is the name as I don’t see why creating a connection to a server be Personal?
  • New Visualizations – Options are great and I love having more to select from.
  • Lack of dependency on Excel Versions – It can take a while, especially in large companies to get everyone upgraded to the latest version of Excel. Now that process doesn’t keep good visualizations from happening.
  • Price – At $9.99 a month or free if you don’t mind manually refreshing your data and don’t have much data, the cost decrease helps win the argument to move to it.
  • Separation from SharePoint – the underlying administration on the web version with SharePoint in Office 365 added a layer of complexity which thankfully has been removed.

I have a lot of other thoughts on Power BI, but this is getting pretty long, so I will save my thoughts for another posting. Since it doesn’t cost anything to get started, I highly encourage everyone to start playing around with Power BI and let me know what you think.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Feb 10 2015

Licensing of Power BI moves away from Office 365

Author: Ginger Grant • Discussion: No Comments

The gap between Power BI and Excel keeps on getting wider. As there is conflicting information about the Excel/Power BI break-up on various places on the internet, I wanted to clarify some of the common discussion questions.  One place where you can get definitive answers is in the new Licensing Information for Power BI which I linked in case you missed it. Unfortunately for those of us who have been paying the higher fees for Power BI, the price reduction to ten dollars isn’t immediate. Although I am disappointed, the non-immediate fee reduction makes sense, since the new-Excel-free-version of Power BI is still preview edition. No one outside the US is able to even try it yet. The new pricing will be available when the new product is available. This also gives people a chance to migrate their existing reports to the new version of Power BI. Another way of saying this is, going forward you don’t need Excel or the Four Powers – Power Query, Power Map, Power Pivot and Power View – anymore. The only thing you’ll need is the New Power BI.

No SharePoint needed for Power BI

Another thing that the licensing document makes clear, is when the new Power BI is released, the Office 365 version of SharePoint will not be required. To be even clearer, SharePoint will not be needed to use Power BI. There are several places online where I have read conflicting information regarding the need to have SharePoint. Let me clarify by quoting from Microsoft’s Licensing Information for Power BI page just to make it perfectly clear “Power BI service will become a standalone service and will no longer require SharePoint Online”. Since the current version of Power BI is using SharePoint, if this is the only reason you have Office 365 SharePoint, you can get rid of SharePoint, which will be an additional cost savings. How much will it cost? Talk to Microsoft Support as the details must be worked out with them.

Why Did Microsoft Change Power BI to not use Excel ?

While at SQL Saturday in Albuquerque, which was even better than my high expectations, I had a chance to talk to someone from Microsoft, and of course Power BI came up. I asked why he thought Microsoft moved away from Power BI? While not divulging anything that is covered under an NDA, he mentioned that there were a lot of people who would like to use the features of Power BI, but they didn’t have the right version of Excel within their organizations. Microsoft removed this barrier to adoption by moving to a non-Excel version. Excel also had a lot of features that weren’t needed for data visualization, and support for some of the current features was sort of confusing. I agreed with him. For example there are three different ways of creating a data connection, which is definitely confusing.

Scheduled Updates in Excel

For those people who like the Power BI Add-ins to Excel and want to stay with them, there is one big issue, scheduled updates. Only with Power BI deployed to the Office 365 Cloud SharePoint can you get scheduled updates from all the places which you might be retrieving data. For all those people who for a variety of issues didn’t want to go with Power BI deployed that way, there is now a solution. If you are looking to update Excel, Power Update is what you need. Kudos to Rob Collie for providing this solution as I have heard from a number of people that they had SharePoint and didn’t want to go to the cloud for Power Query updates or didn’t want to have to deploy SharePoint. Keeping up with Excel and Power BI is now a wider world than just Microsoft.  The one thing you can count on is things will always be changing.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Sep 16 2014

Power Map – No longer only BI

Author: Ginger Grant • Discussion: No Comments

Microsoft did something interesting with the licensing of Power Map, they changed their mind. Power Map was original released as part of Power BI a preview tool. Shortly after Power BI went live on February 10, Microsoft made this announcement. “On May 30, 2014 if you have the Power Map Preview installed, it will no longer work in any non-Office 365 subscription version of Excel.” You won’t see that text on their site, but I had it on a slide for a previous presentation I did on Power BI.  Time to update the slide as things have changed since SQL Saturday Detroit, and I have updated my presentation for Power BI SQL Server Saturday Denver.

Do you have a License for That?

The Preview for PowerMap was originally available for Excel 2013 and Excel 2013 for Office 365. Microsoft’s message that came out after PowerBI was officially released is translatable to unless you have Office 265 and pay for a Power BI license, no Power Map for you. Well, that folded like a bad poker hand. If you look now at the Power Map website it says this (I’ll quote it here to make sure I have the text if it changes again)

“If you have any subscription for Microsoft Office 365, you have access to Power Map for Excel as part of the self-service business intelligence tools.” Yea!  But what if you have Excel 2013 on prem? You can use the preview forever. What does that mean? No new features but Power Map won’t stop working. Here’s what the website says exactly “Although feature and performance enhancements for Power Map will continue to be released for the Office 365 subscription plans, there are currently no future plans to update the downloadable Power Map Preview.” Now you don’t have to have purchased the separate license for Power BI to get Power Maps. You can be all Powerful with Office 365 or Office 2013. (Note to Microsoft:Does everything have to have Power in Excel? I am starting to channel the Wizard of OZ)

New Power Map Features

On September 9th, 2014, Microsoft just released some new features for Power Map which they do about once a month. One of the cool new features is the ability to add your own maps for things like the inside of buildings. If you want that to work, you might need to check your configuration. I’ve included a copy of my Com Add-In Screen. If you can’t remember how to pull this up, check my previous post https://www.desertislesql.com/wordpress1/?p=178

PowerMapAddIn

You will notice there are two checkboxes here for Power Map on the COM Add-In Window because I was using the preview and Office 365 automatically gave me the non-Preview version of Power Map when I got an automatic Office 365 update. To get the cool new stuff, I must have the Microsoft Power Map for Excel checked, not the Preview one. If you didn’t pay for Power BI with Office 365, you won’t be able to use the new features Microsoft adds in every month.

Now if you have Excel 2013 or Office 365 and didn’t download the preview yet, you still can right here. http://www.microsoft.com/en-us/download/details.aspx?id=38395. Microsoft now calls the Power Map Preview an Unsupported Add-In, but all of the original features still work.

Mapping in Excel

If you have Excel 2013 or Office 365 and you don’t have Power Map Preview installed, you can still use mapping tools for Excel reports. How is this possible? By inserting maps into Power View.  The maps inside of Power View are very interactive, and unlike Power Maps, you can encode these reports as HTML 5 and display them on your phone or tablet.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Sep 15 2014

Powerful Stuff at SQL Saturday 331 – Denver

Author: Ginger Grant • Discussion: No Comments

SQLSaturday 

This will be the fourth SQL Saturday this year that I have honored enough to have been selected as a presenter. I know it is an honor, because I’ve also been turned down, which makes me work harder and keeps me humble.  If you decided today that hanging out at the University of Denver next Saturday is something you want to do, you are out of luck. There is now a waitlist to attend as SQL Saturday Denver is filled to capacity.  It is really awesome to think that they have so many people interested in learning about SQL Server that they are going to have to turn some away. It says something about the great job that Steve Wake, Windy Martin and friends are doing in publicizing this event too.

Power BI

I am going to presenting on Power BI. As a lot of people haven’t yet seen it in action, I will be demonstrating the features of it and explaining the rather complicated licensing issues around Power BI.  Power BI is mostly Excel, and I’ll be explaining what you can do in Excel 2013 without having to buy anything and if you do decide to buy PowerBI what you get.  Because there are some things which won’t really fit in my presentation, I’ll be posting a few things here on Power BI as well.

SQL Community

I don’t get out as much as I would like, and I certainly don’t get to visit the places where a lot of people who read my blog are, like Brazil. If you are attending SQL Saturday Denver please come by and introduce yourself.  I will give you 10,000 reasons to come to my presentation, none of which I am going to say here as it will ruin the surprise.  I am looking forward to hearing presentations from the other speakers too as there are some great topics being covered. I hope to see you there.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Jul 31 2014

Getting started with PowerBI in Office 365

Author: admin • Discussion: No Comments

When speaking at SQL Saturday #292 – Detroit, I received a question which I thought more people might have. How do I get started using Power BI or PowerPivot? I have Office 365 and I can’t get to the PowerPivot Menu and I don’t see Power Query either. What’s wrong?
I am assuming in that case your version of Excel looks like this.

image 1

 

Power BI was officially released on February 10th 2014. You might think that would mean that if you installed Office 365 after February 10th, which I did here, that Power BI would work out of the box. Sorry, that’s just not the case.

Turn on the Power

In order to get PowerPivot, Power Map, Power View or Power Query working you need to turn them on in Excel first. Click on the file tab in Excel and you’ll see this screen

image 2
 

 

 

 

 

 

 

 

 

 

 

Click on the Options menu item and you’ll see the following screen

image 3
Notice there are two sections to this window, Active Application Add-ins and Inactive Application Add-Ins. I’ve highlighted Power Pivot, which is listed under Inactive Application Add-Ins. This is why you don’t see it, it’s not active. Sounds reasonable, just click on the GO button and fix it right? No, wrong.

Type Tricky

The trick to adding add-ins is looking at the type. PowerPivot is type COM Add-in. If you click on the Go button you will see all of the Excel Add-ins, won’t turn on PowerPivot. When is an Excel Add-in not an Excel Add-in? When the Type says so. If you click on Go, this is what you will see

image 4

 

 

 

 

 

 

 

 

 

 

 

PowerPivot is a COM Add-in, so it isn’t on this menu. Cancel out of here and go back to the Add-In screen and change the drop down box to COM Add-in before clicking on the Go Button. This menu is what you need for getting 3 out of the 4 powers.
image 5

 

 

 

 

 

 

 

Go ahead and check the boxes next to PowerPivot, PowerMap and Power View like you see here. Click ok and then you’ll see the PowerPivot tab in Excel.

image 6

You still won’t see the Power Query Tab, because that has to be downloaded separately, which you can do for free here
https://www.microsoft.com/en-us/download/details.aspx?id=39379
Pick the one version you need, either the 64 or 32 bit version and follow the installation instructions, which means getting out of excel first as it will only install when it is not loaded.

image 7

 

 

 

 

 

 

 

 

 

 

 

After you finish the installation, go ahead and open Excel.

Power Signs

The Power Query tab is magically added, as the install takes care of adding the add-in for you.

image 8

 

When Excel looks like this, it’s time to get started with PowerBI!

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur