Free Marquee™ for Google Sheets Template demo

Microsoft Power BI isn’t just for getting data from Microsoft products. The PBIX demo file that you can get once you register below, allows you to query the data from your Google Sheet into Power BI and then share resulting reports and dashboards via PowerBI.com with co-workers or the world if you desire. If you have the Power BI mobile app, you now have Google Sheets data on the go.

Demo File Only

This PBIX  is provided as a demo only, with no support or warranty offered as a result. Testing was only sufficient for a demo and not for production use. You may encounter errors in your environment with the use of this model in it’s current state. You are welcome to expand the solution. If you do, please add to the comments below so that we can all share from your experience.

Note: the PBIX file only connects to the first tab in your Google Sheet.

Google Sheets API Oddities

This was an interesting project as Google Sheets doesn’t have the same concept of table as Excel does. Therefore, there’s two conditions you may encounter for which we don’t yet have a good solution.

First, you shouldn’t have a blank column heading. This will cause the model to error out on the last data transformations as Power BI expects column headings to be present.

Second, the Google Sheets API doesn’t appear to return cells that are null that are in the last column of your sheet. Since the cells are returned as a list and we fold the list every X rows, this throws off the row count and fold points. As a workaround, we recommend having the last column of data have values in all cells.

Send me the data model!

 

Setup

You need three pieces of data in order to use this PBIX file.

  • The number of columns in the Sheet
  • Your Spreadsheet ID
  • Your Browser API Key

Steps to get your SpreadsheetID

  • Navigate to your Sheets page.
  • The key is in the URL, see the bolded red text below.
    • https://docs.google.com/spreadsheets/d/1gLzc8AxdlUl1MPY4t2ATKjc1UfBNj7iUaHRexwLYSKQ/edit#gid=0.

Steps to get your Browser API Key

  • Log into your Google account
  • Click this link to start the wizard.
  • Click Continue button to create a new project.
  • Unfortunately, you have to create the following, even though we won’t use it.
  • Click Go to credentials button.
  • Select Web Browser (Javascript) in the Where will you be calling the API from?
  • Select User Data under What data will you be accessing?
  • Click What credentials do I need? button
  • Click Create client ID button
  • Enter a Product name
  • Click Continue button.
  • Click Done button.
Now to create the credential we need for this to work.
  • Click the Create credentials button.
  • Select API key.
  • Select Browser key.
  • Give it a name and click the Create button.
  • Copy the API key and paste it into the BrowserAPIKey parameter.

Setting Up Your PBIX File for Use

Once you receive your PBIT file, do the following.
  • You must have Power BI Desktop installed prior to performing this procedure.
  • In File Explorer, double-click on the Google Spreadsheet Template – Final.pbit file.
  • Power BI Desktop will open and you will be presented with this dialog.
  • Fill in the values and click the OK button.
  • The model will refresh and it should load your Google data.

Setting Up Scheduled Refresh on PowerBI.com

Once you have saved the model, verified the data and built your reports, you can publish this model to PowerBI.com. Once there, you can set it up to automatically refresh the data so that any reports and dashboards are up to date.

Procedure for Scheduled Refresh

  • In Power BI Desktop, click File, Save to save the model
  • Click Publish
  • If you aren’t signed into Power BI, you’ll be prompted to do so.
  • You may be prompted for the location to publish. My Workspace is the default
  • Once done, go to PowerBI.com.
  • Follow the procedure in the video below.
  • Navigate to the Datasets in the left navigation to start the process.
  • Note, the API key you entered earlier in the model is your login. This is why it is set to anonymous in PowerBI.com.
Send me the data model!

The Truth Shall Make You Miserable

Lack of Faith - Vader- Project Dashboards

When companies begin making their data more accessible via Self-Serve Power BI, they soon reach a critical break point in those efforts. The Project dashboards tell them something that isn’t pleasant or doesn’t match the narrative been publicized.

The Reality in Your Project Dashboards

Performance indicators go red. The data shows the stellar progress that was planned isn’t happening. Operational demands for time are much higher in reality than assumed in planning. In short, it shows the harsh reality, as captured in the data.

This is a moment of truth for organizations. Are we going to embrace the transparency or will we attempt to control the narrative?

Data Quality Challenges

The first question is normally, is this data accurate? This is quite reasonable to ask, especially at the beginning the data stream may not be as clean as it should be.

