# 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:

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

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:

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.

# SQL Server Query Fundamentals: A Simple example of a Query that uses PIVOT:

Standard

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
go

— Destination data using PIVOT function
pivot( avg(test_val) for test_Desc IN (Test1,Test2,Test3,Test4,Test5))
as Tests

[/code]

# 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.

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.

# TSQL – Quick note about numeric data type to solve “Arithmetic overflow error”

Standard

Problem:

You are working on a query where you are trying to convert source data to numeric data type and you get an “Arithmetic overflow error”.

Solution:

Let’s understand this with an example:

Here’s the source data: 132.56000000 and you want to store just 132.56 so write a query that looks like:

cast([source language=”column”][/source] as numeric(3,2)) as destination_column_name

and after you run the query its throws an error “Arithmetic Overflow Error” – so what’s wrong?

The issue is that you incorrectly specified the precision and scale – by writing the query that says numeric(3,2) you are saying I want 3 data places with 2 on the right (after decimal point) which leaves just 1 place for left.

what you need to write is numeric(5,2) – and this will have 2 places on the right and leaves 3 places for left.

so after you run this, it shouldn’t complain about the arithmetic overflow error. you just need to make sure that the precision and scale of the numeric data type is correct.

Conclusion:

In this post, you saw an example of how to correctly use the precision and scale in the numeric data type and that should help you solve the arithmetic overflow errors.

# SSIS – How to use Execute SQL Task to assign value to a variable?

Standard

Problem:

How to use Execute SQL Task in SSIS to assign value to a variable?

Solution:

This is a beginner level post so I’ll show you how you can use Execute SQL Task to assign a value to a variable. Note that variables can also be given full result set. With that said, here are the steps:

1. Create the query against the source system

Example: ((Note the column name, this will be handy later!)

2. Open SSIS Project > Create the variable

Example

3. Now, drag a Execute SQL Task to Control Flow. Rename it. And go to Edit. Configure SQL Statement Section

4. Now, since we want to store a value to the variable, change the Result Set property to Single Row

5. One last step, go to result set section and map Result Name (remember the column name from #1?!) with Variable Name:

That’s it! Related article: How to see value of variable during Run Time?

Conclusion:

In this post, you saw how to use Execute SQL Task in SQL server integration services to assign a value to a variable.

# 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:

2. 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:

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

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

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)

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.

# Design pattern for making staging table loads incremental in SSIS:

Standard

Summary:

This is a beginner level post targeted at Developers who are new to SSIS and may not have worked on making a SSIS staging load package incremental. In this post, I’ll share a design pattern that I’ve used to make staging loads incremental which pulls in just new or changed rows from source system.

Tutorial:

Before we begin, why would you want to make a staging load incremental when pulling data from source systems? Two main reasons: 1) the source system may not keep historical data but your Business Intelligence system needs to have it 2) it is also faster and puts less strain on source system while doing data pull.

since this is a beginner’s level, I am going to show you a design pattern when you have a column in the source system that can identify New or Changed Rows. If you do not have a column in the source system that identifies new or changed rows then this topic becomes an advanced level and is out of scope for now.

with that said, let’s see the steps involved.

1) I’ve this kill and fill (a.k.a Full Load) package in my SSIS dev environment:

2) now, let’s make this incremental. so I’ll go ahead and delete the Execute SQL Task that truncates the data.

3) Now, we need a way to be able to pass in the query in our DFT that gets only the new or changed rows. The source system that I am using has a field called modified date and that’s what I’ll be using to pull in new or changed data.

4) Let’s create the query using the help of variables, execute sql task and script task. (Later, we’ll store in the query in a variable and use that variable in the Data Flow Task)

4a) create ModfiedDate and Query variables

4b) create an Execute SQL Task to run the query to get the max ModifiedDate and write it in the ModifiedDate variable that you created.

4c) create a Script Task to get the query using the ModifiedDate variable. This query will extract only new or changed rows from your source system

[code language=”vb”]

Dim ModifiedDate As String
Dim sQuery As String
ModifiedDate = Dts.Variables("ModifiedDate").Value.ToString
sQuery = String.Concat("SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber],[OrderQty],[ProductID],[SpecialOfferID],[UnitPrice],[ModifiedDate] FROM [sales].[SalesOrderDetail] where [ModifiedDate] >= ‘", String.Concat(ModifiedDate, "’"))
MsgBox(String.Concat(" ", sQuery))
Dts.Variables("Query").Value = sQuery

[/code]

5) Now, go to variables section and give a default value to user::Query variable because if you do not do this you won’t be able to go to next steps.

6) Go to Data Flow and change the OLEDB source to use the SQL Command from variable and use the user::Query variable

7) Switch to Control flow and Make sure your precedence constraints are set to run Execute SQL Task > Script Task > Data Flow Task

8) Run the package and you should see the dynamic query that gets generated.
Tip: sometimes it’s helpful to run this query that’s generated against the source system for troubleshooting purpose.

9) On the successful run of the package verify that only new rows got added to the staging table. Also, if there are duplicate rows in the staging table, this might need to handled during the dimension load or fact load. you can also consider having the logic in place here to avoid duplicate records in your staging table.

That’s it!

Conclusion:

In this post, you saw how to make a staging load package incremental.

Similar Blog:

SQL Server Integration services: How to write a package that does Set based updates?