Parking Lot Questions

From Dina:

Issues we are trying to address:

  • Pulling data from multiple sources (GP, TLS, excel files, etc) into one file. –

The best way to pull these sources into one place is to start a new .pbix file and then “Get Data” for each source you desire. They will each show up as a “Query” on the right hand side and can easily be customized or just dragged and dropped onto your Report Canvas.

  • Excel cannot handle large volumes of data

Power BI is a very good alternative to this and it’s one of the huge reasons people use products like it.

  • Flexible ways to summarize the data

Data of course can be summarized by using the value and axis fields, but moreover, the Query Editor can be utilized to summarize the data in a way that is more re-usable. In other words, after you customize it all, the .pbix file will save that dataset with the changes you made and you can re-use that changed query/dataset.

From Rob:

Is there a way to gracefully connect power bi to great plains without pointing straight to the underlyingsqltables? Odata?

It would be great if we could figure out how to do this on our instance

It is my opinion that oData is probably the best way to get most data directly and will work in nearly every scenario. If you have the ability to make a service out of your Great Plains data, then it makes it extremely easy to do with oData. Let’s take a look:

Here is a data source (service) that we can assume a programmer made to hold data from anywhere (in this case Great Plains):

Now, scooping that data up with Power BI becomes a breeze:

(MJ will demo here)

An advantage of doing it this way is also that you can offer different services to different groups of people at URL level (will show this in demo)

A nice article that discusses some details you will like:

OData:

Rob (cont.)

This is for solving the problem of being able to schedule a refresh on a dashboard that Alex currently shares. Currently he has to continually re-create locally and re-publish.

Datagateways: We have an enterprise gateway installed and working for the TLS Reporting database. But Alex is producing a dashboard that points to 2 additional data sources: GPsqldatabase and an excel file on a local network drive. Do I need separate gateways installed for each kind of data source or can I use a single gateway to use for everything behind the firewall? For example, is my next step to install another gateway on the file server and another gateway on the GPdbserver, or is my next step to do something to point thetls_rptgateway to these other sources?

Data Management Gateway through o365 – Need Global Admin permissions in o365

A bit of info from the o365 perspective (because I know you use it) – we will discuss

Next – Dynamics online through o365?

Power BI Gateway Tips

Adding Multiple Sources to Gateway

From Daniel:

Is there an easy way to pin multiple pages from a report onto a dashboard, or do you need to pin one page at a time? Thank you for this question. The easiest way to accomplish this is to use the “Live Pin” at the top of the page. That will pin ALL reports to a new or existing dashboard all at once!

From Alex:

  • If you publish and replace an existing dashboard in the web, is there a way to retain the sharing settings you have previously set up?

Let’s start out with a how to article on sharing in general: (this is for your benefit, just for me to make sure you all have it)

  • Is there a way to create a table view of data and have the ability to check off by row (to export only the rows that are selected)

Yes, definitely. You will use the Query editor to do this and is actually in our lab materials as well. Take a look at this article:

  • More detail on RLS

Let’s discuss the article below:

  • How to Mask data

The best and easiest way to create a mask is with code that will obfuscate text. We will look to this link for the details:

OR

You can mask this data before it’s brought into Power BI with SQL

  • More detail on R-script

R is fantastic. Once you learn it and understand what it is all about, you can use it in most reporting systems. Let’s discuss this article in detail:

Also:

For Fun – an example to work through in code,to be used as data source

  • When is it appropriate to create a column rather than creating a measure

Fantastic question! I will open this link during parking lot time to discuss, but here is an excellent link on the subject:

Myte:

The role of SSIS in Power Bi reporting.

How do we chose between SSIS processing or power BI for external data. What are the advantages?

This is a great question and not the easiest to answer. Overall, SSIS is so good for getting data from point “A” to “B”. But, the SSIS Developer has the sole responsibility for getting that data cleaned, transformed, etc. With Power BI, the person who wants the report/dashboard can also participate. In addition:

Some Advantages of SSIS

  1. Many people who “use” the data in Power BI do not have the same permissions an SSIS person would.
  2. If you use SSIS first, the datasets you are loading into Power BI can already be smaller. So if you used SSIS first, then went to Power BI, potentially, you could have already filtered records out and the data that you load into Power BI would be less data and easier to work with.
  3. SSIS ties into SQL Agent, thereby allowing you to automate your SSIS processes. This can be scheduled and monitored from SQL Catalog. So, if you have data that changes a lot, it might be better to use SSIS package schedules and then use Power BI.
  4. If you have data that needs to remain secure in SQL, you have the advantage of NOT giving that data to Power BI.
  5. If your data comes from multiple sources, it may be frustrating for some in Power BI to “glue” them all together, and time consuming. If you glue them together first, it saves the time and trouble for the Power BI users.

Some Advantages of Power BI for ETL

  1. If you have loads of users who need to do different things with the data (for example you have 8 people in accounting who all need to see different parts of the same data), it might be better to have the entire dataset in Power BI and let the accountants decide which data they deem important for themselves.
  2. If your data needs special focus on formulas/calculations that are more understood by the Accountant team for example, it is easier for you to put the data in Power BI and let the experts take over.

Azure – data uploading to Cloud from local database.

I will demo this for you from SQL Server. Starting with SQL 2012, we have several tools to allow you to pass data back and forth to Azure. Quite amazing. There is an article which discusses this and includes info on SSIS as well. It is here:

Live data through query and dataset from table load. View a better choice over table load for live data, instead if data referesh or it does not matter because it is in the cloud, one can load huge data without performance effects.

You are absolutely correct. The cloud should give you the advantage of working with HUGE datasets with minimal performance effects. When you work with Azure, it is customized to separate data out into : blobs, tables, queues and files. I will show you this later in our dev talk. Because Azure knows how to keep data separate like this, Live data is especially good to pass around.

David:

yesterday question around conditional formatting is somewhat tied to KPIs and how we tend to view data/stats that fall outside of a range. Is there way to create / establish business rules that you can then refer to rather than having to setup on each individual report? An example would be a date threshold, if an assets was in a particular event status for longer than X days then it would be flagged for attention.

Let’s take a look at this article first:

Some KPI Info: