The Next Generation of Training

Over the last five years, training and education have seen drastic changes in how course material is delivered. The reason is because many students simply cannot attend a training class that has a set start and end time and/or eats up twenty to thirty hours of their week. The reality for many of us is that work comes first, and it is impossible to be completely disconnected from our regular nine to five job to attend a training class.

With technology training, this is even more prevalent because of how frequently the technology itself changes. Being able to keep up has never been more difficult. So what do you do? Do you send your team to training over and over again and hope that work emergencies don’t constantly pull them away from an instructor led class?

The answer is self-paced training. That means attending training when it suits you and not when a company schedules it. It means being able to learn anywhere, including on tablets and phones, not just from a classroom.

The market for self-paced training is growing. There are many companies already providing this type of training and some are even available for free. So how do you know which to pick? How do I know that the company I choose will have what I need and keep their training updated? What if I really need to talk to someone with questions I have about the class or how I’m going to implement this in my organization?

Bottom line, do your research. You have to find the company and courses that work best with your needs and schedule. Often times, companies tout the number of courses they have but you will find out rather quickly that their classes leave you begging for more. These classes provide you nothing more than a cursory overview, something Pragmatic Works offers in our Free Training webinars, rather than the deep dive you really need.

For some people, on-demand training will never be the right fit. Some people learn best when they have a live instructor to talk to. If that is the case, there are several alternatives for you to consider. First, you can attend an in-person workshop. These courses are taught all around the world and often allow you to have a personal interaction with the instructor. Next, if you still like the idea of digital training but would just like a little help, then consider virtual training. Rather than leaving you on your own, virtual courses can provide guidance in the form of a virtual mentor. These classes give you the ability to learn from the comfort of your own office or home while still giving you the instructor interaction.

Here are a few things to remember when purchasing self-paced training:

  • Do your research. Make sure the package you select has everything you need and gives you the time needed to absorb the content.
  • Remember the benefits! You can learn when, where and how you want.
  • Self-paced training may not be right for you. There are plenty of great instructor-taught training courses out there.

Do you prefer in-person, virtual, or self-paced training? Let us know which you prefer and why in the comments below.

On-Demand Training

DirectQuery in Power BI Desktop

In the latest Power BI Desktop a new Preview features was released that now allows you to connect using DirectQuery to either SQL Server or Azure SQL Databases.  DirectQuery is a really neat feature that allows you to point to the live version of the data source rather than importing the data into a data model in Power BI Desktop. 

Normally when you want to get an updated dataset in the Power BI Desktop you would have to manually click the refresh button (this can be automated in the Power BI Service), which would initiate a full reimport of your data.  This refresh could take a variable amount of time depending on how much data your have.  For instance, if you’re refreshing a very large table you may be waiting quite a while to see the newly added data. 

With DirectQuery data imports are not required because you’re always looking at a live version of the data.  Let me show you how it works!

Turning on the DirectQuery Preview

Now, because DirectQuery is still in Preview you must first activate the feature by navigating to File->Options and settings->Options->Preview Features then check DirectQuery for SQL Server and Azure SQL Database

image

Once you click OK you may be prompted to restart the Power BI Desktop to utilize the feature.

Using DirectQuery in Power BI Desktop

Next make a connection either to an On-Premises SQL Server or Azure SQL database.

Go to the Home ribbon and select Get Data then SQL Server.

image

Provide your Server and Database names then click OK. ***Do not use a SQL statement.  It is not currently supported with DirectQuery***

image

From the Navigator pane choose the table(s) you would like to use.  I’m just going to pick the DimProduct table for this example and then click Load.  You could select Edit and that would launch the Query Editor where you could manipulate the extract.  This would allow you to add any business rules needed to the data before visualizing it.

image

Next you will be prompted to select what you want to connect to the data. Again, Import means the data will physically be imported into the data model and DirectQuery means you will retain a live connection to the data source.  Select DirectQuery then click OK.

image

With the connection established you can start to visualize the data.  I’ve just brought some fields into a simple table.

image

How can you prove this is a live connection? Go update the data!

I made a quick change to the first product listed and then clicked the Refresh button on the Home ribbon. Normally this refresh button would attempt to reimport data into the model but because we’re using DirectQuery it just refreshed the visualizations to show the new data in the live data source.

image

