Every Data Analyst Needs to check out this FREE excel add-in: Power Query!

Power Query is amazing! It takes the data analysis capabilities of Excel to whole new level! In this post, I am going to share three reasons:

1. it enables repeatable mash-up of data!

Have you every had to do your data analysis tasks repeatedly on the data with same structure? Do you get “new” data every other week and need to go through the same data transformation workflow to get to the data that you need?

What’s the solution? Well, you can look at MACRO’s! Or you can request your IT department to create a Business Intelligence platform. However, what if you need to modify your data mashup workflow then these solutions don’t look great, do they now?

Don’t worry! Power Query is here!

It enables repeatable mashup of data like you might have never seen before! You need to try it to believe.

It’s very easy to input new data to Power Query and it enables you to retrieve final output based on new data using a “refresh” feature.

Each data-mashup is recorded as steps which you can go back and edit if you need to.

Power Query Refresh

2. It’s super-flexible!

Any data mashup performed using Power Query is expressed using its formula language called “M”. You can edit the code if you need to and as you can imagine such a platform enables much-needed flexibility for the analyst’s.

3. It has awesome advance features!

Do you want to Merge data? How about Join? Are you tired with VLOOKUP’s! Don’t worry! it’s super easy with Power Query! Here’s a post: Join Excel Tables in Power Query

How about Pivot or Unpivot? Done! Check this out: Unpivot excel data using Power Query

How about searching for online & open data sets? Done!

How about connecting to data sources that “Data” section of Excel doesn’t support yet? (Example: Facebook) – DONE! Power Query makes that happen for you.

And That’s not a complete list!

Plus you can unlock the “Power” (pun intended) of Power Query by using it with other tools in Power BI Stack. (Power Pivot, Power View, etc…) OR you can use the your final output from Power Query with other tools too! After all it’s an excel file.

Action-Item!

If you haven’t already then check out Power Query! it’s free and works with Excel 2010 and above.

Author: Paras Doshi

Top two key techniques to analyze data:

There are many techniques to analyze data. In this post, we’re going to talk about two techniques that are critical for good data analysis! They are called “Benchmarking” and “Segmentation” techniques – Let’s talk a bit more about them:

1. Benchmarking

It means that when you analyze your numbers, you compare it against some point of reference. This would help you quickly add context to your analysis and help you assess if the number if good or bad. This is super important! it adds meaning to you data!

Let’s look at an example. CEO wants to see Revenue numbers for 2014 and an analyst is tasked to create this report. If you were the analyst, which report would you think resonated more w/ the CEO? Left or Right?

Benchmarking data providing context in analysis

I hope the above example helped you understand the importance of providing context w/ your data.

Now, let’s briefly talk about where do you get the data for benchmark?

There are two main sources: 1) Internal & 2) External

The example that you saw above was using an Internal source as a benchmark.

An example of an external benchmark could be subscribing to Industry news/data so that you understand how your business is running compared to similar other businesses. If your business sees a huge spike in sales, you need to know if it’s just your business or if it’s an Industry wide phenomenon. For instance, in Q4 most e-commerce sites would see spike in their sales – they would be able to understand what’s driving it only if they analyze by looking at Industry data and realizing that it’s shopping season!

Now, let’s shift gears and talk about technique #2: Segmentation.

2. Segmentation

Segmentation means that you break your data into categories (a.k.a segments) for analysis. So why do want to do that? Looking at the data at aggregated level is certainly helpful and helps you figure out the direction for your analysis. The real magic & powerful insights are usually derived by analyzing the segments (or sub sets of data)

Let’s a look at an example.

Let’s say CEO of a company looks at profitability numbers. He sees $6.5M and it’s $1M greater than last years – so that’s great news, right? But does that mean everything is fine and there’s no scope of optimization? Well – that could only be found out if you segment your data. So he asks his analyst to look at the data for him. So analyst goes back and after some experimentation & interviews w/ business leaders, he find an interesting insight by segmenting data by customers & sales channel! He finds that even though the company is profitable – there is a huge opportunity to optimize profitability for customer segment #1 across all sales channel (especially channel #1 where there’s a $2M+ loss!) Here’s a visual:

segmentation data Improve profitability low margin service offerings customers

I hope that helps to show that segmentation is a very important technique in data analysis!

Conclusion:

In this post, we saw segmentation & benchmark techniques that you can apply in your daily data analysis tasks!

Five actions that you can take if you measure your analytics/business-intelligence solution usage:

Summary:

In this post, I am going to share five actions that you can take you if measure your analytics/business-intelligence solution usage:

Five actions!

I’ll highly encourage business stakeholders & IT managers to consider measuring the usage of their analytics/business-intelligence solutions. From a technical standpoint, it shouldn’t be a difficult problem since most of the analytics & business intelligence tools will give you user activity logs. So, what’s the benefit of measuring usage? Well, in short, it’s like “eating at your restaurant” – if you’re trying to spread culture of data driven decision-making in your organization, you need to lead by example! And one way you can achieve that is by building a tiny Business Intelligence solution that measures user activity on top of your analytics/business-intelligence solution. if you decide to build that then here are five actions that you can take based on your usage activity:

Let’s broadly classify them in two main categories: Pro-active & Reactive actions.

A. Pro-active actions:

1. Identify “Top” users and get qualitative feedback from them. Understand why they find it valuable & find a way to spread their story to others in the organization

2. Reach out to users who were once active users but lately haven’t logged into the system. Figure out why they stopped using the system.

3. Reach out to inactive users who have never used the system. it’s easy to find inactive users by comparing your user-list with the usage activity logs. Once you have done that, Figure out the root-cause – a. Lack of Training/Documentation b. unfriendly/hard-to-use system c. difficult to navigate; And once you have identified the root-cause, fix it!

B. Reactive actions:

4. If the usage trend if going down then alert your business stakeholders about it and find the root-cause to fix it?

Possible root causes:

– IT System Failure? Fix: make sure that problem in the system never happens again!

– Lack of documentation/Training? Fix: Increase # of training session & documentation

downward trend line chart

5. It’s a great way to prove ROI of an analytics/business-intelligence solution and it can help you secure sponsorship for your future projects!

Conclusion:

In this post, you saw five actions that you can take if you measure your usage activity of your analytics/business-intelligene solution.

I hope this was helpful! I had mentioned user training in this article and so if you want to learn a little bit more about it, here are a couple of my posts:

1. http://parasdoshi.com/2014/05/05/presented-at-sqlsat-305-dallas-ba-edition/

2. http://parasdoshi.com/2014/05/07/how-to-train-your-users-to-create-their-own-business-intelligence-reports-5-of-5-post-training/

Business Intelligence system – Customer Complaints – B2B company:

Business Intelligence system – Customer Complaints – B2B company:

Analyzing customer complaints in crucial for customer service & sales teams. It helps them increase customer loyalty and fix quality issues. To that end, here’s a mockup:

Note: Drill down reports are not shown, details are hidden to maintain confidentiality and numbers are made up.

Customer complaint dashboard quality feedback

Sales Bookings vs Quota Dashboard for a B2B company:

Sales Bookings vs Quota Dashboard for a B2B company:

Business Goal:

Need a daily report delivered in sales team’s inbox that shows Sales Team’s Bookings vs Quota for current & next month.

Brief Description:

Ability to see Bookings vs Quota in near real-time is a key to effectively manage performance for any sales team. Before the project, analyst(s) would have to manually put together this report and since the report took more than a day to put together they couldn’t afford to run it daily and so they delivered this report bi-weekly/monthly basis to the sales team. After the project, the process was automated and the sales team received an email with a report on a daily basis and this helped them see Bookings vs Quota in near real-time. As a famous saying goes “if you can’t measure it, you can’t improve it” (by Peter Drucker) – in this case, the report helped them measure their actual numbers against their goals and helping them improve their sales numbers which directly hits their top-line!

Tools used: SharePoint report subscription, SQL server analysis services, SQL Server Integration services, SQL server reporting services & Excel.

Mockup:

Note: Drill down reports are not shown and the numbers are made up.

Sales Team bookings vs quota dashboard

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

Time Intelligence in MDX: last N days

it’s a common requirement to create a report that shows last N days of a business metric – so I thought I’ll post a template here for SQL server analysis server’s MDX query:


WITH
  MEMBER [Measures].[Sales_last_15_days] AS
    Sum
    (
      {
          [Calendar].[Date YYYYMMDD].CurrentMember.Lag(14)
        :
          [Calendar].[Date YYYYMMDD].CurrentMember
      }
     ,[Measures].[Sales]
    )

   MEMBER [Measures].[CurrDate] as
      "[Calendar].[Date YYYYMMDD].[" + Cstr(Year(Now())*10000+month(now())*100+day(now()))  +"]"

SELECT
  {
     [Measures].[Sales_last_15_days]
  } ON COLUMNS
