How to train your users to create their own Business Intelligence reports? #3 of 5: User Experience, Trainer, 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 this post, part #3, I am going to write about three more important topics before you schedule trainings. Here they are:

1. user experience

2. Training Content

3. Trainer

Let’s talk about them in more detail:

1. User Experience:

How many clicks does it take for a user to get to the data? Measure this! From desktop, It’s Ideal to have 3 clicks or less to get to the data. If you more steps that users need to follow to get to the data, the chances of them getting lost somewhere else increases. If you have a great user experience, it’s easier for users to not have to remember how to get to the system. Here’s one example of an ideal User Experience:

Click #1: Click on web browser & type the name of the BI site (or pull it from favorites)

Click #2: On a BI site, they will have a “team site” (and that would show up automatically based on windows authentication) and they will see a reports categorized by subject areas. They will click on their subject area.

click #3: Click on a template and it will download the excel based template to user’s computer. (The template needs to be pre configured to connect to the data source)

In summary, easy-to-navigate BI sites are a huge plus!

On point #2, I had mentioned a BI site. You need some place for users to collaborate with their team & share reports. If you can’t setup SharePoint BI sites, then consider some shared network folders or have it on a common web site, some place that users can use to collaborate.

On point #3, I had mentioned “templates”. They may be excel files or blank power view reports configured to connect to the data source. Don’t ask your users to enter data source credentials – who would remember hxajfafhjfdakjinstance2143452 anyways?! Have templates that are ready to consume for end-users.

2. Trainer

Who needs to train user? of course, the trainer to have decent public speaking and communication skills along with being an expert at the end-user tool. He/she will also have to understand the business value of the data that the users are being trained on.

Now depending on the demand for training, a trainer could be hired full-time/part-time to train users.

If there’s not enough budget or training demand, IT managers can consider requesting Business Intelligence Developers/Consultants/Architects or IT analysts to train the users.

If possible, IT managers can also request an analyst from the business group to do the training.

It would be great to record the trainings in video/document format for users to review them later.

3. Training Content & Format

3a Content

There are various methods to design training content:

– Look at Frequently asked questions from user community & design training content around them

– Invite smaller group of users for “beta” testing your training content. see if they like it! And keep improving your training content iteratively as you have more training sessions.

– Look at resources available online or books, user groups, etc for best practices & samples

– build upon the work of your colleagues, your past work, ask for feedback!

– And most importantly, remember to communicate business value in your training content.

– consider including Hands on (practice sessions) content in your training.

3b. Format

There are various training format & depending on your needs you will have to decide on the format of delivery mechanisms and training schedules:

Delivering mechanisms: In person or virtual.

Time: One hour-long/2 hour-long/one-hour for three days/ etc

I have had virtual trainings with users from Asia at 9 PM Easter Time & I’ve had 6 AM Eastern time meetings for users from Europe. You’ll need to decide the format that works best for you.

Conclusion:

In this post, I wrote about three topics for training business intelligence users 1. user experience 2. Trainer 3. Training Content.

How to train your users to create their own Business Intelligence reports. #2 of 5: Pre Training Prep

In part 1, I wrote about why is it important to enable business users to create their own BI reports. In this post, part 2, I am going to share the pre-training preparations before you start training. I’ve classified into three categories: 1) Data 2) Tools 3) Culture. Let’s go through them:

1. Data

Data assets needs to be published before you start training. It should be a business friendly analytic layer on top of your data sources. It could something as simple as a Power Pivot Model to a SQL server analysis cube. As long as you have an analytics layer – you’re good! Do NOT grant access to transactional systems. I’ve seen a business analyst who was considered the go-to-business-expert of a system having issues trying to create reports using the system’s relational data source – He had challenge trying to get his head around multiple tables, keys, unfriendly field names. He got something up & running but it was hard for him! What’s the lesson here? Try to make it as easy as possible for business users to use data – create an analytic layer over your data sources.

Apart from this, Data Integrity is very important! If the users don’t trust data, they are not going to use it. Invite selected set of business users to test the integrity of the data before you publish the data assets.

Also, the analytic layer that you developed should perform well. if it takes a minute to return fairly simple result, then you will have challenge driving adoption.

2. Tools

What tools would you use to teach business users reporting? Of course, Excel is a top choice since many of the users are already familiar with using excel. Also, Show them a Power View using YOUR data – that may get them excited enough to learn Power View.

How about SSRS report builder & performance point dashboard designer? This is mainly targeted for IT developers so it won’t be great idea to train business users using this tools.

What about Power Pivot/ Power Query? from #1, business users face challenge trying to analyze relational data sources, so test your audience to see Power Pivot/ Power Query is a fit or not. It might work well if they want to combine from couple of spreadsheets with IT’s data assets, that would work! But don’t expect business users to spend time trying to learn Power Pivot & Power Query to analyze data. Again, test it with your audience, see if they pick it up, some of your users may pick it up, great! But usually, you’ll have to create data assets (cubes/power pivot models/tabular models) to reach the masses!

3. Culture

Image Source: Economist & Tableau

The more time that you spend understanding the culture, the more successful you are going to get in training users. It’s because the things you’ll learn while trying to understanding an organization’s culture will be useful in content creation, delivery mechanisms, target audience selection & communicating business value of data driven decisions.

let’s step back. what is culture? It is a characteristics of a group of people. What characteristics are you trying to find before you start training? Try answering following questions:

