• Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About
Microsoft Data & AI

Power BI

Power BI and Creating Custom R Visualizations: Pre Getting Started / First Step Install List

2/1/2017

1 Comment

 
I was recently on VM (virtual mentoring) duty at Pragmatic Works (PW) and was asked to fix someone's custom R visualization in Power BI (PBI) Desktop.  Consequently, it was "Hello, R scripting!" for me, and of course, I wanted to apply all that I've learned in t-SQL, VB, C#, MDX and DAX.  First suggestion: Don't do that.  

Here is the skinny:  Before you try to create custom R visualizations in PBI, do these things:

1.  Download and install Microsoft R Open from  https://mran.revolutionanalytics.com/download/
      /or/
      
Download and install R for Windows from https://cloud.r-project.org/
2.  Download and install Microsoft R Client from https://msdn.microsoft.com/en-us/microsoft-r/r-client-install
3.  Download and install R Studio from https://www.rstudio.com/
4.  (Optional) Download and install R Tools for Visual Studio (preferred by some over R Studio for R development)
      
https://www.visualstudio.com/vs/rtvs/
      https://microsoft.github.io/RTVS-docs/
5.  Make sure you have the latest PBI Desktop version installed.  (Look in the lower right corner of the PBI Desktop software and if there is no informative message of an available update, you are all set.)
6.  Verify that PBI Desktop is set to use installed R components.  In PBI Desktop, go to File --> Options and Settings --> Options --> R Scripting.  The R home directory and Detected R IDE properties should be filled in appropriately.

Picture
You will find many tutorials already on line, so I'll just share what I learned about what I call "pre-getting started":
1.  You must have #1, #2 and #6 complete (above) for some of the R-powered visuals from the PBI Custom Visuals Gallery to even install without error in Power BI Desktop.
​

2.  When you are told in tutorials that "of course you have the package installed" or "you may have to install the package first", what this means is that you must open R Studio and install the R package before the R script in Power BI Desktop will run.  This is the first four lines in the screen print below.
Picture
With the necessary R applications installed on your workstation, and the R packages you intend to load in PBI installed in your R environment, you are now ready to begin.  
​
3.  I suggest strongly that you get your R visualization working correctly in R Studio (my preference) or R Tools for Visual Studio because PBI Desktop does not provide meaningful error messages.  

4.  Final tip: PBI R-powered visualizations aren't EXACTLY identical to what you will see in R Studio, especially if your 'x' or 'y' axis is a date or time data type.  You may need to work with text data types to get around this.

Note: Virtual mentoring at Pragmatic Works is a great way to get pay-as-you go consulting help from an industry leader in the MS BI tool stack (just saying!).
1 Comment

Fun with Power View II: Enhance your report with Pop Outs, Themes, and font changes

1/17/2016

0 Comments

 
Picture
Pops Outs
Screen real estate in a Power View report is at a premium, and one of the ways to get more in the space allotted is to create smaller scatter and pie charts and rely on their pop out feature.  Click the blue "up arrow" in the upper right corner.

Picture
Small detailed charts -->

Picture
Suddenly become readable -->

Picture
Pop in using the "down arrow" to return to original size -->

Themes
On the Power View ribbon, click the Themes button.  This is a simple and quick way to customize your report!
Picture
Font Changes
There are two ways to change the font in Power View: 
1.   Change the font of the entire Power View report.  Go to the Power View ribbon and select the Text Size menu option.
Picture
2.   Change the font of a single chart.  Go to the Design Ribbon and click the Text button
Picture
For more fun with Power View, see Fun with Power View I: Enhance your report with Tiles, Slicers, and a Play Axis.
0 Comments

Fun with Power View I: Enhance your report with Tiles, Slicers, and a Play Axis

1/17/2016

1 Comment

 
I am frequently asked, "How can i dress up my Power View report?"  In return, I end up sending an e-mail that I typed up the first time someone wanted formatting tips for Power View.  You can consider these "beginner tips", but hey, we all begin at some time!

​For a deeper dive into Power View, I recommend Brian Larson's book Visualizing Data with Microsoft Power View.  If you have a bit of initiative and want to have a little fun, just read below.


Tiles
These are a filtering option in Power View.  I think of tiles are visually appealing slicers.  Click onto a column in a Power View  data table.  From the Design Ribbon, click Tiles.  A second option is to add a tile directly in the  Power View Fields window. under Tile By. 
Picture
The result should be "tiles" running across the top of your table.  The tile "United States" is highlighted below.
Picture
If you have a tile attribute that is "unknown" or "n/a", use filters to remove it.  If you do not see a Filters bar in your Power View work space, go to the Power View ribbon and click on Filters Area.  Expand the Filters Area and click on Table.  All the column in your data table should now be visible.  Expand the column attribute you wish to filter and mark EVERYTHING BUT the offending tile code or description.  In the screen print below, every country but Australia will show up in the tile list.
Picture
Slicers
Slicers are a second way of filtering Power View.  The difference between slicers and tiles is visualization, and for me, the amount of screen real estate I can afford to give up for the cause.  Create a slicer by first creating a data table with a single attribute / column.  The new table is now an attribute list.
Picture
With the new data table selected, on the Design ribbon, click the Slicer button.
Picture
Now the Power View report has a slicer!  Note the blue boxes showing next to each attribute.
Picture
Play Axis
The short step-by-step version is as follows
1.    Add a Scatter chart to your Power View design area.
2.    Set your ‘x’ and ‘y’ axis values.
3.    Select your Details property to control what your bubble represents.
4.    Select your Size attribute to control the size of the bubbles.
5.    Generally select a month, quarter or year as your Play Axis.
6.    Click the <Play> button located to the lower left corner of the scatter chart and watch the buggles move systematically across the time element selected in the Play Axis.
Picture
Having a bunch of bubbles jump around haphazardly isn’t very informative, so you’ll want to pick values that gradually move over time.
Picture
For more fun with Power View, see Fun with Power View II: Enhance your report with Pop Outs, Themes, and font changes.
1 Comment

Excel 2013 Prof Ed Data Source Comparison

5/24/2015

0 Comments

 
Picture
I was asked by a client today which Power BI tools in Excel could connection to a multidimensional cube.  Microsoft is constantly adding new data sources and functionality -- especially in Power Query -- and past versions of Excel had some MultiD limitations.  However, today if you are running 2013 Professional Edition, you are in pretty good shape!  To keep this straight in my own mind, I, of course, created a table, but before you get to that, please be aware of a couple of items.

1.  Make sure you have the latest version of Power Query installed.  You can tell if are up-to-date when the <Update> button in your Power Query ribbon is disabled.

2. It is important to understand that Excel uses different drivers depending on what tool you use to connect.  For instance, a
 connection to a SQL Server Database through one of these paths …                 
               Excel à Data ribbon à From Database à SQL Server
               Excel à Data Ribbon à Connections à Add à Browse for More à New Source
… results in Excel using an OleDB driver.  You can see this when you edit the connection and take a look at the connection string.

Picture
A connection to SQL Server through Power Pivot results in Excel using a SQL Server Native Client 11 driver.
Picture
A connection to SQL Server through Power Query results in Excel using a dot NET driver.
Picture
Please keep this in mind as you review the following chart that was current as of June 15th, 2015.  Although you can access the same data source multiple ways, be aware of what is really happening in your connection string.
Picture
Picture
Picture
0 Comments

    Categories

    All
    Power BI
    R Scripting

    RSS Feed

    Tweets by @SqlServerSME
Powered by Create your own unique website with customizable templates.
  • Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About