Power BI Dashboards in One Day for SharePoint / Office 365

Want to learn how to build this kind of dashboard over SharePoint data?”

Yes you can! The big difference between our class and ones you’ll find elsewhere is that we go beyond the tool knowledge to show you:

  • How should I approach my BI need?
  • What things should be considered when creating dashboards?
  • What are the best practices?
  • How much is this going to cost me to license?
  • What security aspects should concern me?

Why you should take this course

To save time and money, immediately and in the long run. Since we’ve been in your shoes before, we also discuss topics related to maintenance, implementation, security and design best practices.

According to Forrester, half of a department’s annual reporting needs are ad hoc, which works out to be about 50 reports a year. They also added that the fully loaded cost of an individual report is $3200-$6100 per report. Each report can eat up up to 32 hours of time to develop and test. That’s hundreds of thousands of dollars spent in throwaway report investment.

Learn a faster, cheaper way to explore your data. Power BI is well suited to enable quick ad hoc reporting where you spend the majority of the time developing the data set, rather than the individual report. This helps you save money and reduces the cost of developing ad hoc reports.

Who should take this course

If your company uses Office 365 or SharePoint 2013/2016 and you are a:

  • Manager
  • Business Analyst
  • Power User
  • SharePoint Administrator
  • Consultant

You need this class if you are expected to use data from Office 365 and SharePoint to do your job effectively. The class assumes no prior knowledge of Power BI and Data Concepts

What is required for this class

  • Install Microsoft Power BI Desktop
  • We’ll provide a Office 365 site for you to use for exercises

What you will learn

In this training course you will learn how to create Production Dashboards using Power BI, from SharePoint list and document library data.

You will learn how to use Power BI in a multitude of situations, including ad hoc analysis and the creation of formal dashboard. You will learn about the Power BI components: Power BI Desktop, PowerBI.com, Power BI mobile applications and how they can be used with Office 365 and SharePoint

You’ll also receive an introduction into the core functions of Power BI; Data extraction, loading and transformation using Power Query Formula Language (“M”) and DAX. You’ll receive some guidelines on how to extract Project data in fast manner.

You will discover some data modeling practices that will ensure you have maximum flexibility in analysis. You’ll also learn some visualization best practices to ensure you can tell your digital story effectively.

You’ll learn best practices for maintaining content with your organization. This course provides an end to end view of Power BI for Project reporting, so that you are able to use Power BI immediately for your needs.

What you will get

You’ll get the tools to immediately get started on your design. You’ll receive:

  • Design spec that prompts the asking of the right questions
  • PowerPoint-based layout templates for dashboard paper prototyping
  • Tried and true BI design approach

5 critical value-adds you will take back to your company

  • You’ll have a jump start on a standard BI development approach
  • You’ll deliver new insights into your data.
  • You’ll have a great understanding of how Power BI can be used and implemented
  • You’ll learn techniques to make your dashboards perform well with SharePoint data
  • You’ll gain insight into how others are leveraging Power BI within their companies.

How will the class be conducted?

The class will be conducted live in an 8 hour session over Skype for Business, from 7 AM – 11 AM and 12 PM to 4 PM Pacific Time, with a lunch break from 11 AM -12 PM and other breaks during the day.

The sessions will occur on Wendesday, September 28, 2016.

This class will be recorded and made available, in case you miss part of the class or are unable to attend live.

What’s the cost?

Only $199 per person to learn how to unlock the power of your data.

Questions?

Contact us directly at info@tumbleroad.com.

Curriculum

  • Introduction to Power BI
  • Getting Data from SharePoint
  • Using Power Query M for Data Retrieval and Transformation
  • Data Modeling with DAX
  • Data Visualization Techniques
  • Content Administration and Deployment
  • Licensing and Planning Considerations

Project Dashboards in One Day Using Microsoft Power BI – Project Online – Sept 14

“Want your Project reporting to look like this?”

Then this is the class for you! The big difference between our class and ones you’ll find elsewhere is that we go beyond the tool knowledge to share real world experience with these questions.

  • How should I approach my BI need?
  • What things should be considered when creating dashboards?
  • What are the best practices?
  • How much is this going to cost me to license?
  • What security aspects should concern me?

Why you should take this course

To save you and your company time and money. Project management depends on great data. It’s the lifeblood of your team for making decisions and taking action. You need data for decisions and you are spending a lot of time and money currently, trying to get to that data.

