Articles for the Month of July 2015

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

Jul 17 2015

Getting Started with R

Author: Ginger Grant • Discussion: 5 Comments

RAbout two years ago, because I kept on reading that R was the language for analyzing data, I signed up for an online class in R. I took the class for four weeks, and then I got busy and couldn’t finish it. Now maybe they would have gotten around to the good parts if I had stayed longer, but in those four weeks I had no idea why you would want to use R. The classes had various projects to load data up into memory and analyze it, which I did thinking all the while “I can do in Excel, so why is this cool?”. I didn’t see anything that showed my why R was the tool for analyzing data. A little while later, I heard someone talking about visualizing data in R. Now I saw why people were so excited about it. This video from Revolution Analytics, which Microsoft bought last year, shows some of the cool visualizations you can do with R. Since Microsoft is including R support in SQL Server 2016, now might be a good time to start learning it.

Starting R from a SQL Perspective

Since R support in SQL Server 2016 will be available in the preview for CTP3, now might a good time to start learning it. When I wrote this post, CTP3 was not available, which is why all of my samples are created in the open source free tool, R Studio (go ahead click on the link and download it then come back). The application generally uses four quadrants. RscreenThe top left contains the code editor, below that is the console where you see the results of the code you run. Like SSMS, you can run what you highlight, which may or may not be everything in the code editor. R Studio sort of reminds me of PowerShell, as R has good help and doesn’t always tell you if your command contains an error or not. The top right contains the workspace, including data which has been loaded, and the bottom right contains tabs for showing your graphics or the help files. R includes you data to play with to get started, just type data() to see the data sets, so you don’t have to go fish for data sets on the internet.

Visualizations in R

If this was the first example I saw with R, I would have better understood why people are paying more for R developers than C# developers as it doesn’t take much to get started to chart your data. You do have to load the libraries for what you want to load. I chose to load a selection of BMI data from gapminder for my sample. I have a simple text file which contains Country, year and BMI. First I am going to reference the libraries beeswarm and ggplot2, then load the data and assign it to a variable. After that, I will call the beeswarm function and have it plot the data, then provide a legend to see which country is which. After I have it loaded I am going to call the boxplot function to overlay the data. Not much code and a lot more satisfying than Hello World.

 install.packages("beeswarm")
 install.packages("ggplot2")
 library(beeswarm)
 library(ggplot2)
 setwd("D:/files/code/r/WorkingDir")
 BMIFemale<-read.csv("d:/Files/code/r/BMIfemaleCountriesSelected.csv")
 #View(BMIFemale) 
 beeswarm(BMIFemale$BMI ~ BMIFemale$Year
 ,data=BMIFemale, pch=20
 ,ylab='BMI', xlab='Year',
 ,pwcol=(Country)
 ,labels=c(BMIFemale$Year)
 )
 legend('topleft',legend =levels(BMIFemale$Country), title='Country',
 pch=20, col=1:50
 )
 boxplot(BMIFemale$BMI~BMIFemale$Year, data=BMIFemale, add=T
 , col="#0000ff18"
 )

Why should I use R ?

I hope this post provided a quick way of getting started with R. I will be devoting more space on this blog to provide other reasons why you might want to use R, especially when it comes to Machine Learning and explaining further R in relation to what you can do with SQL or other tools like Excel so you can better understand it’s place in the data environment. If you are interested in reading my upcoming posts on this topic, feel free to subscribe to my blog to get the updates.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Jul 09 2015

Datazen – The On-site BI Option

Author: Ginger Grant • Discussion: 2 Comments

I have talked to a number of people who looked at Power BI Preview and read that it will be replacing Power BI Office 365 as a cloud solution for mobile self-service Business Intelligence [BI] and thought what are we going to do? Some people are not going to go to the cloud. Since they have made that business decision, they were wondering what Microsoft was planning on offering in the non-cloud space? I was curious too, so I asked my local Microsoft Technical Sales Representative about what was the plan for people who wanted on-premises BI. The answer to that question is, use Datazen. Datazen is positioned to be the application for people who don’t want cloud but do want to create a rich self-service environment for doing self-service BI, which of course means that you can send the reports to tablets and phones.

