Achievement Unlocked: Tableau Desktop 8 Qualified Associate!

To test my Tableau knowledge, I attempted the Tableau product certification and got the “Tableau Desktop 8 Qualified Associate” certificate.

Tableau 8 Qualified associate Certificate paras doshi

 

SQL Server Reporting Services: How to Solve Divide by Zero Errors?

Problem:

you thought you wrote an if condition to handle “divide by zero” errors in SSRS but still get the run time error?

Example. my expression is:

=IIF(Fields!denominator.Value=0,0,
CINT(Fields!numerator.Value/Fields!denominator.Value))

And I still get the error:
Divide by Zero Error SSRS

Note: if you do not have integers to divide and you allow data conversion to double, it would show on SSRS as “infinity” instead of “#Error”.

Solution:

Let’s understand why does it do it?

So even though you are using IIF condition, the vb IIF condition evaluates every function in every expression before it renders and that’s why the “False” condition that I have in my condition gets run which results in #Error.

Ok, armed with that knowledge, let’s solve the problem.

So here’s a modified version of the expression, have a look:

See what we did there! We added one more IIF condition in the “false” condition of the parent IIF.

=IIF(Fields!denominator.Value=0,0,
CINT(Fields!numerator.Value
/IIF(Fields!denominator.Value<>0,Fields!denominator.Value,1)))

That should solve the problem:
Divide by zero fix customer code ssrs

There’s also an alternative to this especially if you have a lot of expressions that does this. You can write your custom code and call it SSRSDIVIDE or you can come up with a better name! Here’s a post that talks about how to do that: http://salvoz.com/blog/2011/11/25/ssrs-checking-for-divide-by-zero-using-custom-code/

Conclusion:
In this post, we saw how to solve the divide by zero errors in SSRS.

SQL server Analysis Services: How to import cube meta data to SQL server Data Tools?

Problem:

Someone on your team deployed a cube and gave you access to it. Now, as an IT developer on the team you want to know the calculation that was used for some of the measures. How do get it? You can email the contact person, right? Sure, but let me show you how you can decrease email back and forth using the following steps.

Note: it’s applicable to both multidimensional model as well as tabular model.

Solution:

1. Open SSDT

2. File > Project > New

3. Installed Templates > Business Intelligence > Analysis services > Import from Server

SQL Server Analysis Services Business Intelligence Import

4. You’ll get a dialog box, enter server name and the cube/model metadata that you want to import

Import Microsoft Business Intelligence analysis cube

5. In the solution explorer, you should be able to see the cube meta data! Now, you can go look at the measure calculation that you wanted to see, or may be the KPI rule? or something else in the cube that you needed to know.

Conclusion:

In this post, I described how you can import SQL server analysis services cube/model from the server.

SQL Server reporting services: How to use the Split function in the SSRS expressions to get sub-string?

Problem Statement:
How do you use SQL Server Reporting Services (SSRS) expression to get sub-string from the inputted text?

Solution:
I am going to show you few SSRS expressions that you can use in your SSRS reports:

SSRS SQL Server Reporting Services Expression SPLIT

Here’s the same in a text:

Input: SSRS Expression used: Output:
[Date].[Fiscal Year].&[2008] Split(Parameters!DateFiscalYear.Value,”&”)(1) [2008]
[Date].[Fiscal Year].&[2008] Split(Parameters!DateFiscalYear.Value, “.”)(2) &[2008]
[Date].[Fiscal Year].&[2008] Split(Split(Split(Parameters!DateFiscalYear.Value, “&”)(1),”[")(1),"]“)(0) 2008
[Date].[Fiscal Year].&[2008] Parameters!DateFiscalYear.Value.Split(“&”)(1) [2008]

Conclusion:
In this post, you saw how to use the split function in SQL Server Reporting Services (SSRS) expressions to get a sub-string.

SQL Server Analysis services MDX: How to write a query that works as LIKE operator in SQL?

Problem Statement:

In MDX, How to search for a sub-string using something similar to LIKE operator in SQL?

Solution:

here’s an example using Contoso Sales Cube, please use this to get started for your scenario:

Note the use of INSTR function.


select
[Measures].[Sales Amount]
on 0,
Filter(
[Geography].[Region Country Name].[Region Country Name]
,instr([Geography].[Region Country Name].CURRENTMEMBER.MEMBER_CAPTION,'United')>0)
on 1
from
[Sales]

SSAS MDX analysis services like SQL

Also,
if you are looking for NOT LIKE then replace >0 with =0 and you should get the desired results.

Conclusion:
In this post, you saw how to write a MDX query that does sub-string search for you similar to LIKE and NOT LIKE operators in SQL.

PASS Business Analytics Conference – Day #2 Keynote

what an inspiring keynote by David McCandless! Here are some the things that I noted:

1. Play with your data, get your hands dirty

“Data is the new Soil” – David McCandless

2. set the context while communicating what data is trying to tell.
Example: China has the largest army but compared it to their population they are 120ish! So measuring just the army size does not give you necessary context & sometimes it can meaningless

3. Data visualizations help you combine the language of the mind (numbers) with language of the eye (visuals)! Humans are better at processing visuals.

4. Make sure your data is true & keep your personal bias out of analysis.

5. Other than making sure that the data integrity is achieved in visuals, they should be interesting and functional.

6. Design is about removing unwanted things & distilling the visual to it’s functional essence.

You can check out David’s work at http://www.informationisbeautiful.net/
Information is beautiful

PASS Business Analytics Conference – Live Blogging: Day #1

I’m at the Business Analytics conference and I thought of sharing the news that I get to listen here!

On day #1, Kamal Hathi & Amri Netz are keynote speakers today.

They started with progress made during past few months (Power Query, Power BI, Power Map, SQL server 2014, Azure HDInsight….)

Then they shared some user adoption data…

Power Pivot & Power Query:

They also shared  user adoption data about Power BI:

They use Power BI to track user adoption of Power BI.

Power BI demo contest: if you’ve not seen some of the amazing demo’s that were submitted during the Power BI demo’s then you can read them here: http://blogs.msdn.com/b/powerbi/archive/2014/02/15/give-a-high-five-to-the-top-five-finalists-in-the-power-bi-demo-contest.aspx

Mobile BI:

Microsoft is committed to having Power BI native apps on different platforms and enable BI on any device

SSRS with Power BI:

BI for the masses

It’s great to see Microsoft committed to create easy to use tools!
The Age of Classic BI -> The Age of Self Service BI -> The Age of Data.
In the new age, everyone in the organization who is curious will have tools that they can use to get to the answers!

Amir’s Demo:

Analysis of Tourism in Hawaii. It was really entertaining :)

New features in Power BI:

create dashboards using natural language (KPI editor)

Forecasting in Power View:

Tree maps in Power View

And I just saw a Tree maps in Power View!

Drag items from one chart to another!

you should now be able to drag items from one chart to another chart!

Combine two charts into one!

Nice interactivity feature

 

The journey to DATA CULTURE begins today…

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

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 part #4, I wrote about Sample Business Intelligence Training content.

In this post, part #5, I am going to write about check list that you need that complement your training efforts. And the list is:

  • Survey
  • User Support System
  • Monitor usage
  • Demo user-created BI reports

Here are more details about each topic:

Survey

Send out a simple survey at the end of the training session. Here’s a sample survey:

Please rate the training based on following criteria’s:
1.Overall
2.Content
3.Trainer
4.Easy of use (BI System)
5.Comments?
______________

User Support System & Documentation

As users use the BI system, they would have questions that need timely response. So IT managers need to think about setting up a user support system. Someone from the BI team could take on the responsibility answering user’s question. They also need to think about how to integrate corporate IT support system with the user support for the BI system. if need be, a dedicated user support & documentation site can also be created. Note that resolving user queries is as important as giving them training!

Also necessary documents (/videos) need to be provided to user for their future reference.

Monitor Usage

BI system logs (Analysis services Trace/reporting services logs) can be used to monitor usage. It’s important to use data to track the usage of the system to see the return of investment for training efforts.

Demo user-created BI reports

recognizing your followers who do great job is a great tactic to attract more followers!

Demo user-created BI reports in trainings, meetings with project sponsors, team meetings and recognize the effort of the user. In some cases, you could invite the creator to demo their report in a training session – and you got one more trainer to teach your users!

Conclusion:

here’s the complete checklist for “How to Train your Business users to Business Intelligence reports?”

  • Data
  • Business Value of analyzing data
  • Tools & User Experience
  • Trainer
  • Training Content
  • Documentation
  • User Support specialists
  • Usage monitoring system

And here’s a slide-deck that shares the same information:  bit.ly/trainbusinessusers

I hope this series gave you a framework to train your business users to create their Business Intelligence reports.

Presented at #SQLSat 305 Dallas BA edition!

I got an opportunity to speak at SQL Saturday 305 Dallas BA Edition on 5/3/14! It was a great opportunity to network, share & learn!

if you are interested in “How to Train your business users to create their own BI reports” slides then they are posted here: http://bit.ly/TrainBusinessUsers

Here’s couple of generous session evaluations I got:

Paras Doshi Speaking SQL Sat 305 feedback Train Business Intelligence UsersParas Doshi Speaking SQL Sat 305 feedback

 

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 hxajfafhjfdakj\instance2143452 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.