Positioning Power BI Dataflows

BI Polar

It’s been two weeks since Power BI dataflows became publicly available in preview, and there’s been a flood of interest and excitement as people have started exploring and using this new capability for self-service data preparation.

There has also been a growing trickle of confusion. To illustrate this confusion, I will share a few examples.

Here’s the first example: A comment thread from a few weeks ago on this post, initiated by Neville:

2018-11-22_12-01-55

The potential confusion here may come from the fact that Power BI dataflow entities are similar in purpose to tables in a data warehouse or data mart. But due to many factors (including the underlying storage being in files in a data lake) it is not generally appropriate to think of dataflows as a replacement for a data warehouse. While there may be exceptions to this rule, any time you hear someone saying you don’t need…

Ursprünglichen Post anzeigen 759 weitere Wörter

Solving Real World Scenarios with the Microsoft Power Platform

Time to play with dataflows. I have the following situation. For transactions in foreign currencies I need accurate fx rates. Can a dataflow solve this requirement?

First thing I did was to subscribe for a free plan here:

 

Then in Power BI service I created my very first dataflow. Basically the flow consists of one simple api call (well described on the fixer page):

And with some basic steps in the Power Query editor I ended up with this:

Now I can link every transaction in foreign currency to my central fx rates table. This is great but there was one important thing missing. I couldn’t figure out a way to store the results like it is possible with the streaming dataset feature.

So I was searching for an alternative solution. Why not use CDS for application to store the data? And then point the Power BI dataflow to the CDS entity:

I created a new entity as I could not find an existing one in the standard cds entities:

 

Afterwards I created a custom connector to the fixer api:

 

Next thing I needed was a flow to pull the data in my newly created entity:

And last of all I created another dataflow in Power BI pointing to the CDS entity:

 

Now I have the result I was looking for. A central fx rates table with historical data.

I have to admit figuring out all of this was fun. And best of all I could solve a real business need.

 

 

A few DAX functions

Data Warehousing, BI and Data Science

This query shows summarise, addcolumns, filter, union, row and format DAX functions:

  • The financials table is filtered on Product = Montana
  • Display just the Country column using Summarize
  • Sales column is added using AddColumns functions
  • Total row is added using Union function
  • Add commas for thousand separator using Format function

Output:
Canada 24,887,655
Germany 23,505,341
France 24,354,172
Mexico 20,949,352
USA 25,029,830
Total 118,726,350

Data source: link
Ref: Whytheq: link, SQLBI: link

Ursprünglichen Post anzeigen

Microsoft PowerApps Office Add-In enables user to read, modify and insert data in CDS environment. Add-In is available for download here for free, or you will be prompted to install Add-In for first time when you open entity data in excel from PowerApps portal. Add-In establishes connection to PowerApps OData service. Note: Add-In is compatible […]

über Manage CDS Entity Data – Microsoft PowerApps Office Add-In — Bansal Blogs – Dynamics 365, PowerApps, Microsoft Flows, Power BI

Calling The Power BI REST API From Microsoft Flow, Part 3: Finding All Data Sources Used In A Power BI Tenant

Chris Webb's BI Blog

[Part 1 of this series can be found here and part 2 here]

The problem with self-service BI is that you never quite know what your users are up to. For example, what data sources are they using? Are there hundreds of Excel files being used as data sources for reports that you don’t know about? If so, where are they? Could they and should they be replaced by a database or something else more robust? In this post I’ll show you how you can use Microsoft Flow and the Power BI REST API (see part 1 to find out how to create a Flow custom connector to call the Power BI API) to get the details of all the data sources used in all of the workspaces of your Power BI tenant.

I’ll admit that doing this turned out to be a bit trickier than I had expected…

Ursprünglichen Post anzeigen 717 weitere Wörter

Keeping Big Data Big with Aggregations

PhantomDB

I have built several solutions over the years to enable business users to get at the data they need to do the analysis that is important to them. I would build cubes so that they could slice, dice and mold the data into the shape they need. They would use tools like Excel, Reporting Services and more recently Power BI to interactively work with an entire dataset. Then I entered the world of Big Data when I started building reporting solutions Microsoft’s Bing business teams. All the sudden, the data was too large to fit into one cube. Suddenly it became impractical to have to load all the data into one SQL databases.

While the size of the data presented many implementation challenges to the development team, it didn’t change what the business users needed to do with it. To enable them to use the same tools they were used…

Ursprünglichen Post anzeigen 939 weitere Wörter