Data Visualization Cloud Service cannot only create analysis based on the data available in applications as I explained in my previous post on DVCS. As adding Excel data sources, and combining the information with Sales Cloud data, is not possible using the Sales Cloud BI tools only, it was high on my list of things I wanted to try in DVCS. This is what this blog post is all about.
Adding Excel data sources
I created an Excel sheet with target information per industry. The goal of this experiment was to combine these targets with the open opportunity revenue information that I have in my Sales Cloud data source.
I started by creating a data source of type ‘From a File’.
I then selected my Excel sheet that I had already prepared and …
… in no time I had my new data source ready to be used.
Linking data sources together
I then had a Sales Cloud data source with opportunity information per industry and an Excel based data source with targets per industry. In order to use the industry targets, I just had to add the data source to my analysis.
Obviously these data sources were not aware of each other yet so. It is obvious to us that we can link them easily together through the industry field as that is the common information these data sources share, but DVCS needs to know that too. Here is how that is configured. Open up the ‘Source Diagram’ where relationships between data sources can be defined. Double click on the relationship and connect the sources by identifying matching columns between data sources as shown below.
Once the data sources were connected and I could drag the ‘Revenue Target’ to my analysis …
… and DVCS combined the data sources as expected, showing per industry the ‘Open Opportunity Revenue’ from Sales Cloud and the ‘Revenue Target’ from my local Excel sheet. Notice how the excel sheet contained industries for which Sales Cloud did not have open opportunity revenue information and how these are added anyway.
Adding calculated fields
As I had both data sources working together in my analysis, I wanted to create some sort of calculated field where I was using information from both. So I created a calculated field indicating how much the total open opportunity revenue in Sales Cloud was over or under the target value. Notice here how I could just drag and drop the fields into my formula and combine them with any of the available functions.
Once the calculated field was created, I dragged it onto my analysis and obtained the following results.
Reloading data
The last thing I wanted to try was change the Excel sheet and understand how DVCS would handle that. I removed all the industries lacking open opportunity revenue information Sales Cloud and I changed the target values for some of the remaining industries. All I had to do to was reload the data for my Excel based data source and my analysis changed accordingly.
My next post will all about making this analysis look a lot better 🙂