The approach to this answer can decide your success going forward. For some, questioning the data is a prelude to dismissing the use of the data. For others, it’s a starting point for improvement.

The data deniers will provide many reasons why “we can’t use the data.” They will complain that the data is inaccurate or incomplete. Therefore, they can’t trust their data to integrate its use into their daily work or to use it to make decisions.

These data deniers may have other hidden reasons for their position, such as political or power base protection reasons. Moving to data-centric culture is a big change for many organizations, as you have to be open about your failures. No company is always above average in every endeavor.

Data deniers also fear how business intelligence might impact their careers. If the corporate culture is such where punishment is meted out when the numbers and updates aren’t desirable, likely data transparency won’t be welcome.

Change the Focus of How Data is Used to Succeed

The key to overcoming the data fear is to change the intent for its use, moving the focus from punishment to improvement.

For the successful companies using data, they embrace two simple facts. One, the data is never perfect and that it doesn’t have to be to effect a positive change. Two, they’ve defined the level of granularity needed in the data to be used successfully.

How Imprecise Data is Changing the World

We see this approach in our personal lives. For example, the Fitbit device is not 100% accurate or precise. Yet, millions are changing their behavior of being more active because of the feedback that it provides. based on relatively decent data. You may also be carrying a smart phone, which also tracks your steps. Between the two, you would have a generally good idea of how many steps you took today.

From a granularity approach, we aren’t generally worried about whether I took 4103 steps or 4107 steps today. We took 4100 steps. Hundreds is our minimum granularity. It could easily be at the thousands level, as long as that granularity meets your information needs.

Cost Benefit of a Minimum Level of Granularity

One area we see this type of data accuracy dispute in the corporate world is with cost data. It’s been engrained in our psyche that we have to balance to the penny. Our default data granularity is set to the cent.

While that may improve accuracy and precision, it doesn’t make a material difference in the impact. For example, if your average project budget is $2M, then worrying about a 5 cent variance is a percentage variance of 0.0000025%. I’ve seen organizations who get wrapped up in balancing to the penny and waste an inordinate amount of time each week getting there.

Instead, let’s define a minimum granularity in the data such that a 1% variance is visible. For a $2M average, you would round up at the $10,000 point. Doing so then reduces work attempting to make the data perfect. Any variances of that size are significant enough to warrant attention and are more likely to stand out.

Implementing Self-Server BI using products like Microsoft Power BI and Marquee™ Project Dashboards will enable your organization to gain great improvements as long as they are willing to accept the assumptions above. The truth may make you miserable in the short term as you address underlying data and process challenges. In the long run, you and your company will be better served.

Please share your experiences in the comments below.

Death to the “Perfect Report”

Crumpled paper Power BI

When designing your Power BI solution, are you designing your solution for maximum long term effectiveness? One of the biggest mind shifts that my Power BI students have to make when beginning their Power BI journey is to stop thinking in terms of reports when approaching their Business Intelligence (BI) needs. Rather, it’s more effective to approach those BI needs from a holistic data model perspective.

Why the “perfect report?”

In years past, designers were focused on writing the “perfect report” as the amount of time and effort needed to create a report was substantial. This led to reports that were used for multiple purposes. This multi-use design also created complexity in the presentation and increased maintenance costs. Each additional report also creates a long tail of maintenance effort to ensure that the report stays relevant over time.

With tools like Power BI, the focus is on rapid development of new reports and dashboards. The “perfect report” approach is no longer necessary as it is very easy to create new single focus reports and dashboards. The value of a single report is then diminished and the importance of the data model design increases.

Think data models, not reports

The rationale for this design approach is that, on average, 50% of your annual reporting needs are ad hoc in nature. Therefore, you can’t design a report for every possible contingency. Having a 200 reports that don’t meet your immediate need creates noise rather than benefit to the organization. They simply slow down the ad hoc reporting process as the data consumer wastes time evaluating the existing content first. Flexibility offered from a well-designed data model is the expedient path to fulfilling your ad hoc needs effectively for the long term.

Power BI design for the business conversation

The purpose of business intelligence is to support business conversations with relevant data to enable decision making. Therefore, your data model should be designed to support a specific business scenario. The scenario provides the necessary context for scoping the type of data required to meet the most likely questions. It also provides the necessary context to the data consumer to identify the data model as the correct one for use in a given business conversation.

Conversation-centric design

The path to outlining the necessary data is using an approach we at Tumble Road call “Conversation-Centric Design.”

