In my last blog post we talked about Power BI Designer Preview and why I was lovin’ it. In this post, I’m going to use Microsoft’s most recently Power BI tool to connect to my SQL server instance to analysis my Google Analytics data, Marketing data and CRM opportunities.
Connect to your SQL Data
Let’s get started by selecting the Get Data button in the top left corner and then select SQL Serve.
Supply your SQL sever name and database that you would like to connect to.
Select with tables you would like to use for your dashboard/report. I’m going to use my CRM, Google Analytics and KTLMarketing.
Transforming your Data
At this point we can edit the query before loading the table, by selecting Edit from the bottom of the window, or we can load the table(s) and then select each table to transform the data.
Now that we’ve connected to a data sources, we need to adjust the data to meet our needs.
The Query view in Power BI Designer makes ample use of right-click menus, in addition to having tasks available on the ribbon. Most of what you can select in the Transform ribbon is also available by right-clicking an item (such as a column) and choosing from the menu that appears.
When you transform data in the Query view, you’re providing step-by-step instructions (that Query carries out for you) to adjust the data as Query loads and presents it. Clicking on the View tab allows you to select the option for the formula bar so you can enter in or modify the DAX formulas with your transformation. The original data source is not affected; only this particular view of the data is adjusted.
The steps you specify are recorded by Query in the Applied Steps navigation pane, and each time this query connects to the data source those steps are carried out so that the data is always shaped the way you specify. This process occurs whenever you use the query in the Power BI Designer, or for anyone who uses your shared query, such as on the Power BI Service. Those steps are captured, sequentially, in the Query Settings pane under Applied Steps. Reversing your applied transformation is as simple as clicking on the ‘x’ to the left of the step.
For starters, I’m going to replace the null values with zero. I just right click the column header, select Replace Values.
Type null in the Value To Find field and 0 in the Replace With field and then click the OK button.
Additionally, I’m going to remove several columns that were imported within the KTLMarketing table by highlighting them and then right clicking and Remove Columns.
I’ve completed the transformations to my data that I want to do for now. I can always do additional transformations later by selecting Query and then the table that I want to work with.
Building Your Visual Dashboard/Report
I’m going to start my first report by looking at Google Analytics data. I drag sessions by date, page views by date and page views by channel grouping. I like what I have started but want to compare sessions to page views. To do that I simply select page views by date and drag and drop it on top of sessions by date.
Next I want to add some of our KTL Marketing data to the Google Analytics report. I select the KTLMarketing data set and drag the Blog post views by author, LinkedIn views by author and Twitter by authors statistics. What I am left with below is a clear prospective of where my traffic to my website is coming from and which employee is providing the most relevant posts with each social media channel that I’m analyzing.
Next I’m going to start a new report by right clicking a blank space in the Reports navigation pane to the left.
Next I’m going to select my actual value by customer, estimated and actual value by opportunity owner and then count of opportunities by name. I’m going to use this data to analysis who are best customers are, which opportunity owners do the best at estimating the opportunity and who is the best preforming employee and then I can use the opportunity name to see if there is any correlation between blog posts and the opportunities.
This is just a start into analytics using Power BI Designer Preview and is specific to our business needs. There is so much more that you can do with it and the best thing of all is its FREE!!!!! As always contact KTL Solutions to help kick starting your organization into becoming data driven organization by exploring your Business Intelligence, BI360, Power BI, Microsoft Dynamics GP and CRM needs.
BARRY CROWELL, MBA | BI/EDW Solution Architect
Barry uses his expertise to lead and teach a team of GP consultants to assure our solution scoping, architecting, and delivery meet and exceed the customer’s expectations. He helps clients analyze their current business processes and provides them with process improvements. He has performed implementations as the lead consultant and end-user project manager. Barry has over 20 years of experience working in accounting and the Microsoft Dynamics GP industry. He also possesses a bachelor’s degree in accounting and business administration from Black Hills State University and a master’s in business administration from La Salle University.