According to Forrester, half of a department’s annual reporting needs are ad hoc, which works out to be about 50 reports a year. They also added that the fully loaded cost of an individual report is $3200-$6100 per report. Each report can eat up up to 32 hours of time to develop and test. That’s hundreds of thousands of dollars spent in throwaway report investment.

Learn a faster, cheaper way to explore your Project data. Power BI is well suited to enable quick ad hoc reporting where you spend the majority of the time developing the data set, rather than the individual report. This helps you save money and reduces the cost of developing ad hoc reports.

Who should take this course

If your company uses Project Online and you are a:

  • Manager
  • Business Analyst
  • Power User
  • Project Server/Online Administrator
  • SharePoint Administrator
  • Consultant

You need this class if you are expected to use data from Microsoft Project to do your job effectively. The class assumes no prior knowledge of Power BI and Data Concepts

What is required for this class

  • Access to your Microsoft Project Online instance
  • Install Microsoft Power BI Desktop
  • If you don’t have Project Online yet, don’t worry. We have one you can use, for this class.

What you will learn

In this training course you will learn how to create Project Dashboards using Power BI, from Project Server data.

You will learn how to use Power BI in a multitude of situations, including ad hoc analysis and the creation of formal dashboard. You will learn about the Power BI components: Power BI Desktop, PowerBI.com, Power BI mobile applications and how they can be used with Microsoft Project Server/Online.

You’ll also receive an introduction into the core functions of Power BI; Data extraction, loading and transformation using Power Query Formula Language (“M”) and DAX. You’ll receive some guidelines on how to extract Project data in fast manner.

You will discover some data modeling practices that will ensure you have maximum flexibility in analysis. You’ll also learn some visualization best practices to ensure you can tell your digital story effectively.

You’ll learn best practices for maintaining content with your organization. This course provides an end to end view of Power BI for Project reporting, so that you are able to use Power BI immediately for your needs.

What you will get

You’ll get the tools to immediately get started on your design. You’ll receive:

  • Design spec that prompts the asking of the right questions
  • PowerPoint-based layout templates for dashboard paper prototyping
  • Tried and true BI design approach
  • Data dictionary of the Microsoft Project data store
  • Relationship diagrams for all Microsoft Project entities

5 critical value-adds you will take back to your company

  • You’ll have a jump start on a standard BI development approach
  • You’ll deliver new insights into your data.
  • You’ll have a great understanding of how Power BI can be used and implemented
  • You’ll learn techniques to make your dashboards perform well with project data
  • You’ll gain insight into how others are leveraging Power BI within their companies.

How will the class be conducted?

The class will be conducted live in an 8 hour session over Skype for Business, from 9 AM – 12 PM and 1 PM to 6 PM Pacific Time, with a lunch break from 12 PM -1 PM and other breaks during the day.

The sessions will occur on Wednesday, September 14, 2016.

This class will be recorded and made available, in case you miss part of the class or are unable to attend live.

Need an earlier time? Look at this Central time alternative.

What’s the cost?

Only $199 to learn how to unlock the power of your Project data.

Questions?

Contact us directly at info@tumbleroad.com.

Curriculum

  • Introduction to Power BI
  • Getting Data from Project
  • Using Power Query M for Data Retrieval and Transformation
  • Data Modeling with DAX
  • Data Visualization Techniques
  • Content Administration and Deployment
  • Licensing and Planning Considerations

Project Dashboards in One Day Using Microsoft Power BI – Project Online – Sept 13

“Want your Project reporting to look like this?”

Then this is the class for you! The big difference between our class and ones you’ll find elsewhere is that we go beyond the tool knowledge to share real world experience with these questions.

  • How should I approach my BI need?
  • What things should be considered when creating dashboards?
  • What are the best practices?
  • How much is this going to cost me to license?
  • What security aspects should concern me?

Why you should take this course

To save you and your company time and money. Project management depends on great data. It’s the lifeblood of your team for making decisions and taking action. You need data for decisions and you are spending a lot of time and money currently, trying to get to that data.

According to Forrester, half of a department’s annual reporting needs are ad hoc, which works out to be about 50 reports a year. They also added that the fully loaded cost of an individual report is $3200-$6100 per report. Each report can eat up up to 32 hours of time to develop and test. That’s hundreds of thousands of dollars spent in throwaway report investment.

Learn a faster, cheaper way to explore your Project data. Power BI is well suited to enable quick ad hoc reporting where you spend the majority of the time developing the data set, rather than the individual report. This helps you save money and reduces the cost of developing ad hoc reports.

Who should take this course

If your company uses Project Online and you are a:

  • Manager
  • Business Analyst
  • Power User
  • Project Server/Online Administrator
  • SharePoint Administrator
  • Consultant

You need this class if you are expected to use data from Microsoft Project to do your job effectively. The class assumes no prior knowledge of Power BI and Data Concepts

What is required for this class

  • Access to your Microsoft Project Online instance
  • Install Microsoft Power BI Desktop
  • If you don’t have Project Online yet, don’t worry. We have one you can use, for this class.

What you will learn

In this training course you will learn how to create Project Dashboards using Power BI, from Project Server data.

You will learn how to use Power BI in a multitude of situations, including ad hoc analysis and the creation of formal dashboard. You will learn about the Power BI components: Power BI Desktop, PowerBI.com, Power BI mobile applications and how they can be used with Microsoft Project Server/Online.

You’ll also receive an introduction into the core functions of Power BI; Data extraction, loading and transformation using Power Query Formula Language (“M”) and DAX. You’ll receive some guidelines on how to extract Project data in fast manner.

You will discover some data modeling practices that will ensure you have maximum flexibility in analysis. You’ll also learn some visualization best practices to ensure you can tell your digital story effectively.

You’ll learn best practices for maintaining content with your organization. This course provides an end to end view of Power BI for Project reporting, so that you are able to use Power BI immediately for your needs.

What you will get

You’ll get the tools to immediately get started on your design. You’ll receive:

  • Design spec that prompts the asking of the right questions
  • PowerPoint-based layout templates for dashboard paper prototyping
  • Tried and true BI design approach
  • Data dictionary of the Microsoft Project data store
  • Relationship diagrams for all Microsoft Project entities

5 critical value-adds you will take back to your company

  • You’ll have a jump start on a standard BI development approach
  • You’ll deliver new insights into your data.
  • You’ll have a great understanding of how Power BI can be used and implemented
  • You’ll learn techniques to make your dashboards perform well with project data
  • You’ll gain insight into how others are leveraging Power BI within their companies.

How will the class be conducted?

The class will be conducted live in an 8 hour session over Skype for Business, from 8 AM – 12 PM and 1 PM to 5 PM Eastern Time, with a lunch break from 12 PM -1 PM and other breaks during the day.

The sessions will occur on Tuesday, September 13, 2016.

This class will be recorded and made available, in case you miss part of the class or are unable to attend live.

Need a later time? Look at this Pacific time alternative.

What’s the cost?

Only $199 to learn how to unlock the power of your Project data.

Questions?

Contact us directly at info@tumbleroad.com.

Curriculum

  • Introduction to Power BI
  • Getting Data from Project
  • Using Power Query M for Data Retrieval and Transformation
  • Data Modeling with DAX
  • Data Visualization Techniques
  • Content Administration and Deployment
  • Licensing and Planning Considerations

How to query Project Online multiple value fields in Power BI

Power BI

This post addresses a need to arises when querying Project Online data via OData. It can also serve as a solution template for other data sources.

Synopsis

A number of projects in our portfolio have impacts in different countries. These countries are designated via a Country Project level multi-value (MV) custom field. When multiple values are selected from the dropdown list, it creates a comma-delimited list of values. For example, you could see Brazil, Canada, United States returned as a field value from OData.

Challenge

One of the challenges is that this list can be any number of values and will grow and contract over time. Therefore, the technique should automatically adjust to the underlying maximum number of specified values.

Approach

For each MV field, a new data set representing all values in the MV field will be added to the data model. This new MV field data set will be a child to the original Project master data set. The relationship between the Project set and the MV set will be 1:N.

An inline function will also be used so that it can be executed for each record in the data set. This function will perform the split of values into separate columns.

Once split, the values will be unpivoted, creating a record for each ProjectId, MV Field value pair.

Potential Issues

  • Correct data set relationships. Initially, Power BI created the MV data set with a 1:1 relationship instead of a 1:N. Once corrected, all errors went away.
  • You will need to watch refresh performance as you are basically querying the same data multiple times within the model.

Procedure

Register here for this free session to see the step by step video and to download the PBIX file containing the data model with sample code. http://academy.tumbleroad.com/courses/how-to-parse-multi-value-columns-in-power-bi