This means that there is not data model being used, just a connection to the live data.  There are some implications of the data modeling not being utilized here.  If you look at the screenshot above you’ll see there is no Data view, only a Relationship View.  That’s because there is no model for the data to be stored in.  It also means you cannot create calculations because there is no model for them to be stored in.  There are a few other limitation:

  • All tables must come from a single database
  • If the Query Editor query is overly complex an error will occur. To remedy the error you must: delete the problematic step in Query Editor, or Import the data instead of using DirectQuery
  • You cannot enter a SQL query when connecting to data
  • You cannot add calculated measures, columns, or tables
  • Relationship filtering is limited to a single direction, rather than both directions
  • You cannot change the data type of a column

These and other details can be found at the Power BI Support Knowledgebase here.

I think this is a really cool feature and I can’t wait for it to be officially part of the product!

Power BI Desktop: A Guide for Excel Users

Today Microsoft officially released the new development tool for Power BI called Power BI Desktop.  First of all let me share my excitement in what this release means for Power BI.  The team at Microsoft has done an incredible job and the results are something they can truly be proud of.

This new release presents a lot of questions for those who have built Power BI solutions in the past using Excel though.  So I thought it would be appropriate in this post to answer many of those questions as you begin exploring Power BI Desktop and compare it to Excel capabilities.

**Disclaimer: Information current as of 7/24/2015**

Overview

The good news is fundamentally what you’re doing with Power BI isn’t changing. You’re first going to find the data you want to import.  Then you may apply some business rules or transformations to the data.  Next, you’ll organize or model the data, which is an especially important step if you’re pulling from multiple data sources so they all relate properly to each other.  Last, you take what you’ve modeled and visualize it in reports that are presented to your users.

So if these steps haven’t changed then what has?  Well, the main changes have come with which tool you may choose to do your development in but there is also significant changes from the visualization side that are important to highlight. Let’s look at each of these steps: Importing, Transforms, Modeling and Visualizing your data and discuss how things have changed as you begin to develop in the Power BI Desktop application.

Importing Data

Excel

When working in Excel if you wanted to import data you had 3 possible ways to do this. This could be done with the Data, Power Pivot, or Power Query tabs in Excel.  You would choose each of these for different reasons.  The Data tab would be used to connect often to more enterprise data sources like Analysis Services cubes or Tabular models that have been prepared by IT.  The Power Pivot tab would be used to import data and begin modeling it with calculations, hierarchies, KPIs, etc..  Then the Power Query tab would not only import data but also allow you to apply business rules to the data through a built-in transformation language.  Many people have questioned whether or not to start with Power Query or Power Pivot to import data in Excel.

My guidance to you now would be to get in the habit of starting with Power Query if you’re not already because it presents you with many advantages from an overall Power BI perspective.  Consider things like additional data source options, the ability to share queries, and the continuing upgrades coming with Power Query as just a few reasons.

I’ll also mention that in Excel 2016 you will find Power Query under the Data tab going forward.

Power BI Desktop

In the Power BI Desktop application this story becomes much more simple. The one and only way to import data starts with Power Query.  By selecting Get Data in Power BI Desktop you’re presented with a very large (yet continuing to grow) list of data sources that you can use for importing data.

image

This is using Power Query as initial tool to connect to the data you need.  After you select the data you want you can decide if you want to bring it in as-is by selecting Load or if you need to apply business rules to the data first before importing you can select Edit.

image

Behind the scenes what this means is when you select Load the data immediately gets placed into an in-memory data model (aka Power Pivot).  If you select Edit then it will launch the Query Editor (aka Power Query).  Here you can continue to modify the data using the user interface or by writing your own M Query.

The one thing I’ll mention that’s currently missing from the Power BI Desktop for importing data is it’s currently missing the Data Catalog Search that Excel Power Query has.  This allows you to search public and organization queries.

Transform Data

Excel

In Excel if you want to apply business rules or transform data you will likely be using Power Query.  It’s the only tool that really allows you to modify the results of data as you import it in.  Tools like Power Pivot are all about importing first, completely read only, and then building on top of what you’ve imported.  While Power Query has an in-depth query language for modifying data during the import process.  This also can use features like Query Folding to run the final query against the data source and not on the client side.

Power BI Desktop

Really no big changes here in regards to the actual transformation process.  The Query Editor for Power Query in Excel looks essentially the same as it does in the Power BI Desktop.

image

The one thing I’ll note here is that it will be difficult to accomplish things like macro integration and creating user interactive workbooks like I’ve show here.

Modeling Data

Excel

Data modeling in Excel is done with the in-memory technology called Power Pivot.  Power Pivot has been available since Excel 2010 as a great way to do things like create relationships between currently unrelated items, create hierarchies as a way to navigate deeper into your data, create KPIs to analyze metrics in more in-depth, and many other features.

