Live tweeted #sqlpass’s Business Analytics VC webinar: 13 Excel Tips!

I was live tweeting during our monthly PASS Business Analytics VC meeting, Here are the tweets to learn about 13 Excel Tips!

Thanks everyone who attended, I hope it was helpful!

Here are some ways to follow the Virtual Chapter:
Website: http://bavc.sqlpass.org/
Youtube: https://www.youtube.com/channel/UCOiRAA4gBxEeVxwmEZ1qy1w
Twitter: https://twitter.com/passbavc
LinkedIn: https://www.linkedin.com/groups/PASS-Business-Analytics-Virtual-Chapter-6701113

News from PASS Summit’14 for Business Analytics Professionals: #sqlpass #summit14

This post is a quick summary for all Business Analytics related updates that I saw at PASS Summit’14:

1. Theme of the Keynote(s)/Session(s) seemed to be around educating the community about the benefits of the NEW(er) tools. I saw demos/material for cloud-based tools like SQL databases, Azure stream analytics, Azure DocumentDB, AzureHDInsight & Azure Machine learning. The core message was pretty clear: A data professional does two things – 1) Guards data OR 2) helps to generate Insights from Data – And they will need to keep up-to-date on the new tools to future-proof their career.

Read more about this here: http://blogs.technet.com/b/dataplatforminsider/archive/2014/11/05/microsoft-announces-major-update-to-azure-sql-database-adds-free-tier-to-azure-machine-learning.aspx

2. Coming soon: Power BI will be able to connect to on-premise SSAS data sources (multi-dim & tabular).

3. Coming soon: A better experience to create Power BI dashboards.

Read more about Power BI updates here: http://www.jenunderwood.com/2014/11/05/pass-summit-2014-bi-news/

4. Azure Machine Learning adds a free-tier! You won’t need a credit-card/subscription to sign up for this.

5. I also saw sessions proposing new way of thinking about an architecture for “Self Service BI” and “Big Data” which might be worth following because since these are newer tools, it’s definitely worth considering an architecture that’s designed to make the most of the investments in these new tools. That’s it & I’ll leave you with a quote from James Phillips from Day 1’s keynote:

How to get descriptive statistics in Excel?

Problem:

you are analyzing a dataset and before modeling/analyzing you need to generate descriptive statistics on a field. you have the data loaded in Excel and wondered if there’s a way to do that in Excel.

Solution:

There’s an out of the box solution that will support your needs to generate descriptive statistics on a field. Here are the steps:

Note: for the purpose of this blog post, I am using Excel 2013 but data analysis toolpak is available in Excel 2007+.

1. Active “Data Analysis” toolpak.

Follow this steps:  File > Options > Add-ins > Manage: Excel Addins > “GO”

excel data analysis toolpak

2. make sure to check the “analysis toolpak” checkbox.

3. Now you should see a “data analysis” option under the “Data” pane:

Excel Data Analysis Descriptive Statistics

4. Now click on “Data Analysis” and select one of the following options:

Anova, Correlation, Covariance, Descriptive Statistics, Exponential Smoothing, F-Test Two-Sample for Variances, Fourier Analysis, Histogram, Moving Average, Random Number Generation, Rank and Percentile, Regression, Sampling, t-Test, z-Test.

in this case, let’s go with descriptive statistics but you can see that you can perform other tasks as well.

5. Once you click on the descriptive statistics, a dialog box will show up and you will have to enter some data like your input range to generate descriptive statistics. Once you have filled the data needed, click on OK and it should generate descriptive statistics for you in EXCEL!

I hope that helps!

Conclusion:

In this post, we saw how to generate descriptive statistics in Microsoft Excel.

Author: Paras Doshi

Cost Driver’s Dashboard for a Supply Chain Executive:

Cost Driver’s Dashboard for a Supply Chain Executive:

Summary:

Profitability equals revenue minus costs – To that end, A supply chain executive is mostly focused on optimizing cost elements to drive profitability. Here’s a mock up of a dashboard created for an executive to help him keep an eye on the overall health while making sure he gets alerted for key cost categories.

The Dashboard was created using profitability data-set & also had drill down capabilities to analyze numbers for cost buckets like Raw materials, manufacturing & logistics.

Mockup:

Supply Chain Cost Drivers Profitability Dashboard

Power Pivot: How to get Month Name from a date field?

