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.
SSAS Actions are powerful! You can open web pages, open sql server reporting services, customize drill through reports among other things using actions. In this post, you will see a common requirement from users to navigate to a corporate intranet site from the cube – and usually it needs to be dynamic.
For example, user is interested in seeing the Order Entry Page hosted on the corporate intranet site by using the Order ID from the SSAS cube.
Here’s how you can set it up:
1. Open SSAS Cube in SQL Server Data Tools:
2. Navigate to Actions tab:
3. Here you’ll see three types of action that you can configure
a. Standard (this have five subtypes including the URL action)
b. Drill Through
c. report action
4. For the purpose of this blog post, let’s focus on standard action:
5. Once you click on the “New Action” it will ask you to configure the action:
a. Name: Enter the desired name here
b. Target Type: In this case, Order ID is an attribute member but you will have to choose appropriate target type for your scenario
c. Target Object: In this case, it’s something like [Order].[Order ID] – in your case, you’ll have to choose an appropriate target object
d. Type: URL in this case (also don’t forget to check books online for what other types can do as well)
e. Action Expression: the format of the Action Expression if it’s driven by a parameter would go something like:
[code language=”SQL”]
"http://servername/site/Pages/OrderRef.aspx?Search&ID="+[Order].[Order ID].currentmember.member_caption
[/code]
f. Additional Properties: I like to set the Caption to clearly indicate the user that they are opening the “Order Form for Order ID 123999”. You can do that by setting the caption property. The format goes like this:
[code language=”SQL”]
"Open Order Entry page for Order ID: "+[Order].[Order ID].currentmember.member_caption
[/code]
Also set the caption is MDX to True if you are using above format.
That’s about it, don’t forget to test it (after deploying the cube) using excel or other end-user tool of your choice. In the Pivot Table, use the Order ID attribute in Row/Column labels > Right Click on any attribute member of Order ID attribute > Additional Actions > The caption with dynamic order id should show by here for users to click and navigate to the specified URL:
Problem:
How do you get a Month Name from a date field in Power Pivot?
Solution:
here’s a code snippet that should help:
[code language=”SQL”]
=FORMAT([date],"MMM")
[/code]
This should give you month names (Jan, Feb, …) instead of integers that are returned by the MONTH function.
couple of notes:
1. date field needs to be used to get the month name
2. MMM needs to be in uppercase.
I hope this helps.
Problem:
Convert the following source data into a schema shown below:
Solution:
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.
[code language=”SQL”]
— 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
[/code]
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.
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
4. You’ll get a dialog box, enter server name and the cube/model metadata that you want to import
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.
Problem:
How do I change the query that’s being used to populate tables in SSAS Tabular Model?
Solution:
Here are the steps that you need to follow:
1. After you’ve the solution open in SQL Server Data Tools, go to menu > Table Properties
2. On the Edit Table Properties, you can change the query. you can also change the tables if that’s what you have used using the “switch to” box on the right side of the dialog box.
3. If you need to change the server name or instance, used then you will need to modify the connection, for that go back to menu bar and click on Model > Existing Connections. you should be able to edit the connection from here:
Conclusion:
In this post, you saw how to change the query used for the tables in SSAS Tabular models.
Where can you find key SSAS MDX cube objects in the metadata pane? Following picture should be of help:
1. Cube
2. Measure Group
3. Measure
4. Dimension
5. Attribute Hierarchy
6. Attribute
7. Attribute Members
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 #4 of N:
Report Filters are a great way to put constraints on the data that is displayed. So once a user gets the concept of selecting dimensions as report filters & slicers, they might start wondering how to filter measure values? How do they show a list of customers that have ordered less than $100,000 worth of products? They might try to drag a measure to the report filter section but that won’t work & Excel will throw an error. So how can users filter measure values? it’s a feature of Pivot Table called “Value Filters” and that would be helpful here. Here are the steps:
1. Select the Pivot Table.
2. click on a drop down menu besides “row labels”
3. After that, go to Value Filters. You can see that you can filter by applying different rules like Top 10, less than, greater than, etc. Let me demo “Less than or equal to”
4. On the next dialog box, you can select the measure, rule & the filter value:
After you’ve set them up, click on ok and the Pivot Table will filter by measure values.
Note: Be careful when sharing the files that have value filters with other users, let them know that you’ve value filters set on the report as the value filters are not clearly visible if you’re looking at the file for first time.
If a user is trying to create a dashboard using excel, they’ll have more than one Pivot Table in a sheet. In that case, they’ll need to move the Pivot Tables around. here are the steps:
1. select the Pivot Table.
2. In the menu bar, go to Pivot table Tools > Move Pivot Table:
3. select your NEW destination and click on OK.
That’s it!
In this post, we saw how to filter measure values & how to move Pivot Tables.
#1: How to sort data? How to add slicers? How to change Pivot table Layout
#2: How to add calculated measures?
#3: how to remove grand totals & subtotals and, how to configure the automatic data refresh.