The first step is to identify the conversation to be supported. Standing meetings are usually a good starting point for the identification process. Meetings provide a lot of information for the design process as the timing, audience and typical agenda are known. The agenda can be used to identify the key questions and needed answers. From the answers, we can extract the core data elements for the data model. Each answer becomes a visualization in the eventual dashboard or report.

For example, there’s a weekly Vice President status meeting every Wednesday. Project status of the overall portfolio is reviewed during the meeting. The key questions for the portfolio review are:

  • Which projects have implementation milestones due this month
  • Which projects have resource constraint issues
  • Which projects have escalated issues to the meeting

Each of these questions can be broken down into the data elements necessary to provide the needed answers. This forms the core data for the data model. The designer then adds the most likely secondary data, like custom fields and other key data to anticipate future needs.

Designing for the long term

There are three aspects to consider to support your long term Business Intelligence needs.

Reports are still needed but are not the focal point

First, you should still provide reports over the data, understanding that they provide an immediate starting point for the data consumer to use the data. The report represents the designer’s view of the data and is generally designed for a specific conversation need.

Ultimately, the data consumer is in control

Second, a well-designed data model supports the data consumer needs for ad hoc information. The data consumer can create ad hoc constructs of data using two methods within Power BI.

The data consumer can create personal dashboards, pinning visualizations from multiple reports to create a personalized view of the data to meet their specific needs. This pinning process supports a visualization being pinned to multiple personal dashboards so the data consumer can narrowly define their dashboards.

If you are not reading this post on TumbleRoad.com, please go here to enjoy this post at its original source.

The data consumer can also create new reports in Powerbi.com via the browser.  The primary difference between the report and the dashboard is intent. Dashboards provide a view of the data; reports provide a way to explore the data. Data consumers will create reports when the need to slice and dice the data is the primary need. The effectiveness of their report development is dependent on the underlying data model adequately supporting the business scenario.

Business scenario focus means smaller, easier to use data models

Lastly, there is value in the less is more approach. Data models shouldn’t be monolithic in design. A well-designed data model provides only the data frames and data elements necessary to support the business scenario.

For example, one of the challenges of Project reporting is the sheer magnitude of available data. Overwhelming the data consumer with too many data frames (aka data tables) to search through for the requisite data elements, slows down the ad hoc reporting process and creates user frustration. In our Marquee™ designs, for example, we separate project reporting from timesheet reporting as separate business scenarios. This in turn reduces data consumer confusion.

A business scenario focused data model also reduces the risk of the data consumer inadvertently creating cross-join situations. When a cross-join arises, typically the data consumer has combined two data elements together in a visualization that don’t have a direct data relationship. The result is the data consumer seeing every potential combination of the two data elements. This may then lead to you receiving a support call as to why the tool is doing what it is doing.

Finally, keeping individual data models business scenario focused also enables you to better maintain the data model as the business scenario changes over time.

The wrap up

My hope is that you now understand why a data model approach is better than a report centric approach is superior when designing Power BI. Data model centric design approaches yield better long term support of ad hoc reporting. Focusing the data model on the business conversation also yields many benefits from a data consumer experience, from ability to correctly select the right model to improved speed of creating new reports.

How are you approaching your Power BI design today? What questions do you have? Please post your feedback in the comments below. I look forward to hearing about your design approaches.