Power Pivot is still the core of what makes visualization work and work fast regardless of whether it’s Excel or Power BI Desktop.

Power BI Desktop

In the Power BI Desktop there’s still a lot of growing to do as far as creating data models.  I’m not too concerned about this though because as I’ve seen Power BI Desktop grow most recent investment has been placed on the data modeling side.  For example, up until the most recent release there was no data or diagram view like you have in Excel but with the public release of Power BI Desktop today you will now find these two features added.

image

You’re also seeing some things released to the desktop application before Excel.  For example, there are new DAX capabilities in the Power BI Desktop that aren’t available until Excel 2016.  In Power BI Desktop you now have the ability to do things like variables in DAX and there are new functions also available.  Here’s an example of the new DATEDIFF function:

image

There are even some new more advanced relationship properties to help deal with many-to-many relationships and bidirectional relationships that are currently only available in the desktop application. Chris Webb has a great post detailing this update here.

There’s still a lot of work to do here still to have full feature parity between Excel and the Power BI Desktop application.  Again this is as of 7/24/2015 and I anticipate many of these will be added as time goes on but here a quick list of some features that are currently not available to Power BI Desktop that are in Excel:

  • Perspectives
  • Hierarchies
  • Web URL Image support only (No database images yet)
  • Default Field Set (assigned default fields to a table)
  • Table Behavior
  • Summarize By (for changing the default aggregation behavior)

Some of these are more critical than others but for the most part you should be able to do what you need in the Power BI Desktop application when it comes to modeling now.

Now even though many of these properties don’t exist in Power BI Desktop that doesn’t mean you can’t import an Excel workbook to PowerBI.com that’s already turned on these features and use them with visualizations.

Visualizing Data

Excel

In Excel you have multiple options for how to visualize data once it’s imported.  You can do standard Excel PivotTables or PivotCharts, Apps for Office, Power View, or Power Map. Depending on what type of data you’re working with or what the desired final report needs to look like would determine which route you chose.  Geographical data would fit nicely into Power Map, Power View or even some Apps for Office visualizations.  Power View is great for exploring data and drilling deeper into your data.  They all have a purpose depending on what your needs are

Power BI Desktop

The Power BI Desktop uses exclusively Power View to visualize your data, but it’s a significantly overhauled version of Power View.  You’re now given new visualizations like:

  • Combo chart
  • Tree Map
  • Area Chart
  • Filled Map
  • Funnel Chart
  • Gauges
  • Donut Chart
  • Matrix

Here’s a few of the new visualizations

image

The interactive nature of Power View that you are used to in Excel still exists here and it is still dependent on a well defined model. So if you click on a bar in a bar chart the rest of the report will filter on that selection.

Not only are you seeing new amazing visualizations but it’s also using a framework that’s very extendable.  Meaning you can use custom visualizations that you create on your own or that someone else created.  Check out this Power BI Visual Playground on GitHub to see what’s possible.

You also have a quick and easy way to publish the results to your Power BI site with a simple click of the Publish button in the ribbon the results will be shared with others.

image

Now this has simply been just a comparison between what’s possible in the Power BI Desktop application versus Excel.  If you’re curious about the other Power BI features that were released as well you should read this blog from the Power BI team.

Excel Services in Power BI

**Information current as of 7/22/2015**

There’s lots of exciting news this week with Power BI so expect several short blog posts detailing each.  Here’s one you can try now that I was pretty excited to see.

So here’s the news.  You can now expose standard Excel workbooks as another asset in your reports library in Power BI.  By selecting your workbook a link is created that allows you to launch Excel Services visualizing the workbook.

This will allow you to visualize things like PivotTables, Charts, Slicers and Power View sheets in the same place as your Power BI Dashboards

Current Limitations

  • The workbook must be stored in OneDrive for Business, which of course not everyone has.
  • Doesn’t integrate with other Power BI visualizations.  I’d love to see this in the future.  PerformancePoint allowed visualizations from Excel Services and it’s own proprietary visualization to integrate together.  Would love to see that again here.  Currently it just launches Excel Services to the workbook

How it Works

  • If you haven’t already, you’ll need to sign up at PowerBI.com.
  • Once you’re signed up, you can sign into at PowerBI.com to get started.
  • To get started  select Get Data.

image

  • Select that you want to import data from the Files option.

image

  • Select OneDrive – Business.  Again OneDrive for Business is the only way to currently do this.

image 

  • Select the file you want to import and then click Connect.