a. Where are the “analysts”?

– Are they part of IT teams? Who requests reports from “IT analysts”. (they are your target audience!)

– Are they part of business units? (Great! Make them efficient by removing manual data copy-pasting from their to-do list)

– Do they report to CxO’s/presidents/VP’s? (Great! request examples of data driven decisions)

b. Are there examples of value generated using data driven decisions?

– understanding how business uses data to generate value is very important. you will create content using these examples!

c. How comfortable are users learning new technology?

– Have they shown resistance in learning new systems?

– Are they used to receiving ready-to-consume reports! (don’t expect them to change their behavior. But figure out the person creating reports for them. Train them! Make them better)

In summary, understand the culture of the organization, it would help you prepare before you start the training.

Conclusion:

In this post, we saw three pre training preparations (1. Data 2. Tool Selection 3. Culture) before you start training users.

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.

#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”.

#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.

SQL Server Reporting Services: How to add Interactive Sorting to a Table with no groups?

Sorting is one of the common requirements in a SSRS report that business users ask for, even if they don’t ask – it’s great to have interactive sorting enabled at places where it makes sense. If you’ve a Table with no groups, here’s how you can enable interactive sorting on the table:

1. Right Click on the Column Header:

2. Click on “Text Properties” and navigate to “Interactive sorting”.

3. Check the box “Enable interactive sorting on this text box“. Also since there are no groups, we want to sort “detail rows“. Also, you’ll need to specify the sort by column. For demo, I picked Sales_Amount but you could also have an expression here if you wanted:

4. Click on OK. And run the report – you should see the interactive sorting buttons on the Sales Amount column. Users should be able to sort the data in ascending/descending order using this feature:

That’s about it for this post. I hope this helps!

SQL Server Analysis Services MDX: How to display last/rolling 12 months trend in MDX?

Problem Statement:

One of the common business requirement for Dashboards is to be able to see last/rolling 12 month trend on a chart. To meet this requirement, you’ll need to get a data set from the data source and if it’s a SSAS cube, How do you get last/rolling 12 months using MDX?

Note: It’s not a rolling 12 month sum. If you’re looking to do that, please refer: Sum of Last N Years, Quarters, Months, Periods.

Solution:

Here’s the pseudo code, I hope it gives you a good starting point:

[code language=”sql” gator=”false”]
SELECT NON EMPTY
{
[Measures].[MeasureName1],
[Measures].[MeasureName2]
}
ON COLUMNS,
{
[Fiscal Period].[Fiscal Period]
}
ON ROWS
FROM
(SELECT STRTOMEMBER(@FiscalPeriod).Lag(12):STRTOMEMBER(@FiscalPeriod) on 0 FROM [CubeName])

[/code]

Note the use of sub select & query parametrization. You’ll need to pass in current month value in the fiscal period parameter. I hope this helps!

SQL Server reporting services: How to customize the Legend Text on a chart?

Problem Statement:

How do you customize the Legend Text on a chart? by default, it’s going to show you the name that you have in the Data Set, but sometimes that’s not what you want to display so how do you change the name?

It’s really useful when you don’t want to change the name of field in the dataset or when you are using an expression as an item on the chart and you need to display a business user friendly name on the report:

Solution:

1. Go to Chart Data Properties:

2. To change the Legend Text of “Sales Amount CY” – Navigate to series properties of the “Sales Amount CY” series:

3. Go to “Legend” and enter the Text in the “Custom Legend Text“:

4. You can customize text for all legends on your report by navigating to the series properties.

Conclusion:

In this post, I walked you through how you can customize the legend name/text in SQL Server Reporting Services.

SQL Server Analysis Services MDX: Sum of Last N Years, Quarters, Months, Periods.

Problem Statement:

Solution:

Here’s the MDX code to calculate sum of sales amount for last three calendar years:

[code language=”sql” gator=”false”]

with member [measures].[Sales Amount Last Three Years]
as
SUM({[Date].[Calendar].CurrentMember:[Date].[Calendar].CurrentMember.PrevMember.PrevMember},[Measures].[Sales Amount])
select
[measures].[Sales Amount Last Three Years] on 0
from
[Adventure Works]
where
[Date].[Calendar].[Calendar Year].&[2008]

[/code]

Results:

I hope that gives you a good starting point to calculate the Sum of Measure during Last N Periods.

SQL Server Reporting Services: How to display Multi Select parameter selected on the report?

Here’s the problem statement:

You’ve a multi select parameter on your SSRS report and you need a way to display that on that the SSRS report. (Note: It’s a good practice because when the report is printed out the parameter values that were selected gets displayed and the consumer know that’s right off the bat.) – So how do you do that?

You added an expression on your report and double clicked on the parameter. If you do so, something like this will show up on your expression values: =Parameters!ProductCategory.Value(0) and after you changed Value to Label “=Parameters!ProductCategory.Label(0)” – here’s what you get on the report:

Solution:

I learned this trick via this stackoverflow thread.

Go back to expression and set the value of the expression to:

[code language=”sql” gator=”false”]
=Join(Parameters!ProductCategory.Label,",")
[/code]

And after I did that, it fixed the problem!

I hope this gives a good starting point!

Paras Doshi

Insight Extractor – Blog

Paras Doshi's Blog on Analytics, Data Science & Business Intelligence.

Month: April 2014