FROM 
[CubeName]
WHERE
STRTOMEMBER([Measures].[CurrDate])

Here are things that you’ll need to adjust to make it work for your scenario:

1. Date Dimension Attribute & it’s format. The example shows yyyymmdd but you could have different format of the date.

2. Measure name. Instead of [Measures].[Sales] you’ll have to replace it with your business metric. Also, make sure you are using the right aggregate function, in the example above I have used SUM but you’ll have to change this based on your requirement.

3. Create a parameter and use it in index for the Lag function.

4. change [cubename] to your cube name.

I hope this gives you a good starting point to create last N days for your business metric.

Back to basics: Design your Business Intelligence system to have lowest level data even if it’s not asked!

Here’s a scenario:

A Business Intelligence (BI) system for Sales is being developed at a company. Here are the events that occur:

1) Based on the requirements, It is documented that the Business needs to analyze Sales numbers by product, month, customer & employee

2) While designing the system IT learns that the data is stored at each Invoice Level but since the requirements document doesn’t say anything about having details down to invoice level, they decide to aggregate data before bringing in their system.

3) They develop the BI system within the time frame and sends it to business for data validation.

4) Business Analysts starts looking at the BI system and finds some numbers that don’t look right for a few products and need to see Invoices for those products to make sure that the data is right so they ask IT to give them invoice level data.

5) IT realizes that even though business had not requested Invoice Level data explicitly but they do NEED the lowest level data! They realize it’s crucial to pass data validation. Also, they talk with their business analysts and found out that they may¬† sometimes need to drill down to lowest level data to find insights that may be hidden at the aggregate level.

6) so IT decides to re-work on their solution. This increases the timeline & budget set for the project. Not only that they have lost the opportunity to gain the confidence of business by missing the budget and timeline.

7) They learn to “Design BI system to have the lowest level data even if it’s not asked!” and decides to never make this mistake again in the future!

This concludes the post and it’s important to include lowest level data in your BI system even if it’s not explicitly requested – this will save you time & build your credibility as a Business Intelligence developer/architect.

Business Intelligence Dashboard for Inventory management for a manufacturing organization:

Business Intelligence Dashboard for Inventory management for a manufacturing organization:

Mockup:

BI system allows the analysts & operational specialists to drill down to the lowest data available but here’s a dashboard for executives & Sr. managers:

Inventory Management Business Intelligence Manufacturing

How to Configure SQL Server Analysis services’s Action to Open an URL?

SSAS Actions are powerful! You can open web pages, open sql server reporting services, customize drill through reports among other things using actions. In this post, you will see a common requirement from users to navigate to a corporate intranet site from the cube – and usually it needs to be dynamic.

For example, user is interested in seeing the Order Entry Page hosted on the corporate intranet site by using the Order ID from the SSAS cube.

Here’s how you can set it up:

1. Open SSAS Cube in SQL Server Data Tools:

2. Navigate to Actions tab:

ssas url action analysis services sql server web page

3. Here you’ll see three types of action that you can configure

a. Standard (this have five subtypes including the URL action)

b. Drill Through

c. report action

4. For the purpose of this blog post, let’s focus on standard action:

ssas url action analysis services sql server web page5. Once you click on the “New Action” it will ask you to configure the action:

a. Name: Enter the desired name here

b. Target Type: In this case, Order ID is an attribute member but you will have to choose appropriate target type for your scenario

c. Target Object: In this case, it’s something like [Order].[Order ID] – in your case, you’ll have to choose an appropriate target object

d. Type: URL in this case (also don’t forget to check books online for what other types can do as well)

e. Action Expression: the format of the Action Expression if it’s driven by a parameter would go something like:

"http://servername/site/Pages/OrderRef.aspx?Search&ID="+[Order].[Order ID].currentmember.member_caption

f. Additional Properties: I like to set the Caption to clearly indicate the user that they are opening the “Order Form for Order ID 123999″. You can do that by setting the caption property. The format goes like this:

"Open Order Entry page for Order ID: "+[Order].[Order ID].currentmember.member_caption

Also set the caption is MDX to True if you are using above format.

That’s about it, don’t forget to test it (after deploying the cube) using excel or other end-user tool of your choice. In the Pivot Table, use the Order ID attribute in Row/Column labels > Right Click on any attribute member of Order ID attribute > Additional Actions > The caption with dynamic order id should show by here for users to click and navigate to the specified URL:

excel ssas url action analysis services sql server web page