image 

  • Next you’ll choose: do you want your workbook to be a data source for a Power BI dashboard or do you want to bring your Excel workbook in as an Excel Services report.  This blog is all about demoing the Excel Services component.  Select Connect, Manage and View Excel in Power BI.

image

  • This will take a few moments to process but once it does you will be taken back to the dashboard view of Power BI and you will find the Excel Workbook listed as a new asset in the Report folder.

image

  • When you select this you’re given several options including: View, Edit, Rename, Schedule Refresh, Refresh Now, and Remove
  • If you click View or Edit it will launch your workbook in Excel Services.

Very cool!  I’m hoping for even more integration like this very soon.  For example, I know everyone would be really excited to see Reporting Services integrated so we could have one repository for all our reporting assets.

Creating a Date Dimension with Power Query

A date dimension or table can be extremely important when working on a Power BI project, or BI projects in general for that mater. Here’s some of the quick benefits and reasons why you need a date table:

  • Helpful when filtering data
    • Filter by year, quarter, month, etc…
  • Helpful for drilling into a hierarchy of dates
    • Expand 2015 to see all the months within that year
    • Drill in to January to see all the days within that month
  • It’s required to do Time Intelligence functions in Power Pivot with DAX
    • Calculating year to date, prior period, etc…
  • It allows you to track important dates like holidays in a central spot.
    • Theses can be applied to filters
  • Allows you to track multiple types of dates
    • Calendar, fiscal, manufacturing, etc…

Just about any project will likely require one or many of these features. So if you’re working on a Power BI project what are your options?

  1. Import a date table that exist in your source already and use it
  2. Import a date table from the Azure Marketplace (I’ve used DateStream in the past successfully)
  3. Use Power Query and the M query language to create your own date table from scratch

For this post I’d like to share with you how to do option 3 using a script that I’ve created to generate a date dimension. Here’s the steps to replicate this table on your own.

Creating the Date Dimension

  • Launch Excel
  • Go to Power Query tab. If you haven’t downloaded Power Query already you can do so here.
  • Select From Other Sources > Blank Query. This will launch the Power Query Editor .
  • Select Advanced Editor in either the Home or View tab of the editor.
  • Remove any code that the editor is currently story and replace it with the following:
//Create Date Dimension
(StartDate as date, EndDate as date)=>