One surprising challenge that can derail your Business Intelligence implementation

One challenge facing Business intelligence implementations today is related to how data is shared within the organization. We’ve been working to make work more open and social over the last few years, but there’s one place we’ve neglected to address along the way. How do we share ad hoc business intelligence data within the organization?

 

In years past, our Business Intelligence content was developed as individual reports. This disconnected content approach enabled data producers to act as data gatekeepers. The owner of the report would decide who to share the data with and would email it to the person as they saw fit.

 

In subsequent years, we migrated to a portal based distribution strategy. Tools like SQL Server Reporting Services enabled us to post formal reports centrally, where they could be accessed. However, ad hoc reporting generally remained outside of the portal, to remain shared via email.

 

With the advent of new tools like Power BI, ad hoc reporting is easier than ever. Everything on the screen is clickable and it enables interactive data exploration. The intended interaction with these tools is similar to how we use Facebook, Twitter and Pinterest in our personal lives today. We post information and others can choose how and when to consume it.

 

Imagine the confusion that arises when a BI implementation team gets puzzling questions from groups. How do I email this data model to others? How do I print a dashboard and so forth? Simply updating the tool set to the latest generation, while ignoring certain underlying organizational behaviors can impact the long term success of implementing the latest generation BI tools.

 

An organizational assessment should be done as part of a Business Intelligence tool implementation to consider how people share data currently and as to whether those sharing habits are compatible with the new BI tools. If the organization is used to posting and forwarding links, then they should have no issues adopting the new tools. If the organization is still email centric in their information sharing, a concerted effort will be required to change habits away from sharing content via email and to posting and link sharing. These findings can be used to adjust scope around work needed for training and ongoing support.

 

What has been your experience? Please share below in the comments.

Using Project Online, Power BI Alerts and Microsoft Flow for Custom Alerts

This is so cool! I just received an alert about a data condition in Project Online from Microsoft Flow. The best thing is that this condition isn’t anything Microsoft provides out of the box. With the functionality now in Flow and Power BI, we can now construct our own using Power BI and Flow.

With recent updates, Microsoft Power BI added the ability to set alerts on the card visual. You can read more about the details here. One of the settings allows an email to be sent with a distinct subject. That got me thinking since I knew from playing with Microsoft Flow that you can connect it to Office 365 Outlook and drive flows from specific emails. Flow also provides a Push Notification function so that I can push a notification to the Flow app on my iPhone.

It seems like all of the pieces I need are there to create my own custom notification.

First, I have a Project Online instance where I’ve got projects and people assigned to those projects. I’m using Project to manage capacity so knowing when the total number of overallocations is growing helps me react accordingly.

Project Web App

Second, using our Marquee Project Dashboards product, I have a count of Overallocated resources already available. I’m using Power BI Pro, so my data model refreshes automatically multiple times a day.

Marquee Dashboard

Third, I’ve created my own dashboard and I pinned the count of Overallocated Resources to it. Once the card is on your dashboard, you can click the ellipsis menu … and access the alert function (bell icon).

When the Alert panel opens, I turned it on, changed the Alert title and set my threshold. You can also determine if you need once an hour or once a day for alerting. The key is to check the box that says to send an email. This provides a way for us to use Flow to act upon this alert.

Now, we go to http://flow.microsoft.com. Once you login (because you already have an account, right?), go to My Flows and select Create a New Flow. You are going to create a two-step flow where it connects to your email looking for a specific subject and sender and the second step is where it sends the notification.

  • Type Outlook into the Search window to see the Outlook events.
  • Select Office 365 Outlook – When a new email arrives
  • Ensure the connection is to the right account that will receive the alert email
  • Click Show Advanced Options
  • I filled in mine to look like this. Note the From and Subject Filter values.

  • Now click New Step
  • Click Add an Action
  • Type Push into the search window
  • Select Push Notification – Send a push notification
  • I filled out mine to look like the following. You could have the URL set to bring you back to Power BI or Project. I chose for simplicity to bring me to Outlook.

I would have loved to have had an SMS message generated. This would have required a Twilio account that I don’t have at the moment. So since I was playing, I took the free route and loaded the Flow app on my iPhone. The Push notification would then show up there on my phone.

