Excel

Feb 14 2016

Why the Power Pivot Tab may be Missing in Excel 2016

Author: Ginger Grant • Discussion: 11 Comments

If you have recently upgraded to Excel 2016 and don’t have a Power Pivot tab available to you, there are a couple things you can do. You might want to go to the Developer tab and click on the COM Add-ins icon, which will display a list of the Add-ins available. If Power Pivot is available, selecting this options will allow you to use Power Pivot. But what if Power Pivot isn’t there? Well unlike Excel 2013, there is nothing for you to go download from Microsoft. Chances are if you don’t have the option listed in the COM Add-ins window, you are going to have to part with some additional cash to get the Power Pivot tab.

Business Analytics Features are no longer included in all Versions of Excel

Power Pivot is considered a Business Analytics feature, but What-if Analysis and Forecast Sheet are not. Seems to be an interesting definition of Analytics Features. If you want Power Pivot, you are going to have to pay for it. Here’s

Extracted from https://blogs.office.com/2015/09/18/new-ways-to-get-the-excel-business-analytics-features-you-need/

Extracted from https://blogs.office.com/2015/09/18/new-ways-to-get-the-excel-business-analytics-features-you-need/

a clip from Microsoft’s website intending to clear up what versions include Power Pivot. Looking at this graphic, this is no way lists all of the versions of Excel which Microsoft sells. What about Office 365 Enterprise E1? Surely you would get Power Pivot functionality with that right? No. How much more money is Power Pivot going to cost you? Well, if you have Office 365, you are paying $8 a month for the Office software, including Excel. There is no guarantee that spending more money will provide Power Pivot though. Office 365 ProPlus, which has Power Pivot, will run you $12 a month. If you have Office Small Business Premium, which runs $12.50 a month you won’t get Power Pivot. Check the version of Excel 2016 by going to File->Account then look at what is listed. If the version isn’t Office 365 Pro Plus or one of the other versions listed in the graphic, there will be no way to make Power Pivot appear.

Power BI: The tool for Desktop Data Analytics

Excel 2016 is the first version to be released after Power BI moved to it’s own application. While Excel received the visualizations of Power BI, Excel did not inherit all of the data modeling capabilities of Power BI. The difference in Power Pivot is the diagram now shows the directionality of the relationship through arrows, a far cry from Power BI’s data modeling capability. Even when it is available, Power View is turned off in Excel 2016, and the reason given for this is “The interactive visual experience provided by Power View is now available in Power BI Desktop”. This sounds like a clear drive to separate the two products. I understand the desire to separate the two products. Changing the licensing model where people don’t know if the version of Excel they have will do what they need it to do, I don’t think is a good way to get people to use Power BI.
Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Feb 09 2016

2016 Releases of SQL Server and Excel

Author: Ginger Grant • Discussion: No Comments

It’s been 2016 for over a month now, so hopefully you are still not changing 2016your 5 into a 6 still. Chances are you haven’t changed your software to reflect the new year yet. Office 2016 came out in September of 2015 and SQL Server 2016 hasn’t been released yet. It’s hard to keep up with all of the version changes that have come out, especially when you throw in Power BI which is has something new every month. If you know you are going to be upgrading to one or both of these versions, or want to learn more about SQL Server or Excel so you can decide if  is worth the upgrade effort, this week I might be able to help out. I am going to be talking about the 2016 version of SQL Server on Wednesday, February 10 at the Arizona SQL Server User Group meeting and then talk about the 2016 version of Excel on Thursday at the Excel BI SQL Pass Virtual Chapter. If you are not in Arizona right now, you are missing out as we are having Department of Tourism weather of 80 degrees. You can get back to me in August when I am melting in the 115 degree heat.

Polybase in SQL Server 2016

Since there are many new features to talk about in SQL Server 2016, I picked Polybase. As big data matures many places are looking to keep their structured data right where it is and create an HDFS cluster to store other data. Polybase allows SQL Server 2016 users to look at both all in one place.

Excel 2016

It’s been a while since September 22, 2015, the date Office 2016 was released , but I still know very few people who have upgraded. I’ve been to a few clients that hope to upgrade to Excel 2012 this year. In this session, I will show where things got moved and renamed, what’s new and what is on the deprecated list. If you don’t have 2016 installed yet, or if you do and wonder where Power Query went, please join me to hear all about it. Generally speaking, the Virtual Chapters are posted on Youtube, and when they are I will have a link available. Unfortunately for those who attended my last Excel BI Virtual Chapter Meeting, due to technical difficulties that recording is not available, but hopefully this time everything will work.  When the recording is available I will make sure a link it is available on my blog for those who can’t make it.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Jan 20 2016

I’m on TV!

Author: Ginger Grant • Discussion: No Comments

