Business Intelligence system – Customer Complaints – B2B company:

Customer complaint dashboard quality feedback
Standard

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

Time Intelligence in MDX: last N days

Standard

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:

[code language=”SQL”]

WITH
MEMBER [Measures].[Sales_last_15_days] AS
Sum
(
{
[Calendar].[Date YYYYMMDD].CurrentMember.Lag(14)
:
[Calendar].[Date YYYYMMDD].CurrentMember
}
,[Measures].[Sales]
)

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

SELECT
{
[Measures].[Sales_last_15_days]
} ON COLUMNS
FROM
[CubeName]
WHERE
STRTOMEMBER([Measures].[CurrDate])

[/code]

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?

Standard

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:

[code language=”SQL”]

StrConv("hello world",3)

[/code]

OR

[code language=”SQL”]
StrConv("hello world",vbProperCase)
[/code]

InputFunctionOutput
hello worldStrConv(“hello world”,3)Hello World

I hope that helps!

SSAS Joining Facts at different granularity to a single dimension:

Standard

Problem:

You have a Fact Sales and Fact Target in your data mart. Fact Sales stores values are product sub category level and fact target stores values at product category level because business sets “sales targets” at a higher (rolled up) level. How do you connect it to a single dimension at different granularity?

Solution:

Here’s the table structure, I just made this up for the demo purpose:

Fact Sales Table

1. Fact Sales

Fact Target

2. Fact Target

Product Sub Category Table

3. Dim product sub category

so, you went ahead and tried testing by creating relationship’s to single dimension at different granularity in the cube:

SSAS Dimension Usage RelationshipsNote how the relationship was specified between Fact Target and Product Sub Category Dimension – it’s joined at a different granularity compared to fact sales. it would be help you from a performance standpoint if the fields that you are using to join the fact and dimension is an int.

SSAS Relationship Dimension Usage Regular

So, you browse the cube and here’s what you get:

Excel SSAS Analysis Services

Note the problem: the target values are being repeated for sub categories but that shouldn’t be happening, right? that’s misleading to business users…ok, to recap what we need to do here: hide target values for subcategories since targets are not set at that granularity. but we do need to show them if the business users pulls in product category.

So here’s a measure group property that comes to the rescue!

Go to Fact Target Measure group’s property > Set IgnoreUnRelatedDimension to False

SSAS Ignore Unrelated Dimension Property

deploy and browse your cube again, here’s what you will see now:

Excel SSAS Analysis Services Pivot Table

That’s it! you have successfully joined facts at different granularity to a single dimension.

SSIS: Using Data Profiling Task to check the candidate key profile of unknown data source(s)

Standard

As a part of Business Intelligence projects, we spend a significant amount in extracting, transforming and loading data from source systems. So it’s always helpful to know as much as you can about the data sources like NULLS, keys, statistics among other things. One of the things that I like to do if the data is unknown is to make sure that I get the candidate keys correct to make sure the key used can uniquely identify the rows in the data. It’s really helpful if you do this upfront because it would avoid a lot of duplicate value errors in your projects.

So here’s a quick tutorial on how you can check the candidate key profile using data profiling task in SSIS, You need to perform two main tasks:
1. Generate the xml file using the Data profiling task in SSIS
2. View the content of the xml file using the Data Profile Viewer Tool or using the Open Profile Viewer option in the Data Profiling task editor in SSIS.

Here are the steps:
1a. Open SQL Server Data Tools (Visual Studio/BIDS) and the SSIS project type
1b. Bring in Data Profiling Task on Control Flow
1c. Open the Data Profiler Task editor and configure the destination folder that the tasks uses to create the XML file. You can either create a new connection or use an existing one. If you use an existing connection, make sure that you are setting the OverwriteDestination property to True if you want the file to be overwritten at the destination.

1 SSIS Data Profiling Task Data Cleaning Candidate Key

1d. Click on Quick Profile to configure the data source for the data profiler task

2 SSIS Data Profiling Task Data Cleaning Candidate Key

1e. In the quick profile form, you’ll need to select the connection, table/view and also specify what you to need to computer. For candidate key profile, make sure that the candidate key profile box is checked.

3 SSIS Data Profiling Task Data Cleaning Candidate Key

1f. Run the Task and a XML file should be placed at the destination you specified in step 1C.

Now, It’s time to view what profiler captured.

2a. you can open “Data Profile Viewer” by searching for its name in the start button.

4 SSIS Data Profiling Task Data Cleaning Candidate Key

2b. once it opens up, click on open and browse to the xml file generated by the data profiling task.

5 SSIS Data Profiling Task Data Cleaning Candidate Key

2c. once the file opens up, you can the candidate key profiles.

6 SSIS Data Profiling Task Data Cleaning Candidate Key

2d. Alternatively, You can also open the data profile viewer from the “Data Profiling Task” in SSIS. Go to the Editor > Open Profile Viewer:

7 SSIS Data Profiling Task Data Cleaning Candidate Key

Conclusion:
In this post, you saw how to profile data using the Data Profiling Task in SSIS.

#sqlpass #msbi online event: “The Accidental Report Designer: Data Visualization Best Practices in #SSRS”

Standard

