PASS Business Analytics VC has grown 123% in a year! #sqlpass @passbavc

It’s been amazing to see the growth of Business Analytics community over the past couple of years as one of the chapter leaders on the PASS Business Analytics Virtual chapter…Here’s a data viz that I put together to analyze effectiveness of our marketing campaigns:

Here’s the chart: 

PASS Business Analytics Virtual Chapter Marketing Effectiveness Chart

It shouldn’t come as a surprise that an “Analytics” virtual chapter is using data-driven marketing techniques! ;)

Calcs:

May’14 = 100 attendees. Jun’15 = 223 attendees. % Diff = 123%

Projections:

With this growth rate, we should have ~500 attendees in our future virtual chapter meeting in Jun 2016. Can’t wait! :)

Credits: 

A lot of work by Dan English (current president) and Melissa Demcsak (Immediate past president) went into growing this chapter!

SQL: How to get first/last transaction details for each user account?

Looking at user’s first/last transaction details is a common requirement. So given that you have a “user_transaction_details” table which looks something like:

Transaction_id | user_id | transaction_date | purchase amount

How would get first/last transaction details for each user account?

Here’s a design pattern:


select AllTransactions.user_id,AllTransactions.purchase_amt from user_transaction_details AllTransactions
inner join 
(
select user_id, min(transaction_timestamp) as first_transaction_timestamp from user_transaction_details
group by user_id
) FirstTransaction
on AllTransactions.user_id = FirstTransaction.user_id and AllTransactions.transaction_timestamp = FirstTransaction.first_transaction_timestamp

To get the last transaction details for each user account, replace min function with max and that should be it.

Note: Depending on your data model and how you used it in the join, it might be that there would be multiple rows marked as “first/last” transaction and so would want to spend some time figuring out how to deal with these multiple row situation especially if you’re planning to further join this data.

Conclusion:
In this post, I shared a design pattern to write a SQL query to get first/last transaction details for each user account type.

Question for you:
How would you solve this problem? Share you query in the comments section!

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!

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.

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.

SQL Server Query Fundamentals: A Simple example of a Query that uses PIVOT:

Problem:

Convert the following source data into a schema shown below:

SQL SERVER TSQL PIVOTSolution:

Here’s the code that uses PIVOT function to get to the solution, please use this as a starting point.

Note the use of aggregation function avg – this will depend on the requirement. In the example, the Test_value need to be average if more than one tests were performed.


-- source data
SELECT [Product_ID],[Test_Desc],[Test_Val] FROM [dbo].[Address]
go

-- Destination data using PIVOT function
select * from [dbo].[Address]
pivot( avg(test_val) for test_Desc IN (Test1,Test2,Test3,Test4,Test5)) 
as Tests

TSQL – Quick note about numeric data type to solve “Arithmetic overflow error”

Problem:

You are working on a query where you are trying to convert source data to numeric data type and you get an “Arithmetic overflow error”.

Solution:

Let’s understand this with an example:

Here’s the source data: 132.56000000 and you want to store just 132.56 so write a query that looks like:

cast( as numeric(3,2)) as destination_column_name

and after you run the query its throws an error “Arithmetic Overflow Error” – so what’s wrong?

The issue is that you incorrectly specified the precision and scale – by writing the query that says numeric(3,2) you are saying I want 3 data places with 2 on the right (after decimal point) which leaves just 1 place for left.

what you need to write is numeric(5,2) – and this will have 2 places on the right and leaves 3 places for left.

so after you run this, it shouldn’t complain about the arithmetic overflow error. you just need to make sure that the precision and scale of the numeric data type is correct.

Conclusion:

In this post, you saw an example of how to correctly use the precision and scale in the numeric data type and that should help you solve the arithmetic overflow errors.

SQL Server reporting services: How to Add Trademark special symbol in Text?

Problem Statement:

The goal of this blog post is to share a quick tip on SQL Server Reporting Services (SSRS): How to add Trademark TM special symbol in Text?

Solution:

In SSRS, you can put following code in the Expressions:


="Text"& ChrW(8482) &" Dashboard"

Output:
SQL Server Reporting Services SSRS Trademark special symbol

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:

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])

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:

SSRS Custom Legend TextSolution:

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:

SSRS Chart Series Legend Properties

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

SSRS Custom Legend Text Series Properties legend custom4. You can customize text for all legends on your report by navigating to the series properties.

SSRS Custom Legend Text Change Name

Conclusion:

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