Create Power BI reports and dashboards with PowerApps Common Data Service

Today we are pleased to announce the availability of Power Apps Common Data Service (CDS) connector to Power BI desktop as a preview. The preview will start rolling out in the USA. You can now create rich reports with the data in CDS using Power BI desktop, the authoring tool for Power BI. Using the full power of Power BI, you can then go on to create and share stunning dashboards with users in your organization.

We have worked very closely with Power BI team to develop this connector. If you have downloaded the February update of Power BI desktop, you already have access to the connector. Power BI is intimately aware of PowerApps and CDS. The PowerApps CDS connector enables you to leverage key features built into CDS and PowerApps in Power BI.

Reports reflect real time data

There’s no need to schedule a refresh in Power BI. When the data is updated in CDS, changes are reflected in reports. Reports are interactive and responsive. You can interactively explore CDS data using the capabilities of Power BI.

Power BI respects security policies defined in CDS

When you secure CDS entities using policies for a set of users, the same permissions are applied to Power BI reports. As an author writing Power BI reports, you can only report against the entities to which you have been granted access in CDS. As an author, when you share reports with your peers in PowerBI.com, your peers can only see the data they are allowed to access in CDS. You do not need to secure data explicitly within Power BI.

Power BI is aware of rich data types and relationships defined in CDS

CDS enables makers to define rich data types such as Address, Boolean, and Currency when you add fields to an entity. In addition to rich types, makers can also define a list of values, called picklists. These types are recognized by Power BI as first class data types. For an example, when you report using an address field, Power BI shows a map as a default visualization.

Entities are presented by subject areas (perspectives)

While CDS contains a rich set of entities representing many business areas, as a Report writer, you may be interested in creating reports by focusing on a business question or a subject area. To make it easier to report on a given business question or a subject area, we have organized entities into a set of ready-made subject areas called Perspectives. A perspective offers a “view into data” from a reporting point of view.

At the time of launch, we have categorized the entities in to 10 perspectives as follows.

 

 

Ready-made Perspective

Entities*

Examples of business questions that can be answered using the perspective

Sales Orders

Sales Order, Sales Order charge, Sales Order Tax, Worker, Contact, Opportunity, Account

Are our Sales Orders coming from a specific customer segment or a region?

Value of Sales Orders at hand compared to the estimated value of Opportunity

Sales Order Lines

Sales Order, Sales Order line, Sales Order line charge, Sales Order line Tax, Sales order line shipment, Product, Opportunity, Account

Market basket analysis (ie. are buyers of beer also buying diapers?)

Orders where we have partial (or delayed) shipments

Sales Invoices

Sales invoice, Sales Order,

Sales invoice charge,

Sales invoice Tax, Account, Contact

Estimated opportunity vs. total invoiced amount (ie. how much more business can we get?)

Who should I call to get my money? (ie. amount, due date by account contact)

Sales Invoice Lines

Sales invoice line, Sales invoice, Sales Order, Sales invoice charge, Sales invoice Tax, Account, Contact

Are we offering larger than usual Discounts to specific accounts or products?

Order lines with Partial (or delayed) shipments (ie. are we shipping some products always late?)

Purchase Orders

Purchase Order,

Purchase Order charge,

Purchase Order Tax,

Contact

We buy a lot! - are we getting good discounts from our vendors ?

Total taxes owed to various statutory bodies

Purchase Order lines

Purchase Order Line,

Purchase Order,

Purchase Order line charge, Purchase Order line Tax, Contact, Vendor

Purchase Order Line Receipt, Product

Most frequently purchased products (and vendors from whom we buy)

Vendors who consistently deliver late to us

Vendor Invoice

Vendor Invoice,

Vendor

Purchase Order,

Vendor Invoice Charge

Vendor Invoice Tax

Worker

Contact

Which bill should I pay (and which ones can we delay)

Total taxes owed to statutory bodies and when must I pay

Vendor Invoice Lines

Vendor Invoice Lines

Product

Vendor Invoice

Purchase Order Line

Vendor Invoice Line Tax

Vendor Invoice Line Charge

Which vendor bill should I settle first (or which vendor relationships/ products are strategic to our relationship?)

Time elapsed from Order to receipt/payment

Cases

Case

Account

Worker

Contact

Case load per person by time

What are the most common cases and where do they originate from?

Case Activity

Case Activity

Case

Contact

Account

Worker

Case history timeline

Cases where we are awaiting inputs from customer

*You may notice that we have renamed some of the Entities within perspectives to provide better context for business questions.

When you extend the ready-made entities by adding fields, they will be reflected in Power BI desktop under the respective perspective (no pun intended) and you can use them in reports right away.

As we add more ready-made entities into CDS, we will introduce more perspectives that answer valuable business questions. Soon, we will also enable you to define your own perspectives or, extend ready-made perspectives by adding your own entities.

 

Enabling Power BI integration in PowerApps

So how do you start reporting with Power BI? – during preview, you would need to enable Power BI integration feature using the admin portal in PowerApps (admin.PowerApps.com). If your CDS environment happens to be in a region where this feature is enabled, and if you have been selected for the preview, you will see an option to enable the feature. You need to enable the feature only once per database.

This feature will be available initially for selected customers (or databases, more accurately) based in USA. If your database is located in the USA, you will see the option within the next few weeks. It will be rolled out to other regions soon. Can’t wait that long?, If you like to participate in the preview program and get access to the connector, please e-mail CDSPowerBIPreview(at)Service.Microsoft.com.

 

clip_image002

Click the button to enable the feature and you are ready to go.

clip_image004

Who can write reports

All the makers in your CDS environment can write reports using Power BI desktop. If you wish to restrict data to makers, you can define security policies in CDS and associates makers to the policies. (You can add your report writers as makers or define security policies using the Administration portal in PowerApps)

Before they can start writing reports, they have to login to the PowerApps environment at least once to confirm that they are a maker.

Writing reports

Now let’s write some reports in Power BI desktop. Launch Power BI desktop (November update or later, preferably February update) and select the PowerApps connector shown in the get data menu

clip_image006

If you are already logged into Power BI desktop, you will be asked to choose an environment to report on. If not, system will ask you to login with your school or work (AAD) account.

If you have access to multiple CDS environments, that is, you have been designated as a maker in more than one CDS environment, you need to select the environment you need to report on.

clip_image008

Next you will be shown a list of Perspectives to report on.

clip_image010

On choosing a perspective, you will be shown the list of Entities for reporting.

clip_image012

For more information you can refer to the Power BI blog post here: https://powerbi.microsoft.com/blog/power-bi-desktop-february-feature-summary/#cds

Publish CDS reports to PowerBI.com

You can publish the reports to PowerBI.com and share with your peers.

These reports, along with the reports that you create in Power BI, can be can be shared with users in your organization using capabilities of Power BI.

Power BI licensing requirements

As a maker, you can create reports using CDS environments in Power BI desktop without requiring a Power BI license. Makers can also publish their reports to PowerBI.com with a pro or a free license.

Users who view reports built using the PowerApps CDS connector, will require Power BI pro license. If you do not have a Power BI pro license, you can choose to go with a Power BI pro trial license and all the functionality will be available to you.

Coming in the near future

As we mentioned earlier, this feature is currently available as a preview in the USA. Our immediate next steps will see the preview feature extended to many regions supported by PowerApps and CDS. If you like to preview this feature in your region before others, or if you would like to preview this feature, drop us a mail via CDSPowerBIPreview(at)Service.Microsoft.com