Once set up,

  • I updated a Project Online project plan to create a new resource overallocation and published it.
  • Power BI automatically refreshed the dataset, the number of overallocated resources increased.
  • This value change triggered a Power BI alert and sent an alert email to my Outlook inbox.
  • Flow picked up the alert and fired off the Flow.
  • The push notification was sent to my phone and bam, there it is on my Apple Watch.

What do you think? What have you created with Flow and  Power BI? Tell me in the comments below! I hope you found this useful.

Project Dashboards in One Day using Power BI

Learn how to use Microsoft Power BI to unlock your Project Online / Project Server data to create Project Dashboards and more.

Project management depends on great data. It’s the lifeblood of your team for making decisions and taking action. You need data for decisions.

You’ve got to ensure you have the right data…now to answer questions like:

  • Which projects are behind?
  • What projects haven’t been updated in recent memory?
  • Can we staff this new project?
  • What active issues do we have to resolve?
  • Where did all of our time go?
  • And did you actually fill out your time sheet this week?!?

You need the right BI tool to get immediate access to your data. The tool flexible enough that allows you to answer the questions of today and of tomorrow.

Why you should take this course

To save time and money, in a nutshell.

According to Forrester, half of a department’s annual reporting needs are ad hoc, which works out to be about 50 reports a year. They also added that the fully loaded cost of an individual report is $3200-$6100 per report. Each report can eat up up to 32 hours of time to develop and test. That’s hundreds of thousands of dollars spent in throwaway report investment.

Learn a faster, cheaper way to explore your Project data. Power BI is well suited to enable quick ad hoc reporting where you spend the majority of the time developing the data set, rather than the individual report. This helps you save money and reduces the cost of developing ad hoc reports.

Who should take this course

If your company uses Project Online and you are a:

  • Manager
  • Business Analyst
  • Power User
  • Project Server/Online Administrator
  • SharePoint Administrator
  • Consultant

You need this class if you are expected to use data from Microsoft Project to do your job effectively. The class assumes no prior knowledge of Power BI and Data Concepts

What is required for this class

  • Access to your Microsoft Project Online instance
  • Install Microsoft Power BI Desktop
  • If you don’t have Project Online yet, don’t worry. We have one you can use, for this class.

What you will learn

In this training course you will learn how to create Project Dashboards using Power BI, from Project Server data.

You will learn how to use Power BI in a multitude of situations, including ad hoc analysis and the creation of formal dashboard. You will learn about the Power BI components: Power BI Desktop, PowerBI.com, Power BI mobile applications and how they can be used with Microsoft Project Server/Online.

You’ll also receive an introduction into the core functions of Power BI; Data extraction, loading and transformation using Power Query Formula Language (“M”) and DAX. You’ll receive some guidelines on how to extract Project data in fast manner.

You will discover some data modeling practices that will ensure you have maximum flexibility in analysis. You’ll also learn some visualization best practices to ensure you can tell your digital story effectively.

You’ll learn best practices for maintaining content with your organization. This course provides an end to end view of Power BI for Project reporting, so that you are able to use Power BI immediately for your needs.

What you will get

You’ll get the tools to immediately get started on your design. You’ll receive:

  • Design spec that prompts the asking of the right questions
  • PowerPoint-based layout templates for dashboard paper prototyping
  • Tried and true BI design approach
  • Data dictionary of the Microsoft Project data store
  • Relationship diagrams for all Microsoft Project entities

5 critical value-adds you will take back to your company

  • You’ll have a jump start on a standard BI development approach
  • You’ll deliver new insights into your data.
  • You’ll have a great understanding of how Power BI can be used and implemented
  • You’ll learn techniques to make your dashboards perform well with project data
  • You’ll gain insight into how others are leveraging Power BI within their companies.

How will the class be conducted?

The class will be conducted live in an 8 hour session over Skype for Business, from 8 AM – 12 PM and 1 PM to 5 PM Pacific Time, with a lunch break from 12 PM -1 PM and other breaks during the day.

The sessions will occur on Tuesday, August 9, 2016.

This class will be recorded and made available, in case you miss part of the class or are unable to attend live.

What’s the cost?

Only $129 to learn how to unlock the power of your Project data.

Questions?

Contact us directly at info@tumbleroad.com.

Curriculum

  • Introduction to Power BI
  • Getting Data from Project
  • Using Power Query M for Data Retrieval and Transformation
  • Data Modeling with DAX
  • Data Visualization Techniques
  • Content Administration and Deployment
  • Licensing and Planning Considerations

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!