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.

Power Query – Controlling M Query Functions with User Driven Parameters

Have you ever had a user run a query against one of your largest tables only for them to immediately filter the results in Excel to show the last years worth of data? All of that data brought across your network and then immediately filtered out. Or maybe Excel just can handle the amount of unfiltered data they’re trying to return.

In this post I’d like to show you a way to solve this problem with Power Query in a solution that can dynamically filter your data returned based on user driven parameters.

In the scenario that I will demonstrate, rather then returning an entire result of all the company’s employees, I just want to return a list that show employees with a hire date within a certain range of dates that I or a user will provide. Using Power Query’s ability to make queries into Functions I’ll give my user the ability to provide the range of their choice

Connect to the Data

  • In my example I’m going use a SQL Server table as my data source but it could be any type of table. To connect to a SQL Server table from the Power Query tab in Excel select From Database > From SQL Server Database. For my example I’ll be using the AdventureWorksDW sample database.

image

  • Next you will be prompted to provide your Server and Database names where the table is located. Type these in then click OK.
  • Once you provide the Server and Database name you will also be prompted for the credentials you will use to access the data then click Connect.
  • The Navigator pane will appear showing all the available tables.  If you’re following my example with the AdventureWorksDW sample database then choose DimEmployee and then click Edit. This returns back the table to the Power Query Editor. **Note** This could be done on any table from any database.

Making the Query a Function

  • What I’d like to have my users do is return back this list of employees but only when the HireDate column falls within a range of values that they provide. To do this we’ll start by applying a hardcoded value to filter to the HireDate column. Find the HireDate column and apply a filter by clicking the down arrow next to the column and then Date Filters > Between

image

  • The range of values you filter on will depend on your table but for the DimEmployee table in AdventureWorksDW I used the following filter then clicked OK.

image

  • This simple places a filter on the query. If we want to make the filter dynamic we need to modify the M Query that’s behind the user interface. Go to the View tab on the Query Editor ribbon and select Advanced Editor. This will open the query window where you can modify

image

  • Next modify this query to add in a start date and end date parameter with the code in red below.