PASS Business Analytics VC presents “The Accidental Report Designer: Data Visualization Best Practices in SSRS” by Meagan Longoria. Here are the meeting details:

Date & Time: Thu, June 19 2014 12:00 Eastern Daylight Time

RSVP URL:

http://bit.ly/PASSBAVC061914

Session Abstract:
Whether you are a DBA, a developer, or an analyst, there is a good chance that you will have to create reports for coworkers, company executives, or clients. As with any UI design, careful consideration should be given to your data visualization design to ensure you are effectively communicating the intended message and providing a good user experience. While the principles are applicable across reporting platforms and tools, this session will contain demos and examples implemented in Reporting Services using SQL Server Data Tools. Learn how to make information (not just data) the focus of your report and provide your audience with something better than just shiny!

Session Level:
Intermediate

Speaker BIO:
Meagan Longoria is a BI consultant with Valorem Consulting in Kansas City, Missouri. She has over 6 years of experience with the SQL Server BI stack and enjoys sharing her knowledge and experiences at SQL Saturdays. She is also one of the coordinators for SQL Saturday in Kansas City.
Contact URL: http://datasavvy.wordpress.com

See you there!

Paras Doshi
Chapter Co-Leader, PASS BA VC

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

Standard

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:

[code language=”SQL”]

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

[/code]

Output:
SQL Server Reporting Services SSRS Trademark special symbol

Questions Power Users Ask about Excel: #1 of N

Standard

In this series, I intend to document common questions asked by Power users about Excel connected to SSAS cubes (or data warehouse) after they go beyond the basic stage of understanding & using Row labels, column labels, report filter & values in Pivot Tables. This post is #1 of N:

a. How to Sort Data?

There are two ways to do this & here they are:

#1: Select a cell that has a measure value > Right click > Sort > sort the values in ascending or descending from here:

sorting Excel ad hoc report ssas cube

#2: Alternatively, you can also do the same thing by: Select a value from the filed that needs sorting > go to Home Tab > Sort & Filter > from here you should be able to sort data in ascending for descending order:

sorting data excel analysis services cube

b. How to add slicers?

They might not know what slicers are but I’ve been asked how can I add filters where users can see the filter values before picking the value. I’ve also been asked by a Power user for the capability for adding “global filters” who had couple of pivot tables in a single sheet. They also come in handy if you’re building Excel Dashboards. With that, here are the steps:

1. Based on your requirements, decide if multiple items on a dashboard (excel sheet) need to be affected with one click

2. Now, for the purpose of this blog post, let’s assume that you have two Pivot Tables like shown below:

two excel reports slicer 1

Note: the Two Excel Pivot Tables are created by connecting to same cube. And the slicer comes from the same cube.

3. And what if you need to slice data in both pivot tables by one common slicer? Let’s say we want to slice the data in the two pivot tables that we have by Product Category – here’s what you do (assuming that the tables are related in your data source and slicing makes sense)

4. click on any of the pivot table and you’ll see PivotChart Tools in the toolbar

5. Switch to Analyze Tab & click on insert slicer & Select the field that you want as slicer & click OK

pivot table insert slicers

6. you’ll see a slicer on your sheet now:

slicer on pivot tables

7. Let’s format the report to make it look better:

#1: move the slicer such that it does not overlap any of the pivot tables.

#2: In this case, I want to move the slicer to the top of the sheet and change the slicer to have 5 columns. Here’s how you can do that:

select the slicer > from the toolbar > slicer tools > column

slicer tools option excel

#3 I also changed the slicer style from the slicer tools to match its color palette with that of the Pivot Tables:

8. Note that the slicer is connected to just ONE pivot table. Let’s connect it to both pivot tables

9. From Slicer Tools options > Pivot Table connections > check all pivot tables that you want the slicer to be connected to:

pivot table connections excel

10. Done! Test your slicers, with one click you should be able to see that the data gets sliced in both pivot tables:

#1: Sliced by Bikes

excel pivot analysis data 1

#2: Sliced by Accessories

excel pivot analysis data 2

c. How to change the layout of the Pivot Tables?

Without formatting and changing the layout, the excel pivot table looked like this:

step 1 excel pivot table from power pivot

Now, let’s work step by step to meet the requirement:

Step 1: click somewhere on the Pivot Table and from the Toolbar, Switch to the Design Tab under PivotTable Tools

Pivot Table Tools Design Excel

Step 2: Now here, Go to Report Layout > Show in Tabular Form

Pivot Table Layout Tabular Form

Step 3: As you might have noticed the “hierarchical” structure is now broken up into multiple structure getting us closer to meet the requirement.

half way pivot table layout

Step 4: In our requirement, you can see that it does not give the user to see the “+” or “-” (expand, collapse) buttons. So, let’s hide these buttons from the report from Step #3

Right click somewhere on the pivot table > PivotTable Options > Display tab > uncheck the box that says “Show expand/collapse buttons“:

pivot table hide expand collapse button

Step 5: so now it looks as follows:

excel pivot table power pivot

To recap, Here’s what we had to do: Change the Layout of the Pivot Table & we also hid the expand/collapse buttons too.

Conclusion:

In part #1 of N, we saw:

1) How to sort data?

2) How to add slicers?

3) How to change Pivot Table layout?