9.6 KiB
| layout | title | author | author-link | date | categories | color | image | excerpt | language | verticals | geolocation | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| post | How Power BI Embedded helps MB3M provide a more dynamic reporting solution to its customers | Franck Mercier | # | 2017-01-16 |
|
blue | images/MB3M/feat_Dashboard.PNG | MB3M worked with Microsoft to design a solution using Power BI Embedded that enhances a customer portal website and improves reporting options for travel agencies. |
|
|
|
French company MB3M worked with Microsoft to build a better reporting solution to help meet the needs of its travel agency customers. The project involved MB3M Travel, an ERP/CRM system that fits the needs of travel agencies and aims to help them achieve their digital transformation. This solution is based heavily on Azure, and uses more than 200 Azure SQL databases, mostly in an elastic pool.
To provide the right insights at the right time, we decided to add data visualization technologies to the MB3M customer portal website with Power BI Embedded. This customer portal is targeted to travel managers, who want to analyze travel expenses on a wide scope, detect global trends, and also track individual expenses. Our solution required the creation of secured interactive reports for them. This involved the creation of several Power BI reports to provide the right information for these travel managers.
Core team:
- Thomas Bolon (@styx31) – CTO, MB3M
- Franck Mercier (@FranmerMS) – Technical Evangelist, Microsoft
- Sébastien Pertus (@sebastienpertus) – Technical Evangelist, Microsoft
Customer profile
MB3M is a software company founded in 2007 and located in southwest France that provides apps for travel agencies and tour operators. Its flagship product, MB3M Travel, covers most of the CRM, financial, and accounting needs of travel agencies.
Problem statement
Today, MB3M serves many customers across France. These customers (business travel agencies) request the ability to deliver dynamic reports to their own customers (travel managers in regular companies) using a web portal. MB3M already provides a reporting solution, but it is solely based on PDF and Excel documents delivered by email or through this portal. Travel managers are asking for something more dynamic and more embedded, and they also want be more self-sufficient, not to require interactions with their business contacts to analyze their data.
Solution, steps, and delivery
1. Create SQL Database and connect Power BI desktop
MB3M uses Azure SQL Database and elastic pool heavily in its solution. MB3M has more than 200 Azure SQL databases and most of them are in the pool. Each travel agency has its own database.
For our reporting solution, we will create one Azure SQL database per travel agency's customers to store a reporting data in a star schema. We decided to use this kind of architecture for the customer's data isolation purposes.
We started with one customer of one travel agency, and we plan to deploy the solution on the fly. We also have customers hosted in an on-premises datacenter, and we will migrate them in the near future. The schema of the databases is the same as the one in the elastic pool.
To populate the reporting database, we use an internal application to transfer data from the travel agencies' databases (data from the pool as well as from the on-premises datacenter). The reporting databases contain functional schema and data clearly comprehensible by the report’s authors. Actually, data in the reporting database is close to the one in the elastic pool, but we perform aggregations to deliver more specific, ready-to-use measures to travel managers.
In addition, in order to use Power BI Embedded in Azure, we created a workspace collection with a workspace to host our reports. Our website is hosted in a web app, a feature of Azure App Service.
You can see an overview of this architecture below:
The aim of the first version of our Power BI Embedded solution is to provide interactive reports for travel managers. These reports need to satisfy customers' requests for travel statistics and answer specific questions about the use of the online travel reservation system. To avoid creating specific reports for each situation, we decided to give travel managers a tool to allow them to browse and analyze data in real time.
We used the newly created reporting database to connect Power BI Desktop to it and create reports. We had to whitelist the IP address of the development machine in the firewall before we could successfully authenticate with Power BI Desktop.
Once the configuration is done, we connect to the database with Power BI Desktop. We set up the connection to use "DirectQuery" to be sure to get up-to-date information when we connect to the embedded report:
Below is a sample of our star schema:
2. Create reports
We worked with a customer to get exact expectations of reports he would like to have. He asked us to provide first a dashboard as a global overview, then several additional reports with detailed insights related to travel characteristics and details.
Below is a screenshot of the dashboard:
We also added several detailed reports like the one below:
3. Manage security
To manage security, we decided to use Row Level Security provided by Power BI. First, we modified our application code to get username and role to provide during the Power BI Embedded token creation process:
We also managed roles in our reports to filter data according to whom is connected. We did it directly in Power BI Desktop:
4. Publish report to the workspace
There are several methods to publish a report in a Power BI Embedded workspace:
- Using the one provided in the sample app: Sample app
- Using the Power BI command line tool: Power BI Command Line Tool
We used the command line tool to publish our reports in the workspace. Below are steps and screenshots of the process:
-
First we had to install node.js.
-
Then we installed a Power BI node.js package using the command prompt:
npm install powerbi-cli -g
-
And finally, we used the command "powerbi - import" as shown in the screenshot below:
Just for reference, to find needed information such as workspaceID, we went to the Azure portal:
5. Use the report in the web app
Now that the report is published in the workspace, our web app can access it. We used the Power BI JavaScript SDK to integrate the report into our web application. We used this method to allow us to customize our report according to the customer's corporate identity and style guide. We connected with a user who can access the report.
Below is a screenshot of the website with the report embedded:
Conclusion
We have implemented the V1 of our reporting solution for travel managers. During the design process, we talked with one of our travel managers to get feedback on his main pains. We also demonstrated the capabilities of Power BI Embedded, and this gave the travel manager ideas. He quickly saw the benefits of using interactive reports to navigate across data to do analysis according to his customers' needs. For travel managers, Power BI Embedded will save them time and decrease response time to their customers.
From the discussion with the travel manager, we were able to provide reports that closely match his needs and at the same time provide an ability to interact dynamically with the reports.
We also took care to use the Power BI Embedded SDK in a way that allows us to reuse parts of our code in another application. We also created an application to extract data to our database's elastic pool to populate the reporting database. We made this application as generic as possible to allow us to reuse it to populate another database if needed.
We expect to deploy Power BI Embedded to all the travel managers at this pilot agency, and then to follow with all interested travel agencies. We think that will give customers a better experience with their own data.
Last but not least
The project team picture!
