Oct 11 2015

Internet Promotion: A Way to Make Falling Trees Heard

Author: Ginger Grant • Discussion: 1 Comment
TreeFallsInTheWoodsIf a tree falls in a forest, and no one hears it, did it make a sound?

I can’t remember when I first heard this axiom, but it was sometimes when I was in elementary school. At the time I thought it was the silliest thing I had ever heard. After all, a tree falls regardless of my opinion or recognition. The tree is going to do what it is going to do and my interaction with it is meaningless. The sentence was one of those things, which is best described by Lewis Black as something which “…causes your brain to come to a screeching halt“. It made no sense to me, until later.

Why tell the world?

One of the reasons for this blog is pretty much the same as everyone else’s blog, namely, I wanted to write down what I know. So I got a high-speed internet connection (such as those available through optimum internet plans), created a blog, and started writing. There are a number of reasons for writing, to ensure I remember things, to find a link to something that works, to give back to those who have helped me by helping others or the desire to share something over the internet. The reason for informing the world varies. Displaying what your interest or expertise is can lead to opportunities where people ask you to do things where you have demonstrated your knowledge about a topic. As a member of a speaker selection committee, I know being able to find out about a speaker’s expertise made me recommend them or not.

Falling Trees Making a Sound

If someone gives a lot of talk and no one knows it, will they be asked to speak again? If you work with a real expert at a certain task and no one knows it, will this person be able to command a very high rate of pay at a future job? If your employer does something for you, such as sending you to a technical conference and you tell the world, are they more likely to send you to another in the future? While I am sure the answers to these questions can be universally answered by the consultant mantra “It depends“, for what it’s worth, perhaps letting the interwebs know will increase your chances of being asked or increase your value. If you are also one of those people who hate self-promotion, a blog may seem rather braggadocios. It’s also one of the few ways you have of telling people what you do.

The World is the Forest

It’s a big world out there. At some point, someone is going to ask you what you do. Sure you can practice an elevator speech. But when someone is sitting around surfing the internet and trying to find out about you, isn’t it best that you be the one who lets them know what you’ve done and what you know? Now, I don’t know if having a blog has helped me do anything I have done or not, but I continue to post. The world is a forest, and when I chop down a tree this is where I will make a sound.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Oct 06 2015

What is the difference between Machine Learning and Data Mining?

Author: Ginger Grant • Discussion: 1 Comment
An Example of Machine Learning: Google's Self-Driving Car

An Example of Machine Learning: Google’s Self-Driving Car

Often times when I give a talk about machine learning, I get a question about what is data mining and what is machine learning, which got me to thinking about the differences. Data mining has been implemented as a tool in databases for a while. SSIS even has a data mining task to run prediction queries on an SSAS data source. Machine Learning is commonly represented by Google’s self-driving car. After reading the article I linked about Google’s car or study the two disciplines, one can come to the understanding that they are not all that different. Both require the analysis of massive amounts of data to come to a conclusion. Google uses that information in the car to tell it to stop or go. In data mining, the software is used to identify patterns in data, which are used to classify the data into groups.

Data Mining is a subset of Machine Learning

There are four general categorizations of Machine Learning: Anomaly Detection, Clustering, Classification, and Regression. To determine the results, algorithms are run against data to find the patterns that the data contains. For data mining the algorithms tend to be more limited than machine learning. In essence all data mining is machine learning, but all machine learning is not data mining.

Goals of Machine Learning

There are some people who will argue that there is no difference between the two disciplines as the algorithms, such as Naïve Bayes or Decision trees are common to both as is the process to finding the answers. While I understand the argument, I tend to disagree. Machine learning is designed to give computers the ability to learn without specifically being programmed to do so, by extrapolating the large amounts of data which have been fed to it to come up with results which fit that pattern. The goal of machine learning is what differentiates it from data mining as it is designed to find meaning from the data based upon patterns identified in the process.

Deriving Meaning from the Data

As more and more data is gathered, the goal of turning data into information is being widely pursued. The tools to do this have greatly improved as well. Like Lotus 123, the tools that were initially used to create machine learning experiments bear little resemblance to the tools available today. As the science behind the study of data continues to improve, more and more people are taking advantage of the ability of new tools such as Azure Machine Learning to us data to answer all sorts of questions, from which customer is likely to leave aka Customer Churn or is it time to shut down a machine for maintenance. Whatever you chose to call it, it’s a fascinating topic, and one I plan on spending more time pursuing.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Sep 11 2015

Creating a Successful BI Project starts with Data Modeling

Author: Ginger Grant • Discussion: 2 Comments

Recently I was helping someone debug an Analysis Services Multidimensional project, and didn’t come up with much to help the performance. Why? The underlying data model was completely unwieldy and the fix, which no one wanted to do, was to redo it completely. Having worked recently with a number of business analysts to migrate there Excel spreadsheets to Power BI to support the growing trend to Self-Service Business Analysis, has made me think a lot about what makes a project a success.  Self-Service BI has been hyped as the way that analysis can better do their job and not involve technical resources.  While I support the move to the Analysts being more involved in with the data to make good decisions using the data, these kind of projects still need experienced data professions help them make a the project a success. There isn’t a tool which can fix a project with a bad data model. The problems the analyst have are not so much with learning the tool, as Power BI was designed to be easy to use. The problem is with data modeling.

Reporting Views; Modeling for the Moment

A lot of business reporting is developed by using the following process, which you may find where you work. The database team can’t keep up with the report requests, so they create a number of views and provide business analysis with some tool, be it Report Builder, Excel or Access to gather the data to do reports. This method provides the ability for analysts who don’t know much about data modeling to create reports based on the information is provided. This process works for a while. As long as the data people need to do their jobs is provided, reports are created and the Database team doesn’t have to be involved. This whole methodology starts blowing up over time. Why? The reporting time starts to increase.

The Reporting Time Explosion

Once I was working at a company where the person in charge of doing the performance reporting went on an extended medical leave, and trained someone else on what was required to get the data and create the reports. She gathered data from this system and that system, added in some information on a spread sheet, ran some macros did some queries, updated some Excel spreadsheets and after that the reports were generated. This process required three hours every day to do this complicated series of task and a full week for monthly reporting. It took all of about two days for her replacement to be overwhelmed, and the task of doing the reports came to me. After a week, I had gathered all of the data together for the daily reporting and automated it, which took the daily reporting process from three hours a day to seconds of computer time. It took a couple of stored procedures, some SSRS reports and a new process for storing the data not in an Excel Spreadsheet, but in the application where it was supposed to be entered. By the end of the second week, the monthly reporting was completed as well. A task which took the majority the time person spent her day, was automated to button clicks in less than two weeks. Why? The task of gathering the data was given to someone who understood databases and data modeling. That’s the knowledge that is needed to set up a successful BI Project.

Business Knowledge needs to be combined with Technical Knowledge

To be an expert at something takes time and focus. There are only so many hours in a day, and if you are focused on spending those hours on creating technical solutions, you are bound to get really good at applying technical knowledge gained to solving problems. Likewise, if you spend all of your day looking at the data trying to solve business problems and answer questions about how the decisions made impact the data, you are going to get really good at analyzing business data. Tools help provide the ability to answer questions, which can be answered because the data model supports the type of analysis needed. To figure that out, someone who knows about data modeling needs to be involved to ensure the Self Service business intelligence project has a good foundational data model.  If that’s not there, it doesn’t matter what the tool is, the project won’t be successful.

 

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