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:

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

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.

SSAS Tabular: How to mark a table as Date Table?

Standard

Problem:

For time intelligence in SSAS Tabular models, it’s important to mark a date table. So, how do you do that?

Solution:

Here are the steps:

1. After you have the data imported in a table, in the model designer, select the date table

2. Also, make sure that necessary relationships are created between date table and fact tables.

3. Now, on the menu bar, select Table > Date > Mark as Date Table

SSAS Tabular mark as date table4. A dialog box should ask you for a unique identifier of the date table, so it could be a date column which has the unique columns for the date table that you imported.

Also, make sure that the unique identifier does not have blank values because the model designer will not allow you to select it as your unique identifier and so you may have to go back to your table properties and put a logic to filter out blank values or make sure that the a row with blank value has a value assigned to it.

After you do that, you’re all set to go! I hope this helps.

Mapping Business Intelligence Developer’s Tools: Microsoft SQL server & SAP Netweaver BW

Standard

This Post is NOT about Microsoft BI VS Sap BI. Then What is it?

well, I have been playing with SAP’s Netweaver BW Tools for past three months now as a part of a Business Intelligence class that’s about to conclude – Also, I have been involved with work on Microsoft’s SQL server Business Intelligence Tools. So I thought – it would be FUN to map  SAP Netweaver BW Tools (that I got to play with in an academic capacity) and Microsoft’s Business Intelligence Tools (which is currently what I am working on) – so, here you go:

Tool in Microsoft BITool in SAP Netweaver BW
ETL (Extract, Transform, Load)SQL Server Integration Services (SSIS)SAP Netweaver BW: Data Warehousing Workbench
CubeSQL Server Analysis Services – Multidimensional Mode (SSAS)SAP Netweaver BW: Data Warehousing Workbench: Modeling
Report Design Tool and Reporting Layer(It’s not an exhaustive list and does not include third part tools)
  • SQL Server Reporting Services (SSRS)
  • Report Builder
  • Excel (Excel Services/Pivot Tables)
  • PerformancePoint
Business Explorer (BEx):

  1. BEx Query  Designer
  2. BEx  Analyzer (Excel Add-in)
  3. BEx web Analyzer

 

Data MiningData Mining Projects in SQL Server Analysis ServicesSAP Netweaver BW: Data mining – Analysis Process Designer

Note about SAP BusinessObjects: I mapped the Tools in Microsoft BI with the tools that I got to study in my SAP class. Then I was searching what’s the current scenario in SAP world (I know about Microsoft’s!)– I learned that SAP BI world is comprised of TOOLS in SAP Netweaver BW + SAP BusinessObjects (BO). And in the course I studied the following components of Business Objects:

  1. Web Intelligence for ad-hoc query and reporting
  2. Crystal Reports for enterprise reporting
  3. Xcelsius (BO Dashboard) for Dashboard designing

For those interested I am also mapping few terms used while cube development in Microsoft BI and SAP Netweaver BW

Microsoft: SSAS Multidimensional mode
SAP Netweaver BW
CubeInfoCubes
DimensionsCharacteristics
MeasuresKey Figures
Data Source Views (DSV’s)Data Source

Note:

1) I have not mapped the Tools in Self Service BI space.

2) This comparison is not for deciding between Microsoft BI vs. SAP Netweaver BI/SAP BusinessObjects – this post is just meant for mapping tools available in Microsoft BI and SAP Netweaver BW and so if you are an expert in say Microsoft BI – this post will help you see what corresponding tool are available in SAP Netweaver BW world. Consider it as a starting guide for your research.

3) Note the date the post was written – the name of the products may have changed in future. refer to official sites for latest & greatest!

Thanks for reading.