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.
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.
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]
| Input | Function | Output |
| hello world | StrConv(“hello world”,3) | Hello World |
I hope that helps!
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:
so, you went ahead and tried testing by creating relationship’s to single dimension at different granularity in the cube:
Note 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.
So, you browse the cube and here’s what you get:
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
deploy and browse your cube again, here’s what you will see now:
That’s it! you have successfully joined facts at different granularity to a single dimension.
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.
1d. Click on Quick Profile to configure the data source for the data profiler task
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.
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.
2b. once it opens up, click on open and browse to the xml file generated by the data profiling task.
2c. once the file opens up, you can the candidate key profiles.
2d. Alternatively, You can also open the data profile viewer from the “Data Profiling Task” in SSIS. Go to the Editor > Open Profile Viewer:
Conclusion:
In this post, you saw how to profile data using the Data Profiling Task in SSIS.
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:
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
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:
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:
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:
#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:
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:
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
6. you’ll see a slicer on your sheet now:
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
#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:
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
#2: Sliced by Accessories
Without formatting and changing the layout, the excel pivot table looked like this:
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
Step 2: Now here, Go to Report Layout > Show in 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.
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“:
Step 5: so now it looks as follows:
To recap, Here’s what we had to do: Change the Layout of the Pivot Table & we also hid the expand/collapse buttons too.
In part #1 of N, we saw:
1) How to sort data?
2) How to add slicers?
3) How to change Pivot Table layout?