Old-fashioned four legged TV set isolatedI’m on TV! Not just any TV, Excel.TV, which unless you stream the internet on your TV set like I do, probably is on a computer monitor, but in my world it still counts. Please check out Episode 40 if you want to see a recording of the show where I talk about Power BI.

There is kind of an interesting story as to how I ended up being asked to be on the show. I was teaching a Power BI class, and Jordan Goldmeier b | t was talking it. Jordan started following me on twitter, and tweeted that he was in my class. I was really surprised and intimidated that an Excel MVP who has written a number of books on Excel was taking a class from me. My class was in the pre-Power BI desktop days, so I was going over the 4 Powers in Excel, Power Pivot, Power Query, Power View and Power Map. Jordan later told me that he learned something in my class. I was relieved. We’ve kept in contact since then, mainly via twitter, which is how I got asked to be on the show.

I was really impressed by Excel.TV and everything that Rick Grantham b | t , Szilvia Juhasz b | t and Jordan do to make the very professional, with graphics and sound effects. They asked me on to talk about Power BI. While on the ExcelTVshow, Rick asked me about the variety of things I have on my blog, and I got to thinking about it. Whether I use Power BI, Excel, Machine Learning, SSIS, SSAS or R, I am trying to do the same thing, make sense of the data and use the data to provide answers. You can call that data science or business analysis or business intelligence, but whatever the label or the tool, I think that really covers what I like to do.

I really enjoyed being interviewed, and I look forward to catching up with Rick and Jordan at the PASS Business Analytics Conference in May where we will all be speaking May 3-4. Maybe I’ll see you there too?

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Nov 22 2015

Update on SharePoint 2016 and Excel Services

Author: Ginger Grant • Discussion: No Comments

As I wrote about in September of this year, SharePoint 2016 will no longer contain the Power Pivot Gallery. That functionality would be included in Office Online Server, which wasn’t out yet. Later that month Office Online Server was released, and a lot of the questions regarding how Excel Reporting Services were going to be made available were known.

Office Online Server: The Location for Excel

For those of you unfamiliar with the Office Online Server, it isn’t a new product. It was released as Office Web Apps Server in 2012, and was designed to support creating and editing Office documents without having to load SharePointAndOfficethe applications to desktops. The server provides the capability for Office documents to be run via a browser, decreasing the need to update desktops. Most people continued to load office to workstations anyway. Microsoft continued to pound away at the application, which is meant to be the location to go to view Office Applications. Fast forward to 2016, and Microsoft decided that Office Online Server would be the place to go to view all Office documents, meaning they were removing Excel Services from SharePoint, as they already had a product that would do that.

Viewing Excel Reports via SharePoint 2016

Another preview of Office Online Server was released last week, meaning a lot of the details of how Excel Services will work is now known, and you can download Office Online Server yourself to try it out. Office Online Server is a completely separate application from SharePoint, and as a matter of fact it should not share space on a server with SharePoint installed. SharePoint 2016 is designed to work with the Office Online Server, so it will support viewing Excel documents. You will need to establish a trust relationship between the two servers to authenticate them via certificates, which is a lot easier than setting up Kerberos. Of course you can also set up SharePoint to view SQL Server Reporting Services reports as well so you can store all of your reports in one place.

What to do to Prepare for an Upgrade to SharePoint 2016

If your organization is looking to upgrade their versions of SharePoint 2016 and you want to view Excel Reports, go requisition another server for installing Office Online Server as you will need it to support viewing Excel or any other Office Document. SharePoint 2016 has been streamlined to manage files and their content, and is pushing other features to other products. One of the reasons for this is SharePoint has been modified to support content whether it be onsite or on the cloud. As part of Microsoft’s new realization that there are some people who aren’t going cloud, it merges some of the SharePoint Online functionality into the new release and is working on providing better support for SharePoint in the future.

So go ahead and upgrade to the latest SharePoint. You will still be able to access everything in Excel, not through SharePoint, with Office Online Server.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Nov 15 2015

Data Access and Self-Service Business Intelligence

Author: Ginger Grant • Discussion: 2 Comments

There is more to implementing Self-Service Business Intelligence Business Intelligence than getting new software like Power BI, mindsets and practices also need to change. The data teams in many companies formed their policies based on history with previous technologies. One of those policies that is fraught with contention is letting the users have access to the data in order to do their own analysis. The reasons for this are based on a story like this one.  Like many a data professional, I worked at a company where we gave a team of users access to the database in order for them to do analysis. It was a replicated database, as we didn’t want to impact production. As these analysts primary skill was marketing not SQL, they wrote a query that took all the resources so no one had the ability to do anything else with the database, and we were required to intervene and kill the query to make the database useful again. After that, we changed their access to only being able to use views created for them to prevent this from happening again. Variations of this story exist all over.

