[VIDEO] Microsoft’s vision for “Advanced analytics” (presented at #sqlpass summit 2015)


Presented at #sqlpass summit 2015.

SQL Server Reporting services: How to display “There are NO rows” message?



You have a SQL Server reporting services (SSRS) report that has a table which displays some records — but sometimes it can have NO rows; In that case, how to display “There are No rows” message so that it doesn’t confuse the consumer.


  1. Open the report in SQL Server Data Tools and go to the “design” tab of your SSRS report
  2. Select your table (do NOT select a cell inside a table. Make sure that the table is selected) SQL Server reporting services NO data rows message
  3. While the “table” is selected, Go the Properties section OR you can use F4
  4. Inside the Properties section, find “No Rows” section and you should see a NoRowsMessage property:SQL Server reporting services NO data rows message v2
  5. Go to the preview tab to make sure it’s working and you should be ready to deploy the change!

That’s it! Hope that helps.

Official reference:  https://msdn.microsoft.com/en-us/library/dd220407.aspx

Author: Paras Doshi

How to change the Data Source of a SQL Server Reporting Services Report (Native Mode)?



You have your SQL Server Reporting Services environment in native mode — and you want to modify the data source of a report there.


  1. Navigate to Report Manager.
  2. Navigate to the Report that you want to Manage and run it
  3. After the report renders, you will have a breadcrumb navigation on the top right
  4. Click on the Last Part of the Breadcrumb NavigationSSRS properties report native mode
  5. It should open up the “properties” section of this report
  6. On the properties section, you should be able to manage the data source
    SSRS Manage Data Source Native Mode Shared
  7. Make the changes that you wanted to the data source settings of this SSRS report — and don’t forget to click “apply”
  8. Done!

Author: Paras Doshi

How to fix the Non-unicode to unicode data type conversion problems in SQL Server Integration Services?



Are you trying to import an Excel file into SQL Server using SQL Server Integration services…And ran into error that has words like “Non unicode” and “unicode”? Then this blog is for you.

Why does this error occur?

Well it turns out that things like SQL Server and Excel have encoding standards that they follow which provides them a way to process, exchange & store data. BUT turns out that SQL Server and Excel use different standards.


So, the solution is simple right? Import the data from Excel into non-Unicode format because that’s what you need for SQL Server.

So how do you that? Between your Source and Destination tasks, include a task called “Data conversion” and do the following for all columns that have text:

Excel SQL Server Unicode Nonunicode

And in the destination task, you’ll have to make sure that the mapping section using the new output aliases that you defined in the “data conversion” step.


In this post, we learned about how to solve a common error that pops up when you try to import excel file to sql server using SSIS. Hope that helps.

Author: Paras Doshi

Titanic Data


Here’s a link to download the Titanic data — http://lib.stat.cmu.edu/S/Harrell/data/descriptions/titanic.html — it’s really useful in analytics and data science projects. You can:

  1. Build a predictive model. Example: https://www.kaggle.com/c/titanic
  2. I also use this data set to create interactive dashboards on tools like Qlik and Tableau to understand their features.


If you liked this, you may also like other data sets that I have here: http://parasdoshi.com/2012/07/31/where-can-we-find-datasets-that-we-can-play-with-for-business-intelligence-data-mining-data-analysis-projects/

Productivity Tip: Learn to Comment/Uncomment SQL code using shortcuts


I spend a lot of time writing SQL code — and as a reader of this blog, You might be in the same boat. So any productivity gains that we could get here could go a long way. On that note, here’s a quick productivity tip: Learn to comment/uncomment multiple lines of SQL code using keyboard shortcut.


If you are using SQL Server Management Studio, it’s “CTRL-K followed by CTRL+C” for commenting AND “CTRL+K followed by CTRL+U” for uncommenting.

If you are using some other Data Management Software tool, I am sure you can find it using their HELP section or googling around.

Either ways, these shortcuts go a long way in making you more productive! What is your favorite productivity tip?

Qlik sense: How to see Data Load Editor scripts for apps developed by your Team members?


(This post first appeared on the Qlik Community. here)


So you just joined a Business Intelligence Team and one of the responsibilities include building apps for your business users. Eventually, you would have a need to see Data Load editor scripts for apps developed by other members in the team. So what permission do you need to be able to do that?

Credits: darkhorse

Qliksense Version: Enterprise Server 2.0

Source: can’t see a peer’s data load editor scripts


This a two-step process.

1) Get “content admin” access (or “higher” level access)

2) Double check if you have access to see data load scripts for ALL apps

Step 1:

The short answer is that you need “Content Admin” permission from your Qlik sense admin…But with this access level, you will have access to other developer’s app via QMC. If you need to do this via HUB as well then you will have to change the content admin role.

Here’s how Serhan ( darkhorse ) explained how to get this done:

QMC–> Security Rules–>Content Admin–> Edit–> Context–> Both in Hub and QMC

Qlik sense management console

Step 2:

Now, once you get the “content” admin access, you might want to double two things:

1) You can get access to data load scripts on published apps — (I was able to do this but there still seems to some open questions around some folks not being able to see the data load scripts for published apps. If this is the case for you, you need to duplicate the app on your “my work” area and see the scripts)

2) You can duplicate apps on your “my Work” area and see scripts — this is also useful if you want to make changes to published apps that are out there.


I hope this helps you resolve the permission issues and help you collaborate with your team members!

How to add Sparkline data visualization to Google spread sheets?


I like using spark lines data viz when it makes sense! It’s a great way to visualize trends in the data without taking too much space. Now, I knew how to add sparklines in Excel but recently, I wanted to use that on Google sheet and I had to figure it out so here are my notes:

1. Google has an inbuilt function called “SPARKLINE” to do this.

2. Sample usage: =SPARKLINE(B2:G2) — by default you can put line chart in your cells.

3. Then there are other options including changing the chart type. You can find them documented here:  https://support.google.com/docs/answer/3093289

4. One of the best practices that I advocate when you spark-line to “compare” trends is to make sure that you have the consistent axis definition. So the sample usage for that could like this:


(if you want to do this for excel then here’s the post: http://parasdoshi.com/2015/03/10/how-to-assign-same-axis-values-to-a-group-of-spark-lines-in-excel/ )

After you’re done, here’s what a finished version could like on Google sheet:

Google Sheet Data visualization spark line

Here’s the working google sheet: https://docs.google.com/a/parasdoshi.com/spreadsheets/d/1EJYDTxOifeEL-YwW1a0oxXw7tFG1iAVQlwjo4EU8R-s/edit?usp=sharing

Data -> Insights -> ?


I was at the HP Big data conference last week and I heard something during the keynote that’s worth sharing with you.

As Data & Analytics professionals, we spend a lot of our time on finding insights, trends & patterns out of the data but the keynote speaker (Ken Rudin, Facebook) encouraged everyone to take that a step further = Think about Driving impact based on the insights. It’s simple yet a powerful idea! Over past few months, I have started working closely with decision makers and helping drive impact vs just “handing-off” insights.

I hope that helps! Just wanted to share that with you. What do you think?


How to add custom User-ID to your Universal Analytics (Google) implementation?


There are three different implementations that you could have with Google Analytics:

  1. Classic Google Analytics
  2. Universal analytics
  3. Universal analytics with Google Tag manager.

If you fall under “2. Universal analytics” then this post is for you since I’ll walk you through steps that you need to take see UserID’s on your google analytics report.

First why do you want UserID on your GA reports? 

1) Data blending is powerful. If you can combine your transactional datasets with web traffic data then you can extract some powerful insights! To be able to join your transactions data with web traffic data, you need some field that is common across those datasets. UserID might be one of the most useful fields that you could use for data blending.

2) Also having UserID in your reports let’s you perform some user behavior analysis at individual level and that could be pretty powerful too!

Now, How?

There are three steps:

  1. Identify the User ID
  2. Create a Custom Dimension on Google Analytics
  3. Modify your tracking code to send data for the custom dimension

Step 1. Identify the User ID

The first step is to identify the ID that you are going to send to Google analytics. Remember that you can’t send PII (personally identifiable information) so you can’t send something like an email id but you can send other ID’s that may be used in your database.

Step 2: Create a custom dimension on Google Analytics

Go to google analytics admin section > Select your account & property > Go to Custom dimensions

Custom dimensions google analytics

Now, create a new custom dimension. Give it a name, scope it and make sure it’s marked active.

Custom dimension universal analytics User ID

Step 3: Modify your tracking code to send data for the custom dimension

Notice that once you create the dimension, it will also show the example code (on the right side in the picture above). Send this to your web developer!

Just a note on this: The custom dimensions for which you are passing value using the tracking code are referenced as dimensionxx and you don’t use the Name like “Custom User ID” — if it’s the first custom dimension that you will refer to it as dimension1 in the code.

Next steps: Let me know if you have any further questions and if you are interested in seeing the steps for classic GA and Universal analytics w/ Google Tag manager then here’s a blog for you: http://dan-russell.com/2014/03/how-to-set-a-userid-as-a-custom-variable-using-the-google-analytics-cookie/

I hope this was helpful!