๐ŸŽ‰ Bedrock Data is now Formstack Sync! ยท Learn More ๐ŸŽ‰

Jeremy Martin | April 20, 2018

Analyze and Visualize Your Fused Database in Microsoft Power BI

 

Overview of Power BI

Power BI is a is a business analytics service provided by Microsoft. It provides interactive visualizations with self-service business intelligence capabilities, where end users can create reports and dashboards by themselves, without having to depend on information technology staff or database administrators. You can use Power BI as either a desktop or web application. This article will focus on Power BI desktop, since it's this version that contains the entire suite of tools needed to build data models. 

Like most analytics platforms, Power BI allows you to connect to an array of data sources. Although unlike most business intelligence tools, users needn't know SQL to report out of Power BI. Instead, Power BI's lets anyone drag and drop visualizations onto its interface canvas.

For more complex analysis there are DAX measures. Short for Data Analysis Expressions, DAX is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. That is, DAX creates new information from data already in your model. For example, in the expression Total Sales = SUM(Sales[SalesAmount]), you've created a measure called Total Sales that you can now visualize.

powerbi-desktop-hero.gif

Connecting Your Fused Data Warehouse to Power BI

Connecting your Fused Data Warehouse to Power BI is straightforward. From the Home menu, you just select Get Data, which will import data from a new source.

Screen Shot 2018-04-19 at 3.39.49 PM When the dialog box opens, choose MySQL from the list of data source options.

Screen Shot 2018-04-19 at 3.41.15 PM

Enter your Fusion Warehouse credentials, which you can access by logging into Fusion and clicking Get Credentials.

Screen Shot 2018-04-20 at 9.30.01 AM

Feel free to copy or download your warehouse credentials as a .csv file.  Screen Shot 2018-04-20 at 9.31.41 AM

When creating a connection, you could select everything. But if you know what kinds of analyses you want, it's better to only load the relevant tables because Power BI actually loads the data locally, which means refreshing data or visualizations in Power BI could crash, error, or be slow to load if you've selected all the tables in your Fusion Warehouse. Reason being, Power BI is importing the data locally and if you don't have enough memory, the hang time could have you pounding the keyboard. As a best practice, just add the tables you need rather than an entire warehouse worth of data. 

So below I'll just select the ones I know I'll be using for my analysis โ€” fused_company, fused_contact, and fused_opportunity.

Screen Shot 2018-04-20 at 10.11.11 AMThe one finicky thing about Power BI is that you'll need to go to Manage Relationships to include your links tables (recall that the links tables will tell Power BI how to join objects such as companies, contacts, and opportunities). In other words, Power BI knows that so far we only have the fused tables for Company, Contact, and Opportunity, so we have to select the corresponding links tables.

Screen Shot 2018-04-19 at 4.13.37 PM

Now if you try to create a simple visualization, you might run into this error 'Can't display the visual' and wonder, as we did, just how best to resolve this. Screen Shot 2018-04-19 at 4.01.41 PM

The best route is to click Edit in the Manage Relationships dialog box to ensure that each links table is connected to the proper fused table.

Have a look at the example below. Here we have the links_company_contact table tied to the fused_contact relationship. Make sure that for the Cross filter direction you've selected Both and that the relationship is set to active. You're going to have to edit the relationship.Rinse and repeat for all your relevant tables.

Screen Shot 2018-04-20 at 10.27.01 AM

In SQL parlance, this is similar to a join and Power BI will display these relationships in its data model. So in case you want to be sure you've set everything up correctly, you can visualize the model relationships.

Screen Shot 2018-04-19 at 4.03.17 PM

 

Drag-and-Drop and Drill-Down 

At this point we can see the data, with our tables and fields on the right.

Now for the fun part.

In this example we selected Contacts, Companies, and Opportunities data from our Fused Warehouse. So let's pretend we wanted to drill down into all our sales deals over time, broken down by business unit. We could drill down from year to quarter. And if we care about months instead, in the Visualizations panel we can add whatever level of specificity to the Axis.

Screen Shot 2018-04-19 at 4.07.06 PM

As we'll see, there are a lot of ways to slice and dice this data. We could select the closed_date, amount, and territory, where the territory originates from the Company. All we'd need to do is drag these fields in the order that makes sense for our analysis. We can now visualize our deals by territory and break them down by whatever business unit we want (like engineering or marketing). 

Power BI's drill down function is perfect for the Fused Warehouse. After loading data from multiple SaaS applications by the objects (Company, Contact, Opportunity) we care about, we can tweak the data however we want. And any adjustments we make to one chart will pipe these changes into whatever other corresponding charts we have in the canvas. 

As you go about your analysis, try to add other types of fields (like business_unit or deal_stage) to see how they add dimensions to the data. You can then alter the Visualization to better suit the dimensions you add or subtract.

changeviz

One cool trick is to move the dimension from the Legend into the Axis field so that the field becomes yet another drill-down dimension. So if we were interested in seeing how many of our deals are associated with each business unit, our visualizations could drill down to the different deal stages (MQL, closed-won, etc.) within the different business units (sales, marketing, etc.).

Screen Shot 2018-04-19 at 3.59.47 PM

Getting familiar with your data with a drag-and-drop and drill-down approach is the ideal way to start telling a story with your data. In the beginning, you explore the data to answer a specific question or glean some interesting highlights about a point in time, customer segment, or business function. And the more you do this, the more creative you'll become in illustrating customer journeys.

Getting started with Fusion is easy. It takes just four steps:

1. Connect Your Data Sources. Connect your data from SaaS applications such as Marketo, HubSpot, Salesforce and NetSuite. In just a few clicks.

2. Fuse Your Data. Automate data matching, de-duplicating, resolving data conflicts and modeling object relationships for more real-time reporting and dashboards. 

3. Warehouse Your Data Instantly. Automatically access and query your warehouse via a cloud data warehouse. 

4. Feed Analytics & BI tools. Enter your Warehouse Keys into your analytics platform and feed your favorite BI tools or dashboards -- including Tableau, Microsoft PowerBI, Amazon QuickSight, MetaBase, and more.

See for yourself what you can do with fused data.

 Unify Customer Data

Subscribe Here!