Datazen vs Power BI Preview

When comparing the two products Datazen and Power BI Preview, there are a number of features which will sway people one direction or another.DataZenVisualizations  For example, Datazen doesn’t integrate with PowerPivot models, you have to flatten the model to connect. On the plus side, Datazen has a very robust security and distribution model which Power BI Preview does not. There are more visualization types in Datazen and the snap to grid makes using them a breeze. Power BI has the ability to connect with QuickBooks and other data sources such as Salesforce, which Datazen cannot do. Datazen is included with a current SQL Server maintenance agreement. Power BI will have an on-going per user monthly fee. Both have the ability to allow users to pick their own colors, rather than limit the selection to a series of color pallets, and both deploy to tablets and phones in addition to a web page. All of these factors, plus a whole lot more than I have space for here, weigh into the decision of which product you may select. If the foremost criteria for mobile Self-Service BI is No cloud, Microsoft’s solution in the self-service BI space is, use Datazen.

Future is Cloudy

Recently most of Microsoft’s new products have been very cloud based. Azure Machine Learning, is a great example of this, as no where can you find any document on Microsoft’s site about any plans to move that out of the cloud. The purchase of Datazen shows Microsoft is still accommodating people who just want to stay in house, which I missed in their announcement. I’ll be reading the upcoming releases a little more carefully from now on, as I am sure there will be many more.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Jul 04 2015

Designing Dashboards with Datazen

Author: Ginger Grant • Discussion: No Comments

CreateNewDataZenDashboard creation in Datazen uses the Publisher application which you can find here. My previous post walks through installing it in detail. When you open the Datazen Publisher Application, you will notice that it has a mobile interface, not a Windows interface, which means using it can be different from what you may expect. To create a dashboard, you will need to click the menu on the bottom on the screen. If there is no menu bar on the screen, right click anywhere and the menu bar will appear. There are two options available for you when you get started, either and empty dashboard or an existing dashboard, which provides a design template as shown on the picture of the screen.

Setting Up Excel as Data Source in DataZen

Your data can come locally from Excel or from the Datazen server. I picked Excel. The navigation is not the Windows-like navigation I am used to, as I had to click on the libraries to get a drop down menu to get to my data. The first spreadsheet I selected contained a Power Pivot Data Model. Datazen doesn’t appear to read Power Pivot Data Models, as it was unable to access the data. First I needed to modify my excel spreadsheet to create a flat pivot, and then Datazen can use that as a source. After I modified my Excel spreadsheet, then I was able to use that spreadsheet as a source in DataZen. Then I tried to create some KPIs. I found out that Excel cannot be used as a data source for KPIs in Datazen, unless you configure Datazen Server to use excel like a SQL Server Data source. Good to know, and hopefully you will read this before you decide to use Excel as a data source, which will save time.

About Datazen Visualizations

After all of your data sources are defined, it’s pretty easy to set up visualizations from the selections on the left. One thing that I really like about the Designer is the snap-to grid function, which makes laying out content much more organized. I wish other dashboard design tools I use had a grid. When visualization are selected, the settings for configuring it are on the bottom of the screen. The output can be designed for a phone or a tablet from the master view. This feature makes a lot of sense as the screen size changes the way the display looks and your ability to click on it. The maps are pretty nice out of the box. Here’s a time saving tip when creating maps in Datazen. Make sure that you spell out the state names when you want to use them. The visualizations you create will allow you to drill into other reports, similar to other reporting applications. It’s not really meant to be self-service BI, but a tool to present dashboards to business users and provide them the level of detail desired by creating deeper dive other reports. As it was designed for a mobile environment, touching the screen to interact with the visualizations means needing to think about finger size when creating phone applications so that interaction is possible by all users.

Try Datazen Yourself

I hope you found this post useful and take the time to explore the product yourself. I am sure Microsoft has a lot of plans for Datazen, one of which is backwards compatibility. Datazen now has a preview of the Publisher in Windows 7, along with the released product in Windows 8, which will allow more people to use it.  If you are exploring dashboarding tools, especially for a mobile deployment, this could be the tool you end up selecting.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur