A key driver for business intelligence adoption: Embedded analytics.

Did you know most business intelligence (BI) solutions are under-utilized? Your BI solution might be one of them — I definitely had some BI solutions that were not as widely used as I had imagined! Don’t believe me? Take a guess at “number of active users” for your BI solution and then look up that number by using your BI server logs. Invariably, this is Shocking to most BI project leaders = Their BI solution is not as widely used as they had imagined! Ok, so what can you do? Let me share one key driver to drive business intelligence adoption: Embedded analytics.

Embedded analytics

#1: what is Embedded analytics? 

Embedded analytics is a technology practice to integrate analytics inside software applications. In the context of this post, it means integrating BI reports/dashboards in most commonly used apps inside your organization.

#2: why should you care? 

You should care because it increase your business intelligence adoption. I’ve seen x2 gains in number of active users just by embedding analytics. if you want to understand why it’s effective at driving adoption, here’s my interpretation:

Change is hard. You know that — then why do you ask your business users to “change” their workflow and come to your BI solution to access the data that they need. Let’s consider an alternative — put data left, right & center of their workflow!

Example: You are working with a team that spends most of their time on a CRM system then consider putting your reports & dashboards inside the CRM system and not asking them to do this:

Open a new tab > Enter your BI tool URL > Enter User Name > Enter Password > Oops wrong password > Enter password again > Ok, I am in > Search for the Report > Oops, not this one! > Ok go back and search again > Open report > loading…1….2….3…. > Ok, here’s the report!  

You see, that’s painful! Here’s an alternative user experience with embedded analytics:

They are in their favorite CRM system! And see a nice little report embedded inside their system and they can click on that report to open that report for deeper analysis in your BI solution.

How easy* was that?

*Some quick notes from the field:

1) it’s easy for users but It’s not easy to implement! But well — there’s ROI if you invest your resources in setting up embedded analytics correctly!

2) Don’t forget context! example: if a user is in their CRM system and is looking at one of their problem customers — then wouldn’t it be great if your reports would display key data points filtered for that customer! So context. Very important!

3) Start small. Implement embedded analytics for one subject area (e.g. customer analysis) for one business team inside one app! Learn from that. Adjust according to your specific needs & company culture AND if that works — then do a broad roll out!

Now, think of all the places you can embed analytics in your organization. Give your users an easy way to get access to the reports. Don’t build it and wait for them to come to you — go embed your analytics anywhere and everywhere it makes sense!

#3: Stepping back

Other than Embedded analytics — you need to take a look at providing user support and training as well…And continue monitoring usage! (if you’re trying to spread data driven culture via your BI solution then you should “eat at your own restaurant” and base your adoption efforts on your usage numbers and not guesses!)


In this post, I shared why embedded analytics can be a key drive for driving business intelligence adoption.

How to assign same axis values to a group of spark-lines in Excel?

Spark-line is a very handy data visualization technique! It’s great when you are space constrained to show trends among multiple data points.

Here’s an example:

Spark Line Trend Excel Data Visualization

But there’s an issue with above chart! Axis values for these group of spark-lines do not seem match – it could throw someone off if they didn’t pay close attention. So a good practice – when you know users are going to compare segments based on the spark-lines – is to assign them same axis values so it’s easier to compare. Here’s the modified version:

Excel Sparkline data visualization same axis

And…here are the steps:

1. Make sure that spark-lines are grouped.

Select the spark-lines > go to toolbar > Sparkline Tools > Design > Group

Excel Sparkline Group

2. On the “group” section, you’ll also find the “Axis” option – select that and make sure that “same for all axis” is selected for Vertical axis minimum and maximum values:

Excel Spark Line Data Viz same min max value


That’s about it. Just a quick formatting option that makes your spark-lines much more effective!

Author: Paras Doshi

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.


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

Author: Paras Doshi

#sqlpass webinar: “Data Analytics Explained for Business Leaders” on 1/15

A quick blog post to let you know about a #sqlpass webinar on 1/15.

Data Analytics Explained for Business Leaders

Thu, Jan 15 2015 12:00 (UTC-05:00) Eastern Time (US & Canada)

RSVP: http://bit.ly/PASSBAVC011515


Description: The world is becoming more efficient. Today, seventy percent of the companies that graced the Fortune 1000 list a mere decade ago have vanished. Agility and survival are function of innovation, culture, and the ability to predict the future. To that end, data analytics offers a lifeline, a means of survival that will drive productivity and continue to disrupt and redefine business. However, the resources available to today’s business leaders sit on two vastly different ends of the spectrum. On the one hand, highly technical academic resources and on the other largely fluffy overviews of value propositions and potentials. The state of the industry shouldn’t be surprising. The same dynamics played out in early years of the internet. Software providers, technical leaders, and consulting firms greatly benefit from mystifying the world of data analytics into something that is incomprehensible. That lack of conceptual understanding is incredibly risky and propels the cost of analytics initiatives upwards. This webcast aims to bridge that gap between the technical data scientists and business leaders. Ultimately, this understanding will help to: – Connect the strategic goals of business leaders with the capabilities of technical advisers – Focus investments and initiatives within analytics and technology – Distill immensely complex subject matter into comprehensible examples – Accelerate the path to value and increase the ROI of analytics initiatives

Speaker Bio

Alex is a Predictive Analytics Architect in the Oil and Gas industry with a passion for distilling complexity into insights and evangelizing data science. His work has been featured on KDNuggets and he was recognized by DataScienceCentral as a top 180 blogger in 2014.

RSVP: http://bit.ly/PASSBAVC011515

I hope to see you there!

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


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!


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/

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

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:

  MEMBER [Measures].[Sales_last_15_days] AS
          [Calendar].[Date YYYYMMDD].CurrentMember.Lag(14)
          [Calendar].[Date YYYYMMDD].CurrentMember

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


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)


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

I hope that helps!