let
    //Capture the date range from the parameters
    StartDate = #date(Date.Year(StartDate), Date.Month(StartDate), 
    Date.Day(StartDate)),
    EndDate = #date(Date.Year(EndDate), Date.Month(EndDate), 
    Date.Day(EndDate)),

    //Get the number of dates that will be required for the table
    GetDateCount = Duration.Days(EndDate - StartDate),

    //Take the count of dates and turn it into a list of dates
    GetDateList = List.Dates(StartDate, GetDateCount, 
    #duration(1,0,0,0)),

    //Convert the list into a table
    DateListToTable = Table.FromList(GetDateList, 
    Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),

    //Create various date attributes from the date column
    //Add Year Column
    YearNumber = Table.AddColumn(DateListToTable, "Year", 
    each Date.Year([Date])),

    //Add Quarter Column
    QuarterNumber = Table.AddColumn(YearNumber , "Quarter", 
    each "Q" & Number.ToText(Date.QuarterOfYear([Date]))),

    //Add Week Number Column
    WeekNumber= Table.AddColumn(QuarterNumber , "Week Number", 
    each Date.WeekOfYear([Date])),

    //Add Month Number Column
    MonthNumber = Table.AddColumn(WeekNumber, "Month Number", 
    each Date.Month([Date])),

    //Add Month Name Column
    MonthName = Table.AddColumn(MonthNumber , "Month", 
    each Date.ToText([Date],"MMMM")),

    //Add Day of Week Column
    DayOfWeek = Table.AddColumn(MonthName , "Day of Week", 
    each Date.ToText([Date],"dddd"))

in
    DayOfWeek 

You’ll find documentation for this script embedded in the code to help guide you through what each expression is doing.

  • Click OK. This query is actually a function that accepts parameters so you will see that it’s waiting for you to invoke it with values.

image

  • Click Invoke and provide the range of dates that you would like the date table to return back. Then click OK

The results can now be integrated into your solution.  For example, you may add this to an existing Power Pivot data model by selecting Close & Load To.

I’ve seen many date tables with dozens of columns and a variety of types of dates.  I left this one fairly simple so you can add your own customizations to it.  Any thoughts on date fields that may be universally needed by others?  Let me know and I’ll work on adding it to the script.

Here’s a few other takes on solving this problem with Power Query:

Creating a SQL 2016 CTP2 Virtual Machine in Azure

With the CTP2 of SQL Server 2016 now released you may be eager to try it out.  One of the best ways to do this in my opinion is to set it up on a virtual machine.  You could certainly do this with VMWare or Hyper-V but my new preference for creating VMs is Azure.

The great thing about it is you don’t even have to go download the software locally because they already have a template in the Azure Virtual Machine Gallery ready to go for you.  Here’s the steps to create your own SQL Server 2016 CTP2 virtual machine.

  • Go to www.windowsazure.com and click Portal. Then either sign in with your existing account or sign up for a free trial.
  • Go to the Virtual Machines page and select New. If this is your first virtual machine you could also select Create a Virtual Machine in the middle of the screen.

image

  • Choose to create the virtual machine by selecting From Gallery.
  • In the gallery use the search bar to easily find the right VM.  I just searched 2016 and found the one I was looking for and then clicked the arrow to continue.

image

  • Provide the details needed for the Virtual machine configuration dialog.  You’ll need to come up with a name and login details for you VM. You’ll also be asked what size virtual machines you need.  This configures the resources and determines the cost to you. You can change the size at a later time if you’d like. Remember, outside of the storage space you only pay for the time that the virtual machine is actually on so if you boot it down when you’re done using it then you’re just paying for the cost of the virtual hard disk storage.
  • Next you’ll be prompted to create a cloud service and storage account for the VM. If you have previously created these you can assign this VM to an existing cloud service and storage account or create a brand new one here. Click the arrow to continue.

image

  • The last configuration screen will ask you about additional products you would like installed like antivirus an antimalware software. At the very least install the VM Agent, which is the default, and then click the check to create the virtual machine.
  • You should see the virtual machine start provisioning.

image

  • Once it’s complete you can launch the virtual machine by clicking Connect on the bottom of the screen. This will download a remote desktop (.rdp) file that once open will connect to your new Azure VM.
  • If you receive and error when opening this file it likely means that the VM is still provisioning.
  • Type in the username and password you provided when you created the VM to login.

Now that you’ve got SQL Server 2016 ready to go you can now try out many of the new features that are detailed here.

Power BI with Azure SQL Database

**Information is current as of 5/21/2015**

Recently Azure SQL Database was added as a new connection to the Power BI Preview. Read the notes about how it can be used here. The things I found especially worth noting were:

  • Every action sends a query back to the database. So you’re seeing the data real time from that database
  • Dashboard tiles are automatically updated every 15 minutes.  Meaning you don’t even have to schedule a refresh
  • Q&A is not available with this direct connect to the live data :(
  • The direct connection and refresh only works when building reports directly from PowerBI.com and does not work from the Power BI Designer

Keep in mind these things may change but that’s how it exists in preview now.  I thought I’d give it a try and walk you through a quick example of getting connected to a Azure SQL Database that’s been made available free to the community.

Getting Connected

  • If you haven’t already, you’ll need to sign up for the PowerBI preview at PowerBI.com
  • Once you’re signed up, you can sign into at PowerBI.com to get started
  • To create a refreshable data  source from an Azure SQL Database, select "Get Data"

image

  • Then select Azure SQL Database as your data source

image 

  • If you don’t have any Azure SQL Databases but would like to still test this out then use the one that SQLServerCentral.com has kindly provided with login details to the community for free here. This is the AdventureWorks database with several other supplemental tables added to it as well.
  • Connect to your Azure SQL Database by providing the Server, Database, User and Password then click Connect.
  • This will create a new Dataset to the AdvnetureWorks2012 Azure database and a new Dashboard (If you didn’t already have one selected) called Azure SQL Database

image

  • Select the dashboard and you will see a placeholder tile for the dataset.

image

  • Click the placeholder to explore the data or click the dataset and it will launch the report designer where you can begin building a new report.
  • If you’re using the sample provided by SQLServerCentral then you may be a little overwhelmed with all the tables listed to do my demonstration.  I used the Categories, Customers, Products and Order Details tables to produce a report that looks like this.

image

  • Once you’ve built a report make sure to Save it.
  • Once the report is saved you can pin a visualization to your dashboard by simply clicking the Pin to your dashboard button and it will appear on the dashboard.

image

  • If you return to the dashboard you will see any pinned items now appear there.

image

  • These will also appear on mobile devices that Power BI currently is available on.
Follow

Get every new post delivered to your Inbox.

Join 49 other followers