Problem:

How do you get a Month Name from a date field in Power Pivot?

Solution:

here’s a code snippet that should help:

=FORMAT([date],"MMM")

This should give you month names (Jan, Feb, …) instead of integers that are returned by the MONTH function.

couple of notes:

1. date field needs to be used to get the month name

2. MMM needs to be in uppercase.

I hope this helps.

Back to basics: continuous Vs. Discrete variables and their importance in Data Visualization.

Take a look at the following chart, do you see any issues with it?

month trend chart line chart string to date

Notice that the month values are shown as “distinct” values instead of shown as a “continuous” values and it misleads the person looking at the chart.  Agree? Great! You already know based on your instincts what continuous and discrete values are, it’s just that we will need to label what you already know.

In the example used above, the “Date & Time” shown as a “Sales Date” is a continuous value since you can’t never say the “Exact” time that the event occurred…1/1/2008 22 hours, 15 minutes, 7 seconds, 5 milliseconds…and it goes on…it’s continuous.

But let’s say you wanted to see Number of Units Sold Vs Product Name. now that’s countable, isn’t it? You can say that we sold 150 units of Product X and 250 units of product Y. In this case, Units sold becomes discrete value.

The chart shown above was treating Sales Date as discrete values and hence causing confusion…let’s fix it since now you the difference between continuous and discrete variables:

Statistics Discrete Continuos Variable Data Visualization

Conclusion:

To develop effective data visualizations, it’s important to understand the data types of your data. In this post, you saw the difference between continuous and discrete variables and their importance in data visualization.

How to train your users to create their own Business Intelligence reports? #4 of 5: Sample Training Content

In part #1, I wrote about why is it important to enable business users to create their own BI reports.

In part #2, I wrote about three pre-training preparations – 1. Data 2. Tool 3. Understanding Culture.

In part #3, I wrote about 1. User Experience 2. Trainer 3. Training Content.

In this post, I am going share sample training content that uses Excel 2010. Before I share sample content, here are some tips

1. use YOUR data!

2. Show them the end goal & then walk through the steps to get there

 

Here’s a sample training content for a 4 hour-long excel training session (divided into basics & advanced) including hands on lab time.

Here you go:

GOAL:

Excel analysis services business intelligence dashboardBASIC-I

  1. Open the Template
  2. Explore the Field List
  3. Explain the concept of “dimensions” & “measures”
  4. Create a Simple Pivot Table – Row Labels & Measures
  5. Add column labels & report filters

Excel Pivot Table SSAS Step 1

Basic-II

  1. Sorting
  2. Turning off grant totals
  3. Creating a hierarchy
  4. Changing the Pivot Table Design

Excel analysis services business intelligence dashboard step 2

 

Advanced-I

  1. Remove fields from Pivot Table
  2. Add more than one pivot table
  3. Add slicer
  4. Connect slicer with every pivot table

Excel analysis services business intelligence dashboard step 3

Advanced-II:

  1. Add Pivot Chart
  2. Add one more slicer
  3. Add hierarchy structure to pivot tables
  4. Add conditional formatting
  5. Format chart

Excel analysis services business intelligence dashboard step 4

Conclusion:

In this post, I shared a sample training content that uses Excel 2010.

How to train your users to create their own Business Intelligence reports. #1 of 5: Why do it?

Introduction:

Business Intelligence (BI) helps an organization make faster & smarter decisions – There’s no doubt or debate over that! But every organization needs to go through the process of driving Business Intelligence adoption before they start seeing the Return on Investment for a BI solution. One of the form of BI which has been really successful at being adopted by business community is called “self-service BI” and the idea is to enable business users to create their own reports – in other words, “self-serve” their data needs. In this blog series, I would share the best practices that I’ve picked up while leading up an effort to train 200+ business users with the goal of enabling them to create their own BI reports.

In this post, let’s step back and talk about why is it important to enable business users to create their own reports?

#1: Information Technology’s Time & Budget.

The demand for “data” by business users is a great thing! But IT would need to dedicate resources to make it happen. usually, IT teams can be constrained due to budgetary reasons or due to other higher priority items on their radar. So what happens? Business community do not receive the data that they asked for in a timely fashion. And if you cannot offer data when they need it then it might not be as useful. So what’s the solution? One solution is to have “BI platforms” where data assets are readily available for business users to consume. So once they are build, IT would no longer have to dedicate development resources on them. And since IT is no longer a bottleneck, business users can get the data when they want it and however way they want to see it.

Time is money Information Technology Budget

#2: World changes. Business Changes.

World Changes and so does Business. If Business doesn’t adapt to change then they will die! And if Business changes, the data needs would change too. How many times does IT get “change requests” to their production reports? All of these requests would require IT’s intervention to accommodate change requests and what is the average turn around time in your organization for that? How about publishing data assets that businesses can readily access that let’s them see same data from varying perspectives? Of course, they might ask for data that’s not available yet but then IT can spend some time on bringing those data assets into the BI platforms rather than spending resources on “change requests”.

World Changes Business Changes Data Business Analytics Changes

#3: It’s great for BI adoption!

With the flexibility of Self Service BI solution, the control is in the hands of the users (again!). Without having to wait on IT, they can get to the data instantaneously and so faster and there’s a greater chance that they would use “data” in the decision making process. Isn’t it great?

Also, business users who would have created their own reports would share it with others, won’t they? And that would spark collaboration among business users. This is great because the recipients of the reports would start wanting to analyze the data too. And with time, the trust for the data assets would grow among the business community. This is really important for BI adoption.

Conclusion:

In this post, we saw why is it important to train your business users to create their own BI reports using a Self Service BI Platform. In next posts, we’ll get into strategies & tactics to enable users to create their own reports.

Questions Power Users Ask about Excel: #4 of N

In this series, I intend to document common questions asked by Power users about Excel connected to SSAS cubes (or data warehouse) after they go beyond the basic stage of understanding & using Row labels, column labels, report filter & values in Pivot Tables. This post is #4 of N:

a. How to Filter Measure Values?

Report Filters are a great way to put constraints on the data that is displayed. So once a user gets the concept of selecting dimensions as report filters & slicers, they might start wondering how to filter measure values? How do they show a list of customers that have ordered less than $100,000 worth of products? They might try to drag a measure to the report filter section but that won’t work & Excel will throw an error. So how can users filter measure values? it’s a feature of Pivot Table called “Value Filters” and that would be helpful here. Here are the steps:

1. Select the Pivot Table.

2. click on a drop down menu besides “row labels”

Excel Pivot Table Value Filter3. After that, go to Value Filters. You can see that you can filter by applying different rules like Top 10, less than, greater than, etc. Let me demo “Less than or equal to”

Pivot Table Excel SSAS cube filter values measure4. On the next dialog box, you can select the measure, rule & the filter value:

Pivot Table excel ssas cubeAfter you’ve set them up, click on ok and the Pivot Table will filter by measure values.

Note: Be careful when sharing the files that have value filters with other users, let them know that you’ve value filters set on the report as the value filters are not clearly visible if you’re looking at the file for first time.

b. How to move a Pivot Table?

If a user is trying to create a dashboard using excel, they’ll have more than one Pivot Table in a sheet. In that case, they’ll need to move the Pivot Tables around. here are the steps:

1. select the Pivot Table.

2. In the menu bar, go to Pivot table Tools > Move Pivot Table:

Pivot Table options Excel Move

3. select your NEW destination and click on OK.

Move Excel Pivot Table 2010That’s it!

Conclusion:

In this post, we saw how to filter measure values & how to move Pivot Tables.

In earlier articles we saw:

#1: How to sort data? How to add slicers? How to change Pivot table Layout

#2: How to add calculated measures?

#3: how to remove grand totals & subtotals and, how to configure the automatic data refresh.

Three Power Pivot Installation FAQ’s:

Q1: How Can I upgrade Power Pivot on my machine?

A1: Uninstall Existing version (Yes, it’s not intuitive, but you have to uninstall existing version). Download the version that you want to upgrade to. Install it.

Q2: I’ve SQL 2008 R2 on my machine. Can I install “Microsoft® SQL Server® 2012 SP1 PowerPivot for Microsoft Excel® 2010″?

A2: Yes, go ahead! “SQL server 2012 sp1″ in the name is confusing :) It doesn’t matter which version of SQL server you’ve on your machine – In fact, Power Pivot does not need a SQL server to be installed on your machine.

Q3: How do I check Power Pivot version?

A3: Excel > Power Pivot > Settings > You’ll see version # on the top part of the screen.

Power Pivot Settings Excel

I hope this helps!