Data Access has changed and so has the need for a 64-bit OS

Self-Service BI is supposed to be a way for Analysts to answer ad-hoc questions from Management about the business. While data professionals certainly could and do answer these questions, at some point a DatabaseAccessfocus line is drawn. If the primary focus is to determine the best way to write a query or implement an appropriate indexing scheme, this person has a technical focus and not a business focus. People with a business focus probably should be the person who use data to drive decision making. While technical people can write reports very efficiently, given the continual requests for answers from the data, keeping up with what the business people want to do can be extremely difficult as the numbers of reports required in various formats can be overwhelming. Like the old argument that “You don’t need a 64-bit OS” have become obsolete, so have the reasons for not giving business users access to the data. Now is the time to give them access. If you only have a 32-bit operating system, you don’t have the memory needed to do much data analysis. Data Analyst need 64 bit OS and access to the data.

What kind of Access should Analyst Have?

Most Analyst use Excel, which has become the de-facto tool of choice for data analysis. One doesn’t need to have a working knowledge of the SQL language to analyze data, and the scenario referenced above still happens. Instead data should be provided in a manner which is easy to consume in a Pivot table, allowing users to select, sort and filter the data at will. Analysis Services cubes, whether they be tabular or multidimensional provided this capability. Using a cube in an excel spreadsheet has very little chance of ever crashing a server, so go ahead and grant access. Give analysts the tools they need to provide the answers they need. Create a collaborative environment to grant access and provide the analyst what they need. In this kind of environment true data based decision making can really happen.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Sep 06 2015

How 2016 Releases of SharePoint and Excel Impact Power BI

Author: Ginger Grant • Discussion: 8 Comments

There has been a lot of news recently from Microsoft on the upcoming changes to Power BI, Excel and SharePoint. Some of the changes may impact your decisions regarding report distribution.

SharePoint 2016 has no Power Pivot Gallery

Within SharePoint 2016,  Excel Services have been removed from the SharePoint Server.  The Excel Services didn’t go away, they were moved to the Office Online Server Preview, which means if you want to continue rendering Pivot Tables and Power View via the PowerPivot Gallery, you won’t be doing this from SharePoint. If you are wondering how you are going to be able to view Pivot Tables and Power View reports in Office Online Server Preview, you have a lot of company. Office Online Server Preview is not out yet. What Microsoft is saying right now with the Preview Release of SharePoint 2016 is Excel Services are not there, and if you want to use Excel Services, you will be waiting until there is a Preview of Office Online Server. If you are thinking of upgrading next year to the latest version of SharePoint, you will need to wait to see how to do that and still support Excel report distribution.

Decreased Excel Power

Excel2016 cchangesMicrosoft is eliminating the rampant use of the word “Power” when it comes to Excel tools.  I want to stress that none of the tools have been removed, just renamed. Power Query has been renamed “Get and Transform”. Power Map is now called 3D Map. Only two power words remain, Power View and Power Pivot and the use of the word Power has diminished with both of them. Power View is still there and called Power View but you have to add it to the ribbon to see it. The Data tab now contains the familiar Manage Data Model icon to open up the Power Pivot Window, which you will have to enable the add-in to make it work. The add-in is found in the Data Analysis Add-in group with no reference to Power. A number of new features have been added, especially when it comes to visualizations in Power View, which now has Sunburst, Waterfall, Histogram, Pareto, Box & Whisker and TreeMaps.

Forecasting is Back

Forecasting in Excel 2016I really liked the forecasting Feature in Power BI Office 365, which I wrote about when was disabled in December of 2014. Now it is back in Excel 2016. It works like it did before by looking at historical information in the past and using those trends to predict future values using variables to help you adjust the value for different conditions. This feature provides a simple way to view possible outcomes, which I think is going to be a very popular feature.

Power BI Decision Process

Next year’s releases are going to provide a lot of changes which may impact what your organization may elect to do in the future. A lot of these changes were rather predictable, such as Excel 2016 containing the new visualizations found in Power BI, but others such as the changes to SharePoint, may be a surprise. Since I do not work for Microsoft, I have to guess like everyone else what the changes will be, but I don’t think it is a big stretch to guess that Office Online Server may be cloud based. SharePoint 2016 product announcement describes the product as a Cloud-hybrid, and I am guessing the hybrid part may see your Excel documents on Office Online Server in the cloud. If this is a big deal to your organization, you may want to read the information about this really closely. I know I will and will be writing about them here.  Please feel free to subscribe to my blog to get the latest updates.

***For updates on this topic please see my more recent post Update on SharePoint 2016 and Excel Services

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

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 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