(startdate, enddate)=>
let
    Source = Sql.Database("localhost", "AdventureWorksDW2012"),
    dbo_DimEmployee = Source{[Schema="dbo",Item="DimEmployee"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimEmployee, each [HireDate] >= #date(2000, 1, 1) and [HireDate] <= #date(2002, 1, 1))
in
    #"Filtered Rows"

  • Once the parameters are created you can reference them in the query to replace the hardcoded value in the filter with a dynamic value from the parameters.  Modify the query with the code in red below then click Done.

(startdate, enddate)=>
let
    Source = Sql.Database("localhost", "AdventureWorksDW2012"),
    dbo_DimEmployee = Source{[Schema="dbo",Item="DimEmployee"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimEmployee, each [HireDate] >= #date(Date.Year(startdate), Date.Month(startdate), Date.Day(startdate)) and [HireDate] <= #date(Date.Year(enddate), Date.Month(enddate), Date.Day(enddate)))
in
    #"Filtered Rows"

  • This will convert the query into a function. You can test this function by clicking Invoke and then you will be prompted to provide date values to filter on.

image

image

  • If you did invoke the function make sure you remove the Invoke step before moving on. You can do this by clicking the delete icon in the Applied Steps pane.

image

  • This should return the query back to a function ready to be invoked. Now, go to the Home tab on the Query Editor ribbon and select Close & Load.
  • This will save the M Query function into the workbook but does not return any results yet.  Just how we want it! Our next step is to pass the values we want into the function.

image

Making it User Interactive

  • Go to a blank spreadsheet and create a simple Excel table that has a StartDate and Endate column with one row of values like this:

image

  • To make it so our users can type a value in this Excel Table and pass it into our function we need to take bring this small table into Power Query. Select one of the cells inside the table and on the Power Query tab select From Table. This will take the content of this table and bring it into the Power Query Editor.

image

  • To pass these two values into our function go to the Add Column tab and select Add Custom Column.
  • Write the following formula to connect the DimEmployee function created earlier to the dates we’ve now defined in the Excel table then click OK:

DimEmployee([StartDate],[EndDate])

image

  • If all your default settings are turned on inside Power Query you will probably get a privacy warning pop up.  This is because you’re working with two different data sources (1. SQL Server Table, 2. Excel Table) and there’s potential privacy concerns. In our scenario there are no legitimate privacy concerns so I’ll hit Continue. I also set the data sources to Organizational because the data sources should be contained within my company. Read more about Power Query privacy settings here.

image

  • Once the privacy settings are configured Power Query will add in a new column just called Custom (We could have renamed it previously). Click the Expand button next to the Custom column, uncheck Use original column name as prefix and then click OK. This will bring back all the rows that have hire dates between our date range.

image

  • Go ahead and remove the StartDate and EndDate columns from the query now but multi-selecting them and then right-click and select Remove Columns.
  • Next, rename the query in the Query Settings pane to Employee Data then click Close & Load on the Home tab.
  • You should now have two spreadsheets (It would obviously be a good idea to rename these) in your workbook.
    1. Sheet1 that has the Excel table with the date range values
    2. Sheet2 that has the results of the Power Query query. This data could have optionally been send to the Power Pivot Data Model
  • Now go back to Sheet1 and change the date range values in the StartDate and/or EndDate columns. After making this change the next time the Power Query query is refreshed it will pick up the results from the table to filter on saving your users from query a really large table when they only need a subset of the data.

Finishing Touches

If my users aren’t very familiar with Power Query and don’t know how to refresh their queries then we can build a quick little macro to provide a button that does it for them. Using a technique I learned from Ken Puls (blog | twitter) in a few short steps our macro will be done. In Ken’s post titled Refresh Power Query with VBA he shows how to refresh all Power Query queries in a workbook with a little VBA script.  I’m going to take the same principles he shows but just refresh the query I care about.

  • In your workbook press Alt + F11
  • Right-click on VBAProject(Book1), this may be named something different if you’ve saved with a new name, in the Project Explorer and select Insert > Module.

image

  • Use the following VBA script to refresh the workbook connection for our Employee query we created earlier (If you named your query something different you may need to adjust the section highlighted in red):

Public Sub UpdateEmployeeQuery()
‘ Macro to update my Power Query script(s)

Dim cn As WorkbookConnection

For Each cn In ThisWorkbook.Connections
If cn = "Power Query – Employee" Then cn.Refresh
Next cn
End Sub

  • Hit close on the VBA window.
  • To manually try the new code hit Alt + F8 and you will be prompted to run the script. Select the Macro we just created and click Run.

image

  • You should notice this kicks off the refresh of our Employee query.

image

image

  • Click somewhere in the worksheet that you want the button and then select the Macro we created to assign to the button. Click OK.

image

  • Click inside the button to rename it and then you’re all set!
  • Now all you have to do is change the values in the table and click the button to refresh the results of the query. This works if the results are rendered to an Excel spreadsheet or Power Pivot Data Model.

image 

As long as the data source and types of transforms support it Query Folding will still be utilized with this method. If you’re  curious about what Query folding is read more about it in this Matt Masson post.

I’ve made this example available if you would like to download it: User Driven Parameter Example.xlsx

Preparing a Technical Session Part 3: Writing an Abstract

So you’ve come up with a great idea for a session and now you have to translate that idea into a thorough abstract that helps both organizers and attendees understand what to expect from your session. This can make the difference between you getting a session at your favorite conference or not so don’t take this step lightly.

In this post I’ll walk you through some tips for writing a session abstract. Now there’s still no guaranteeing your session will get picked because there’s a lot of factors that go into a conference selection but I hope these tips will make your submission somewhat less stressful. Just as a reminder in this blog series on preparing a technical session I’ll cover the following steps:

  1. Picking a Topic
  2. Coming up with a Title
  3. Writing an Abstract
  4. Building the PowerPoint
  5. Building the Demos
  6. Delivering the Presentation

Writing the Abstract

Think of your session abstract as the appetizer to your meal. If you have a bad appetizer then you probably don’t have high hopes for the upcoming meal. The same is true about a session abstract. If an abstract is not put together well then it’s hard for the consumer (event organizers and attendees) to imagine a great session. Having said that let’s look at a few tips to make a great “appetizer” or abstract.

Grab my Attention

How is your session different than everyone else’s?  Why would someone reviewing hundreds of abstracts pick your session?  Do you think your topic is just that original?  In most large conferences you are bound to pick a topic that someone else has also submitted so what sets yours apart from the others?  Don’t let these questions haunt you but genuinely use them as a tool to ensure your session grabs the attention of the abstract reviewer.

Try to hook the reader in with the very first sentence.  This can be done by starting with a bold statement.  For example, you could say, “Stop struggling to tune your nightly data load when you could be running 60% faster”.  With a statement like that you’ve got my attention and I’m curious how you are going to accomplish such a feat.

Make the Audience Clear

It should be very clear who your session’s target audience is.  If you need to be straight forward and call out who the session is for in the abstract then do it!  You will likely have a large audience of unhappy people if the expectation was given that attendees would learn advanced query tuning strategies but your entire presentation is in Excel.  So help your audience understand that your session is for them.

Get to the Point

You don’t have much time to draw the reader in so don’t waste they’re valuable time explaining the details of what page latches are.  That’s what your session will do!  Instead of going through everything you’ll cover in the session do these things instead:

  1. State the problem (ideally in a way that hooks the reader in)
  2. Describe why it’s important or why the attendee should care
  3. Without getting into the details describe how you’re going to solve the problem

Be Specific

Yes, I know I just said don’t get into the details but that’s different than being specific. Giving the details would mean you write a 3 page abstract.  While, being specific means you’re clear on how you’re solving the problem. For example, don’t say you are going to create a BI solution to solve the problem.  Instead, say you’re going to create a BI solution that uses the following tools in this way.  You should also avoid acronyms that aren’t obvious to every attendee at the conference.

Explain the End Goal

Your abstract should tell the attendee what they will have learned after watching your talk.  For example, you may say “By the end of the session you will have learned how to load a Data Warehouse using SSIS”.  This gives attendees a clear idea of what they are getting by attending the session.  If I already know how to load a Data Warehouse then I know this session is not for me and I won’t be disappointed by it.

Read, Reread and Have Someone Else Read it

If you’ve decided to submit a session to a major conference I know you’re very passionate about the topic.  Unfortunately, the most common reason why sessions are not accepted is because despite your passion you don’t save time to review your abstract before submitting it.  Common spelling mistakes and grammar errors can cause what would likely be a great session unusable to a committee in charge on reviewing sessions.  So do yourself a favor and after you write an abstract read it, then reread it, and then have someone else read it because often we read a sentence how we want it to sound rather than what it actually says.

Other Good References

If you have other references please share them and I’ll add them to this list.

Preparing a Technical Session Part 2: Coming up with a Title

Earlier this month I began a blog series on preparing to do a technical talk. In that first post I discussed some strategies for coming up with a topic, which is ultimately the first step in your preparation.

In this post I’ll walk you through some tips for coming up with a title for your presentation. Just as a reminder in this blog series on preparing a technical session I’ll cover the following steps:

  1. Picking a Topic
  2. Coming up with a Title
  3. Writing an Abstract
  4. Building the PowerPoint
  5. Building the Demos
  6. Delivering the Presentation

Coming up with a Title

You may be thinking once you’ve come up with a topic you’ve got the title nailed down too but I actually see these are two different things. The topic is the general idea of what you’ll be talking about.  This gives you a guide one how you’ll work out the details. The title is one of those details. For example, my topic might be “Intro to SSIS” but I make the title “Getting Started with Integration Services”. This title is very clear on what the topic is and what depth the audience can expect.

Why is it important to have a good title? Well let’s be honest many people attending conferences probably only look at a small version of the schedule that doesn’t include the detail abstract so the title is all they have to go off. If your title is not clear on what you’ll be discussing then why would someone attend.  Let’s go a little deeper and look at some tips you may want to use when naming a session.

Don’t be too Cute

Do you consider yourself a creative person? Do you like to be unique and stand out in a pack? Good, now stop it! I’m kidding to some extent. You want your session to stand out and if the only thing people have to go by is the title than you may want it to be a little more creative or zany with your session title. There’s nothing wrong with this as long as it’s still very clear what your session is about. Here’s a couple good examples of being creative with your session title while still being clear and interesting:

  • Help! I’m a new DBA, Where do I start?!
  • DBA Mythbusters
  • Triggers: Born Evil or Misunderstood?

These sessions stand out but are still very clear what the talk will be about. On the opposite end of the spectrum here some session titles that are certainly unique but I have no idea what to expect if I were to attend.

  • SQL Server: We’re not in Kansas Anymore
  • Kill “BI”ll Quentin Tarantino Style

These sessions tried too hard to be cute and went past being unique to just being confusing.

Short and Sweet

I’ve often made the mistake of wanting to be so clear about my session that my title starts to look more like a poorly written paragraph. The intent is to eliminate confusion but what happens is without knowing it you begin talking people out of your session just by them reading the title. Here’s one I wish I could have back:

  • Using SQL Server 2014 to Build Analysis Service Multidimensional Cubes

Good topic, but a poorly written title. Way too long and complicated when it really didn’t need to be. If I could rewrite it I would simply name it “Building Analysis Services Cubes”. All of the other details that I decided to put in the title should have been saved for the abstract section. That way if someone was wondering which version of SQL Server I would be demonstrating then they could read the abstract to find out more.

Use Active Language

Using active language is a good method for making certain that your session topic is clear. This helps your potential audience know in many cases the kind of demos (if any) to expect. Words like “Building” or “Developing” tell your audience that they can expect demos. For example, if I went to a session titled “Developing Reporting Services Reports” I would expect demonstrations not PowerPoint slides that show me how to develop a reports. Here’s some examples of session using active language:

  • Building Dashboards with Your SalesForce Data
  • Overcoming Data Warehouse Design Challenges
  • Getting Started with Indexes

I don’t think it’s mandatory that you use this tip for every session you do but if you’re struggling for a title then this may help.

Getting Started with the Power BI Dashboards Public Preview

If you’re currently an Office 365 customer that is using Power BI sites then you probably found an early Christmas present this morning when you logged into your Power BI tenant.

Today Microsoft announced the Public Preview of Power BI Dashboards.  If you watched this PASS Summit Keynote (fast forward to the last 10 minutes) then you got an early glimpse of what the new tool will look like.

If you want to start using this preview today then here’s what you need:

  • Must be an Office 365 customer (Corrected)
  • Must have SharePoint online (Corrected)
  • Must have a Power BI tenant (Corrected)
  • Currently you must be a US customer

Getting Started

To get started you’ll first need to login to your Power BI tenant. Once you’ve logged in you’ll find in the top right the ability to launch the public preview by click “Try it now” as shown below.

image

Once this launches you may be prompted to sign in again. Once you’re in you will immediately see a sample dashboard called Retail Analysis Sample that gives you an idea of what you can do in the preview.

image

If you want to get started with your own dashboard you must first find the data you want to visualize.  Click Get Data on the left side of you screen to get started.

image

You can see the current data sources available to you on the left side of the screen.

image

Now if this seems like an incomplete list don’t worry. You actually have the ability to connect to any data source that you would typically pull in with Power Query by using the second option on the list called Power BI Designer File.

The Power BI Designer is a companion application for building Power BI Dashboards.  I’ll discuss more about this in a future post but for now just know that it’s a client tool that combines the capabilities of Power Query and Power View without the need for Office 2013 Professional Plus being installed.  Huge bonus for some eager to try Power BI but running non-Professional Plus versions of Excel.

Probably this simplest way for you to get started is by pointing the preview to an existing Power Pivot workbook you have. So in my example I’ll select Excel workbook and then click Connect.

I really love what you see next. My assumption would be that I would have to upload my workbook but fortunately that’s just one option.  You can also connect directly to your workbook if they are currently stored in either OneDrive or OneDrive for Business. I am a big OneDrive fan especially since Microsoft now allows Office 365 customers to have unlimited storage.

image

So I select OneDrive and after logging in to my account I see all my files available to select from. I pick the Power Pivot workbook I would like to build a dashboard on and then click Connect. Once connected you’ll be notified as shown below that you can start working on your dashboard.

image

You’ll find three groups of objects here: Datasets, Reports and Dashboards.

Datasets

Datasets are the connect to your model.  Selecting a dataset here will launch an empty Power View report with a connect to the dataset you selected.

Reports

These are basically like the new way of creating Power View reports. The development is completely done in the web browser and any visualizations you created here can be pinned as dashboard items. There’s a ton of new visualizations available here.  Again I’ll save that for a future blog post.

Dashboards

These are the completed dashboards that you’ve created.  Any Power View reporting object that you’ve pinned will appear here.

If you click on the new dataset that you just opened that will launch the new Power View reporting interface so you can explore your data. If you’ve worked in Power View before you’ll find there is not a huge learning gap for you. Here’s what the interface looks like.

image

You still select the fields you want to visualize but now the visualization options have been much improved.  Here’s a quick preview of a couple of the new types of visualizations

Funnel

image

Tree Map

image

Filled Map

image

Gauge

image

In addition to these you can also do combo charts!  Huge win with that one.  The dataset I used just didn’t have data to support showing that one to you.

If you want to pin one of these visualizations to a dashboard you simple hover over it and select pin to dashboard as shown below.

image

There are some things I really like about the new filtering capabilities but again I’ll save that for a future post. When your happy with the report click Save in the top ribbon and then you can access it again later.

Once you’ve saved you can return back to the main Power BI Dashboard pane and select the workbook that you uploaded under the Dashboard section.

When your dashboard opens you’ll notice two things are already present.  The first is Power BI Q&A is automatically available in every dashboard at the top.  The second is a connection to your workbook you uploaded is embedded into your dashboard.  This can be removed later but give you a quick way to launch Power View when you select it.

image

The way to create dashboard items is to create Power View reports and then pin the items (as shown earlier) you want to the dashboard. You can have different Power View reports all pin to a single dashboard.  Any items pinned on the dashboard can be selected to drillthrough to the Power View report it was based on. The Power View reports then serves as the detail view of the data and the dashboard is the high level overview of the data.

You can also pin the results of a Power BI Q&A natural language query.  If you use the Q&A search at the top of the dashboard you can pin the results to the dashboard as well.

image

This also allows you to pin visualizations of a simple number you want to monitor as shown below.

image

My final dashboard looks something like this. Using a combination of Power BI Q&A pinned results and Power View pinned visualizations.

image

Overall I love what I’m seeing with the new Power BI Dashboard capabilities and can’t wait to see how these reports are surfaced through things like mobile devices.

I’ll point out too I did find a few quirks here and there but just note it’s in preview still these things will be fixed before the official release I’m sure.

Follow

Get every new post delivered to your Inbox.

Join 42 other followers