[tagline_box backgroundcolor=”#5083bb” shadow=”no” shadowopacity=”0.1″ border=”1px” bordercolor=”” highlightposition=”top” content_alignment=”left” link=”http://eepurl.com/b6gtJ1″ linktarget=”_blank” modal=”” button_size=”” button_shape=”” button_type=”” buttoncolor=”orange” button=”Send me the white paper!” title=”Get our Business Intelligence white paper!” description=”Find out the 3 hidden reasons keeping you from effectively using Business Intelligence.” margin_top=”” margin_bottom=”” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ animation_offset=”” class=”” id=””][/tagline_box]

[tagline_box backgroundcolor=”#508ebb” shadow=”no” shadowopacity=”0.1″ border=”1px” bordercolor=”” highlightposition=”top” content_alignment=”left” link=”http://academy.tumbleroad.com” linktarget=”_blank” modal=”” button_size=”” button_shape=”” button_type=”” buttoncolor=”orange” button=”Click to Register” title=”Are you ready for the changes that Power BI is bringing to your reporting?” description=”Join our classes at the Tumble Road Academy, where we’ll teach you how to use the tools and then go into implementation best practices and organizational change management topics. Our lessons will save you time and headache!” margin_top=”” margin_bottom=”” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ animation_offset=”” class=”” id=””][/tagline_box]

 

 

 

Microsoft Project Online, Power BI and Cortana, better together!

Cortana with Power BI

[fullwidth background_color=”” background_image=”” background_parallax=”none” enable_mobile=”no” parallax_speed=”0.3″ background_repeat=”no-repeat” background_position=”left top” video_url=”” video_aspect_ratio=”16:9″ video_webm=”” video_mp4=”” video_ogv=”” video_preview_image=”” overlay_color=”” overlay_opacity=”0.5″ video_mute=”yes” video_loop=”yes” fade=”no” border_size=”0px” border_color=”” border_style=”solid” padding_top=”20″ padding_bottom=”20″ padding_left=”20px” padding_right=”” hundred_percent=”no” equal_height_columns=”no” hide_on_mobile=”no” menu_anchor=”” class=”” id=””]

The Tumble Road Tribe has been hard at work, pursuing that dream that we should be able to ask our computers questions and get reasonable answers quickly. This post shows some of our recent work, integrating Microsoft Project Online with Power BI and Cortana to create a seamless experience across the products. The video demonstrates this integration, using a very common scenario for anyone who manages a team.

This particular demo is part of a larger, upcoming announcement. Stay tuned for more!

[/fullwidth]

Use metadata to drive Microsoft Project reporting logic

The need to extract Microsoft Project Task level data in an efficient manner is growing as many Project Server and Project Online clients are creating Power BI models over this data. Unfortunately, many did not account for this BI need when creating their project template structures. This leads to Project template designs that make it difficult or impossible to extract usable data from the Project Server/Online data store.

Microsoft Project Task names should not drive meaning outside of the project team

One common issue is making the task names in your project template meaningful to needs outside of the project team. You might have standard task names for Finance or for the PMO for example.

If you have told your PMs that they cannot rename or add tasks to their plans, you have this issue. You have encoded information into the structure of the project plan. The issue is that this way of encoding makes it very difficult to extract data easily using tools like SSRS and Power BI.

We’ve seen this before, when Content Management Systems were new

This was a common problem early on in file systems and SharePoint implementations in the 90s and 00s. A few of you may remember when we had to adhere to these arcane file naming conventions so that we could find the “right” file.

For example, you had to name your meeting notes document using a naming convention like the following. Client X – Meeting Notes – 20010405 – Online.doc. If you accidentally added a space or misspelled something, everything broke.

Metadata, a better approach

With the advent of search, we were able to separate the data from the metadata. This encoding of metadata into the file name data structure went by the wayside. Instead, we now use metadata to describe the file by tagging it with consistent keywords. Search uses the tags to locate the appropriate content. We also do this today for nearly all Internet related content in hopes that Google and Bing will find it.

If we reimagine Project Business Intelligence as a specialized form of search, you see that the metadata approach works to ensure the right information can be found without encoding data into the project plan structure. There are many benefits to using this approach.

Example: Phase 1 tasks encoding before

For example, today I might have the following situation, where the phase information is encoded into the structure.

image

Example: Phase 1 tasks encoding after

The metadata approach would yield the following structure instead.

image

Metadata benefits

The biggest benefit is agility. If your business needs change, you can your data tagging strategy quickly without requiring restructuring all of the projects. You can roll out a new tagging strategy and the PMs can re-tag their plans in less than a day.

Another benefit is consistency. Using Phase and TaskID, I can extract the Phase 1 tasks consistently from across multiple projects. This also has the side effect of reducing the PMO’s auditing effots.

You can better serve the collaboration needs of the project team while still meeting the demands of external parties. Project plans are simply the notes of the latest state of the conversation between members of the project team. It is intended for servicing their communication and collaboration needs. The PM is now free to structure the plan to serve the needs of their project team. They simply have to tag the tasks accordingly, which is a minimal effort. These tags can be used to denote external data elements such as billable milestones, phase end dates, etc.

Lastly, the plan structure makes better sense to the team and is easier for them to maintain. Top level tasks become the things that they are delivering instead of some abstract process step. The task roll-up provides the health of and progress toward a specific deliverable.

How do I implement project metadata in Microsoft Project?

It requires three steps in Project Server/Online.

  1. Create a metadata value lookup table
  2. Create a task custom field (you may need more than one eventually, but start simple)
  3. Add this metadata field to your Gantt views for the PM to see and use

Note: Don’t use multi-value selection for this need as this creates complexities in the BI solution.

Below is an example of a lookup table created to support this metadata use. One use of it was to support a visualization of all implementation milestones for the next month across the portfolio. The query looked for all milestones with a Reporting Purpose equal to “Milestone.Implementation” to extract the appropriate milestones.

To create a task custom field and lookup table, please refer to this link for the details. Note, you can use the same approach in Microsoft Project desktop using Outline codes.

Metadata Lookup Table

The Reporting Purposes lookup table supports two levels of values. This enables multiple classes of tags, such as milestones and phases. This exercise focuses on the Milestone.Implementation value.

clip_image002

Metadata Custom Field

Create the Reporting Purpose task custom field and attach it to the Reporting Purposes lookup table. Specify that Only allow codes with no subordinate values is selected. This prevents the user from selecting Milestones without selecting a more specific purpose.

clip_image004

I hope you find this article useful. Please post questions and comments below.

Creating Maintainable Power BI Report Models – Part 2

Power BI Kitchen

In this installment, the user facing aspects of your Power BI report models will be discussed. Much of this is considered fit and finish work. It is important that you take the time to address these issues as PowerBI.com enables users to build their own reports and dashboards from your data sets. This finish work will result in less time spent on support and making ad hoc updates.

Hiding data sets and fields in the Power BI Report View

When you are creating a complex report model, you may use transitional data sets or configuration type data sets to create your final data set. These transitional or configuration data is not intended to be used by the end user in creating a dashboard or report. Before releasing a report model, you should determine which of these data sets and fields to hide from the Report view. Doing so will reduce user confusion about this information, allowing them to focus on the data most important to them.

To hide a data set or field in the report view, do the following.

  • In Power BI Desktop, click on the Datasheet icon (1)

Power BI

  • In the Field well, right click on a data set name or on a field
  • Select Hide in Report View (2)

image

  • The data set or field will appear grayed out (3)

image

 

Default formatting

Default formatting has a cumulative positive productivity effect for your users. Setting the default format for numbers and dates frees them from having to spend non-productive time “trying to get the data to look right.” It’s tedious work that can easily be eliminated with a few actions on your part. Your users will be happier and able to be faster at generating views.

Dates

In many data sources, dates are stored as datetime values. Project Online stores every date in this format and this makes sense from a transactional perspective. However, in the BI world, most users aren’t looking for time information. The time data creates visual noise instead.

Also, if you are creating content for international users, it is important to use the right formats such that Power BI uses the end user’s regional setting. This way your American and Australian counter parts both know that 3/11/2016 and 11/3/2016 are respectively, the same date. Date and Datetime formats that automatically adjust to the end user’s regional settings are marked with an asterisk *.

To set the default format for a date, do the following.

  • Power BI Desktop, click on the Datasheet icon (1)

image

  • In the grid, click the heading of the column to format. Only one column can be selected at a time (2)

image

  • Click the Modeling tab in the ribbon (3)

image

  • Change Data Type to Date. (4) Note, the format of the selected date column has changed.

image

  • Click Format dropdown, Date Time, select the first value, which has an asterisk (5)

image

Now, your date values will appear as follows.

image

Numbers

The same process applies to numbers as well. Many systems store numbers in a general decimal number format, leaving users staring at numbers with a variable number of decimals or cost data that isn’t apparent it’s related to money. Variable decimal places creates usability problems as it’s hard to scan the data. Not denoting costs with a currency symbol leaves the number open to misinterpretation.

Decimal Numbers

To format the decimal positions in a number, do the following.

  • In Power BI Desktop, click on the Datasheet icon (1)

image

  • In the grid, click the heading of the column to format. Only one column can be selected at a time (2)

image

  • Click the Modeling tab in the ribbon (3)

image

  • Change Format to Decimal Number (4)

image

  • Set the number of decimals to show (5)

image

Costs

To format a number with a currency symbol, do the following.

  • In Power BI Desktop, click on the Datasheet icon (1)

image

  • In the grid, click the heading of the column to format. Only one column can be selected at a time (2)

image

  • Click the Modeling tab in the ribbon (3)

image

  • Change Format to Fixed Decimal Number (4)

image

  • Set the number of decimals to show (5)

image

Categorization

The last set of good practices for report models is to categorize specific types of data such that the default behavior in Power BI is more user friendly. This applies currently to location based data and urls. By default, Power BI would treat this information as character data. If a user drags a city or country value to a dashboard, likely they will be interested in a map view rather than the name of the city or country. Categorization allows you to tell Power BI that the City or Country field is location data and that a map instead of a grid should be rendered by default. For URLs,, categorization tells Power BI whether to render the link as clickable or to render the image to which it points.

In all cases, click the column heading of the field to categorize and select the Data Category on the Modeling tab to set, similar to the actions above.

Location Data

You can categorize text data as location related or numeric data if it contains latitude or longitude values. The list of location categories is shown below.

image

Web URLs

If you want to make the URL clickable, change the Data Category to Web URL.

image

Image URLs

To render the image accessed by the URL, change the Data Category to Image URL

image

 

Following these simple techniques will result in easier to use and easier to maintain report models.

If you want to learn more, check out our Power BI sessions at http://academy.tumbleroad.com  Use code take100off to get $100 off of any paid Power BI class.

 

 

‘); // ]]>

‘); // ]]>

 

