Using LCS Shared asset library Power BI reports in a 1 Box environment

As you know LCS is a service that manage your D365FO environments and is operated by Microsoft. There are currently 23 PowerBI.com solutions available in the Shared assets library of LCS:

1. Maintenance request
2. Maintenance work orders
3. Expense Admin Dashboard
4. Expense Personal Dashboard
5. Vendor payments
6. Practice Manager
7. Fixed asset valuations
8. Credit and collections management
9. Cash overview - current company
10. Cash overview - all companies
11. Bank balances
12. Actual vs Budget
13. Sales and profitability performance
14. Purchase spend analysis.
15. Production performance
16. Warehouse performance
17. Cost accounting analysis
18. Financial performance
19. Recruiting reports
20. Employee competencies and development reports
21. Organizational training reports
22. Workforce metrics
23. Compensation and benefits reports

Power BI reports are developed by using the Entity store and are then distributed in LCS as implementation assets. Most of the PowerBI.com solutions in the following list have been embedded in analytical workspaces in Microsoft hosted environments. In this blog post I will teach you how to use this Power BI reports in a demo 1 Box Cloud Hosted Environment.

 1. Go to https://lcs.dynamics.com/V2/SharedAssetLibrary and download the Power BI reports files in your 1 Box environment virtual machine. In this case we will select the Cash overview - all companies

2. Refresh the LedgerCovLiquidityMeasurement in the Entity Store. Go to the Finance and Operations web client, open the Entity Store page. (Select System administration > Setup > Entity Store

3. Go back to the LCS project and copy the user: axdwadmin and password.

4. Open the Cash overview all companies.pbix file in your 1 Box environment virtual machine and change the Data source settings:

Select Edit Permissions, Edit, select the Database tab and paste the user name and password that you copy in the step 3.

5. Verify that the visualization looks similar to the following screenshot: 

6. Create and Configure a data gateway to point to the AxDW database on your 1 Box VM. Login in to https://powerbi.microsoft.com/ and download the Data Gateway:

a. Select the standard mode:

b. Use the email account that you used to login to PowerBI.com

c. Enter a gateway name of “LocalEntityStore” and enter a recovery key that will be remembered

d. Click Close after you confirm the gateway is ready.

e. Navigate to Powerbi.com and log in with the same credentials https://powerbi.microsoft.com/en-us/

In the upper right corner select the gear icon to manage settings.  In the drop down select “Manage gateways”

f. Select the LocalEntityStore data gateway that was created and on the drop down, select the "Add Data Source” option.

g.Enter in the local SQL information from the LCS project and copy the user: axdwadmin and password.

7. From within Power BI Desktop, on the home tab, select the Publish option.

a. Select the My Workspace destination

8. In Powerbi.com, go into “My Workspace” and to the report that was published should now be able to run the report and see data from the local entity store.

The Cash overview Power BI content was created for individuals who are responsible for cash in their organization. The Cash overview Power BI content provides visibility into your cash flow. It also provides forecasts that can help you make better decisions and therefore improve the health of your cash flow. You can analyze cash by legal entity, currency, and bank account to get a better understanding of surpluses and shortfalls.

Thanks for reading.

Acknowledgments: I want to thank my colleague Amy Flower (Customer Engineer) for her contributions and peer review.