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

Examples to help you differentiate between Business Intelligence and Data Science problems:

In this post, I’ll list few examples from various industries to help you differentiate between business intelligence and data science problems.

Sometime back, I blogged about “Business Analytics Continuum” and in the post we saw that Every Organization has DATA but they use their business data at different levels because of their maturity level. Excel (or other transactional reporting tools) is usually the starting point for any organization – it helps them see WHAT happened. They advance to the next stage, where they get capabilities to slice and dice their data – To find out WHY – and usually this capability is delivered using Business Intelligence tools & techniques. Once the data culture spreads – Thanks to a successful Business Intelligence project – then they soon start to outgrow their business intelligence capabilities by asking problems that need predictive capabilities. This is advanced analytics and Data Science stage. To that end, here are 5 examples to help you differentiate between business intelligence and data science problems:

Business Intelligence.(WHAT & WHY) Data Science & advanced analytics.
Bike Rentals
  1. How many bikes did we rent in Q3 2014? How does that compare to Q3 2013?
  2. What is the trend of total bike rentals at week level? Can you break it down by geography?
Can you predict bike rentals on an hourly basis?
Credit Risk
  1. How many customers have a credit risk of ‘C’?
  2. Can you rank customers by their payments due amount that have a credit risk ‘C’?
Can you predict the credit risk of the customer during contract negotiations stage?
Customer relationship management
  1. How many account cancellations occurred this year (broken down by month and customer segmentation)?
  2. How does percentage of account cancellations this year compare to that previous year?
 Can you predict customer churn?
Flight Delays
  1. What is the trend of % of flight delayed this year?
  2. Can you break down flight delays this year by their reasons?
Can you predict whether a scheduled flight will be delayed by more than 15 minutes?
Customer feedback
  1. What is the customer satisfaction % trend this year?
  2. What is the customer satisfaction % broken down by customer segments and product segments?
Can you classify a customer feedback comment into “positive”, “negative” or “neutral”?

I hope this helps!

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.

SQL Server Reporting Services Tip: How to capitalize just the first letter of text?

Attention to detail is a key in creating SSRS reports/dashboards that look like a work of a professional; To that end, here’s a tip: How to capitalize the first letter in your string? In other words, how to Camel Case the Text?

Here’s the function that you can use in your SSRS Expressions:


StrConv("hello world",3)

OR

StrConv("hello world",vbProperCase)
Input Function Output
hello world StrConv(“hello world”,3) Hello World

I hope that helps!

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.

How does Internet of Things (#IoT) impact data professionals?

Internet enabled computers to be connected with each other.

Internet enabled Mobile Devices to be connected with each other.

Now, Internet will be used to enable physical things to be connected with each other. This is what is called “Internet of things” (IoT).

So what happens?

since more devices are connected with internet – we will able to generate more data! This is usually good if there’s a business vision around how to make sense of data to increase efficiency of all these things.

Here’s a nice case study from Microsoft (focus on the business case – the things in this case is “elevator” to drive reliability)

 

This is all good news for data professionals! There will be increased demand for professionals who can help businesses make sense of data generated via IoT.

Also beware of the “hype” around this technology. It’s important to take incremental steps to achieve the vision – Instead of trying to analyze data from ALL devices in your organization, start with one physical thing that matter the most for your organization or start with data that you have and take incremental steps to spread data culture in your organization!

Now that Big Data has become a mainstream word in IT and business, we have a new buzzword to learn/talk about IoT – but remember it’s all about making sense of data and your skills would be more valuable than ever!

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

How to create an Average Aggregation in SQL Server Analysis services?

Problem:

How do create a measure that does an average over a field from fact table? You can’t find it the “usage” property while trying to create a new measure:

SQL Server Analysis Services Average Aggregation

Solution:

Before i show you the solution, I want you to know that this is a Level 100 solution to get you started – so depending on the complexity of your cube the calculated measure that you are about to create may or may not perform well – if it does not perform well, you might have to dig a little deeper and here’s one blog post to get you started: URL

OK, back to topic! Here are the steps.

SCENARIO: you need average of Sales Amount.

1. Create a SUM OF SALES AMOUNT measure

Steps: Open cube > Cube Structure > Right click on Measure Group > New Measure > Usage: “SUM” > Source Table: Pick your Fact Table. In this case let’s say it’s Fact Sales > Source Column: In this case, lets say it’s SALES AMOUNT

2. Create a COUNT OF SALES measure (important: row count vs. non empty count – this is not a developer’s choice, a business user needs to define that)

Steps: Open cube > Cube Structure > Right click on Measure Group > New Measure > Usage: count of rows OR count of non empty values (again this is not developer’s choice, a business user needs to define this) > Source Table: Pick your Fact Table. In this case let’s say it’s Fact Sales > Source Column: In this case, lets say it’s SALES AMOUNT

3. Create a Calculated Measure that equals (SUM OF SALES/COUNT OF SALES)

3a. Switch to Calculations section > create a new calculated member:

SSAS Analysis services new calculated measure

3b. Complete Name, Format String & Associated Measure Group. For the Expression, use the following expression. Please use this as a starting point for your measure:

IIF([measures].[COUNT OF SALES]=0,0,[measures].[SUM OF SALES AMOUNT]/[measures].[COUNT OF SALES])

4. Before you test it, if you don’t need the SUM OF SALES AMOUNT and COUNT OF SALES measures than don’t forget to hide them!

Conclusion:

In this post, you saw how to define a measure with average aggregation is SSAS.

SQL Server Analysis services warning: “The name specified for the attribute relationship differs from the name of the related attribute”

In this post we will see how to address the SSAS warning message: “The name specified for the attribute relationship differs from the name of the related attribute”, it’s not a critical waning but it’s always good to make sure that these warnings are addressed before going to production.

Usually this happens because attribute names were renamed after the relationships between attributes had already been defined. 

To fix the warning messages:

1. Go to Attribute Relationships section for the dimension.

2. In the lower right corner, you should find list of relationships.The ones that cause the warning would have a blue squiggly line with a warning symbol on the arrow (example shown below):

ssas attribute relationships cube dimension3. Right Click on the Relationship > Go to Properties > Change the Name property to the new renamed name that you gave to the attribute – it should be what’s shown in the Attribute property.

ssas analysis services attribute relantionship propertiesThat’s it, this should fix the ssas warning message now since the name specified for attribute relationship would now match related attribute.