Sales Bookings vs Quota Dashboard for a B2B company:

Sales Team bookings vs quota dashboard
Standard

Business Goal:

Need a daily report delivered in sales team’s inbox that shows Sales Team’s Bookings vs Quota for current & next month.

Brief Description:

Ability to see Bookings vs Quota in near real-time is a key to effectively manage performance for any sales team. Before the project, analyst(s) would have to manually put together this report and since the report took more than a day to put together they couldn’t afford to run it daily and so they delivered this report bi-weekly/monthly basis to the sales team. After the project, the process was automated and the sales team received an email with a report on a daily basis and this helped them see Bookings vs Quota in near real-time. As a famous saying goes “if you can’t measure it, you can’t improve it” (by Peter Drucker) – in this case, the report helped them measure their actual numbers against their goals and helping them improve their sales numbers which directly hits their top-line!

Tools used: SharePoint report subscription, SQL server analysis services, SQL Server Integration services, SQL server reporting services & Excel.

Mockup:

Note: Drill down reports are not shown and the numbers are made up.

Sales Team bookings vs quota dashboard

Advertisements

Cost Driver’s Dashboard for a Supply Chain Executive:

Supply Chain Cost Drivers Profitability Dashboard
Standard

Summary:

Profitability equals revenue minus costs – To that end, A supply chain executive is mostly focused on optimizing cost elements to drive profitability. Here’s a mock up of a dashboard created for an executive to help him keep an eye on the overall health while making sure he gets alerted for key cost categories.

The Dashboard was created using profitability data-set & also had drill down capabilities to analyze numbers for cost buckets like Raw materials, manufacturing & logistics.

Mockup:

Supply Chain Cost Drivers Profitability Dashboard

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:


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

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.

How to Configure SQL Server Analysis services’s Action to Open an URL?

Standard

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:

ssas url action analysis services sql server web page

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:

ssas url action analysis services sql server web page5. 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:

"http://servername/site/Pages/OrderRef.aspx?Search&ID="+[Order].[Order ID].currentmember.member_caption

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:

"Open Order Entry page for Order ID: "+[Order].[Order ID].currentmember.member_caption

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:

excel ssas url action analysis services sql server web page

How to create an Average Aggregation in SQL Server Analysis services?

Standard

Problem:

How do create a measure that does an average over a field from fact table? You can’t find it the “usage” property while trying to create a new measure:

SQL Server Analysis Services Average Aggregation

Solution:

Before i show you the solution, I want you to know that this is a Level 100 solution to get you started – so depending on the complexity of your cube the calculated measure that you are about to create may or may not perform well – if it does not perform well, you might have to dig a little deeper and here’s one blog post to get you started: URL

OK, back to topic! Here are the steps.

SCENARIO: you need average of Sales Amount.

1. Create a SUM OF SALES AMOUNT measure

Steps: Open cube > Cube Structure > Right click on Measure Group > New Measure > Usage: “SUM” > Source Table: Pick your Fact Table. In this case let’s say it’s Fact Sales > Source Column: In this case, lets say it’s SALES AMOUNT

2. Create a COUNT OF SALES measure (important: row count vs. non empty count – this is not a developer’s choice, a business user needs to define that)

Steps: Open cube > Cube Structure > Right click on Measure Group > New Measure > Usage: count of rows OR count of non empty values (again this is not developer’s choice, a business user needs to define this) > Source Table: Pick your Fact Table. In this case let’s say it’s Fact Sales > Source Column: In this case, lets say it’s SALES AMOUNT

3. Create a Calculated Measure that equals (SUM OF SALES/COUNT OF SALES)

3a. Switch to Calculations section > create a new calculated member:

SSAS Analysis services new calculated measure

3b. Complete Name, Format String & Associated Measure Group. For the Expression, use the following expression. Please use this as a starting point for your measure:

IIF([measures].[COUNT OF SALES]=0,0,[measures].[SUM OF SALES AMOUNT]/[measures].[COUNT OF SALES])

4. Before you test it, if you don’t need the SUM OF SALES AMOUNT and COUNT OF SALES measures than don’t forget to hide them!

Conclusion:

In this post, you saw how to define a measure with average aggregation is SSAS.

SQL Server Analysis services warning: “The name specified for the attribute relationship differs from the name of the related attribute”

Standard

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”

Standard

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.

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.

SQL Server Analysis services – How to set the order by attribute sort key?

Standard

Problem:

How to sort the dimension attribute by something other than the key and name column? How do you set the “OrderBy” property?

Example: You have created an Inventory age buckets 1-50,51-100,101-150 and so if a business user uses this dimension attribute then the sorting won’t be logical. It would be 1-50, 101-150,51-100 – so how to show the buckets in the logical order?

Solution:

1. make sure that the table/view that you are bringing in has the sort key.

Example:

1 SSAS Attribute order by sort key2. Now, switch to SSAS and open your dimension. I am assuming that you’ve already configured your data source views and you are already bringing in these columns in the dimension:

Dim Inventory SSAS SSIS VIEW Data source VIEW

3. Let’s start with hiding Aging Bucket Sort key so that it’s not visible to user. Change the AttributeHierarchyVisible to False

4. Now, switch to Attribute Relationships – Right Click on Aging Bucket and click on New Attribute Relationship. And set the attribute relanship between Aging bucket and Aging Bucket Sort Key

Attribute Relationships SSAS

And you should see something like this in your attribute relationship section:

SSAS Attribute Relationship Sort Key

5. Now, one more thing to configure. Go back to dimension structure section. Open the properties section for the Aging Bucket Attribute and change the OrderBy property to AttributeKey. Also, change the orderByAttribute property to Aging Bucket Sort Key (in your case, choose the sort key that you have)

SSAS Order Sort by attribute property

That’s it, after you process the model then you should see the attribute being sorted based on the sort key that you had.

Conclusion:

In this post, you saw how to configure sort/order property of a dimension attribute.

SQL Server Analysis Services (SSAS) multidimensional cube: How to create a role playing dimension?

Standard

Summary:

I was recently asked by a SSAS developer about how to set up a role-playing dimension so I did a brief demo to show how to set it up. In this post, I am document the steps to help you get started:

What is role-playing dimension?

What if you want to see sales data by Order Date as well as Shipped Date? You can one date dimension that is used more than once for seeing sales data by order date and shipped date. In this case, date dimension becomes a Role Playing Dimension.

Steps:

1. Open SQL Server Data Tools and SSAS project that you are working on.

2. Go to Data Source Views from the solution explorer.

3. Make sure that the relationships are specified between dimension (e.g. date dimension) and Fact Table (e.g. Fact Sales). Example: If you’ve OrderDateSK and ShippedDateSK in your fact table, then establish a relationship between the both columns with the Primary Key of the Date dimension.

SSAS Data Source View Role playing dimension

Note: If you do not have the relationships specified in the data source views, there’s an alternative to go to dimension usage for a cube and setup role playing dimensions. since this is targeted to get you started, I am not covering the steps for that.

4. Now go to the Dimensions section in the solution explorer and add a Dimension that uses the existing data source view from step #3.

5. switch to Cubes.

Now, if you don’t have a cube created yet then you can use the cube wizard if you want and as long as you have the relationships specified at DSV’s, it should create a role-playing dimension.

Alternatively, you can also create a new dimension. In the Design pane, lower left section, you’ll see dimensions.

Add Dimensions Cube SSAS

select your dimension that you created in step #4 that’s a role-playing dimension from the list and click on OK. And it creates a role-playing dimension for you! So all you had to do was to make sure the relationships are created between the fact and (role-playing) dimension table and SSAS does the rest of the work for you

SSAS Role Playing Dimension

 

Note: for the demo I didin’t rename the dimension attributes but you do all that good stuff too when you are building the cube for users!

Conclusion:

In this post you got started on how to add a role-playing dimension in a SSAS cube.