Creating Maintainable Power BI Report Models – Part 1

Power BI Kitchen

Professional chefs use a French kitchen organization approach called “Mise En Place”, which means literally, “putting in place.” It ensures that the placement of ingredients and utensils are optimally placed for efficient cooking. Power BI report models can require the same type of approach to ensure that your report models are maintainable over time. This applies whether you are doing reporting against Microsoft Project Online, Microsoft Project Server or Office 365.

We’ll focus on three techniques for the report developer’s back end configuration this week. We’ll cover the front end configuration next week as it relates to improving the end user experience.

Fast is not always good

Power BI allows you to accomplish a lot quickly when it comes to transforming data. However, if you aren’t smart about how you approach your back end configuration, you could be creating a maintenance nightmare for yourself.

Do you know what is what in a report model that you haven’t touched in months? These three techniques will enable you to easily understand and work with your transformation code over time.

What was I thinking when I did this?

This is a common problem for any developer who has ever done work in a rush. The reason for doing what you did in the fifteen transformation steps was blatantly clear the day you created the model. However, the sands of time have worn away the memories and now you are doing code archaeology to understand your previous work.

Power BI allows you to make your transformation steps self-documenting.

To make your transformation steps descriptive, do the following.

  • Open the query editor
  • Select a data set in the left pane. The list of transformation steps are shown in the Applied Steps pane on the right.
  • Right-click (#1) on transformation step
  • Rename the step with something descriptive

Power BI Renaming a Transformation

Instead of accepting the default “Grouped Rows” step name, you can rename the step to read “Grouping by Year, Week, Resource to provide weekly work totals.” Doing so creates a coherent story of what is happening and why.

Power BI Self Documenting Transformations

What is this data set again?

Power BI provides a default data set name, based typically on the source of the data retrieved. This may not be desirable as the data set may be an amalgamation of data from several sources and have a different intent than that indicated by the first data retrieved. Secondly, this name also appears to the end user and may have no meaning.

To make your data set name descriptive

  • Open the query editor
  • Select a data set in the left pane
  • On the right side, in the Properties Name box above the Applied Steps, rename the data set to be more meaningful to the user
    • If you are using our Conversation-centric design approach, you should already have some candidate names available
  • To update, simply type over the value in the Name field and click off of the field to save
  • When you save the model, the change will be saved

Below, we renamed AssignmentTimephasedDataSet to Resource Work Summary by Week.

Power BI Naming Data Sets

Where did I put that thing?

Another challenge that you may encounter is trying to find a query data set when you have several in a report model. Power BI allows you to create groups and assign your data sets and functions to a specific group.

If I am working on model that is in production, I can create a Version 2 group, copy the data set, move the copy to the Version 2 group. This way, I have both copies and can ensure I don’t accidentally change the wrong version.

In the example below, I used groups as a way of organizing my demos for a webinar. Each group demo number corresponded to a PowerPoint slide so it was easy to keep my place.

Power BI Using Groups

To create a Power BI query group

  • Right click anywhere on the Query Editor left pane
  • Select New Group…
  • Fill out the name and the description
  • Click OK

The description can be seen when you hover over the group name. It is very helpful for providing additional context.

To move a data set to a Power BI query group

  • Right click on the data set name
  • Select Move to Group
  • Select the Group

These three techniques will help you keep things organized and understandable. Next week, we’ll discuss best practices for enabling a great end user experience.

3 Ways to Rev Your Microsoft Project Online and Power BI Performance (Number 2 Is Our Favorite)

Are you having tired of waiting for long refresh times with Power BI? Perhaps, you are getting timeouts because you are retrieving too much data. There’s three easy ways to avoid these issues.

WHY IS MY POWER BI DATA REFRESH SO SLOW?

Many Power BI models refresh slowly as the models are not structured to take advantage of query folding.

Query folding is a process by which specific Power BI transformations are executed by the data source instead of Power BI. Allowing those actions to occur at the source means less data has to be sent to Power BI. Less data means faster data refresh times and smaller report models. Note, not all data sources support query folding, but oData for Project Online and SQL Server for Project Server do.

A sample of these foldable actions include

  • Column or row filtering
  • Group by and aggregation
  • Joins
  • Numeric calculations

For example, you need to find out the amount of work scheduled by week for the quarter. You are querying the time phased data from Project Online. If you aren’t careful, you may be retrieving hundreds of thousands of records. Query folding will make a huge difference in speed and the number of records retrieved. If you have the records filtered by Project Online before retrieving them, you may only receive a few thousand records instead.

ISSUE #1: NOT REFERENCING THE DATA SOURCE PROPERLY

This issue occurs primarily using oData sources, such as Project Online, Office 36 and other web based sources. Query folding breaks if you don’t properly reference the oData feed.

In order for query folding to work properly, the transformations that have folding support need to be the first things executed. If a non-folding transformation is added, no subsequent transformation will be folded. If you don’t reference the oData feed properly, the step to navigate to the oData feed isn’t foldable, therefore blocking all subsequent transformations from being folded.

ISSUE #2: DOING DYNAMIC DATE SELECTION IMPROPERLY

This issue causes more headaches with Project’s time phased data than any other action. In many cases, you are looking to create a range of dates for Power BI to query. If you use the Between filter in the Power BI Query Editor, there’s not option for Today’s date +/- a number of days. If you use many of the other Date/Time filters, like Is Latest, you still seem to pull back a lot of data.

Solving this particular issue requires getting familiar with the M query language so that you can understand how Power BI Desktop performs specific actions.

For example, let’s look at the Is Latest date selection. By default, your dataset is unsorted so Power BI creates a list of the values in the selected column and does a Max value. While this is correct, this could result in a lot of records being retrieved to perform a Max and get one record. The M code over a time phased table looks like this:

= Table.SelectRows(#”Filtered Rows”, let latest = List.Max(#”Filtered Rows”[TimeByDay]) in each [TimeByDay] = latest)

To get much better performance, make the following two changes. First, sort the dataset in a descending manner on the column which you are using to decide latest. In this example, that’s TimeByDay. Sorting is a foldable action so the data source will do the work.

Next, change List.Max to List.First. Since the latest date is the first record in the sorted dataset, a lot less data is required to get the answer. So, my statement is now = Table.SelectRows(#”Filtered Rows”, let latest = List.First(#”Filtered Rows”[TimeByDay]) in each [TimeByDay] = latest)

In testing, the original way required over a 1 Mb of data to be retrieved to answer the question. The new way only retrieves 127 Kb.

ISSUE #3: URLS ARE TOO LONG

This issue comes into play when working with Project Online and SharePoint based data sources. Project, in particular, has a lot of long field names. SharePoint has a limit of 2048 characters for a URL. If you are manually selecting a lot of field names, you can accidentally go past the 2048 character limit.

What this means is that Power BI receives a URL that can’t be acted upon. The default Power BI behavior is to then simply retrieve the feed without modifiers. If this happens on a source with many rows, this could significantly impact performance.

In this case, you would break up your dataset into two or more to fit within the limitation and then merge them back together in Power BI.

WANT TO KNOW MORE?

Join us for a free Power BI performance clinic on March 22! This session will do a deep dive into these issues as well as show you how to determine when folding is happening (and not.) Go here to register!

 

[imageframe lightbox=”no” lightbox_image=”” style_type=”none” hover_type=”none” bordercolor=”” bordersize=”0px” borderradius=”0″ stylecolor=”” align=”none” link=”http://academy.tumbleroad.com/courses/power-bi-performance-clinic” linktarget=”_blank” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ animation_offset=”” class=”” id=””][/imageframe]