Creating YTD Calculation in SSRS

Recently I wrote on a method to create YTD calculations using MDX without making modification to the SSAS cube. However, there will often be scenarios where you need an YTD calculation and you are not using a cube as a data source. In these cases you are at the mercy of making an YTD calculation using SQL. For those that have done this before, you know how difficult that can be and how much code you have to write to get that working.
You can creating this using a combination of a Tablix, Matrix, or a Chart object. The required SSRS Expression needed is RUNNINGVALUE. The example of this expression is as follows…

= RunningValue(Fields!Total_Cost.Value,Sum,"Chart3")

Here is an example of using RunningValue to accumulate YTD values…

 

  Using this example, I specific the following formula in the Tablix for the YTD…

 

 The same could be done for SSRS Matrix and Chart objects as well.

YTD Calculation in Query Designer

One of the most common requested aggregations in MDX is YTD. There are many sources that highlight how to do this in the Cube development environment, but sometimes you don’t have access to the SQL Data Tools / BIDs.

I find using the native YTD calculation problematic when attempting to make YTD calculation outside of the Cube development environment. The YTD function can be extremely slow to process outside of the Cube development environment.  Also, if you’re like me many times you only have access to read the cube but not develop against it. You should then you use the Query Builder to create an YTD calculation.

Basically use the Calculated Member Builder in the Query Designer (whether in Report Builder or Visual Studio).

In my example, I use the following data to create the YTD calculation against the Date Hierarchy PostingPeriod.Fiscal and the Measure.LaborCost.

Aggregate

 (
 PeriodsToDate
 (
[Posting Period].[Fiscal].[Fiscal Year]
 , [Posting Period].[Fiscal].CurrentMember
 )
 ,[Measures].[Labor Cost]
 )

 This how looks in the Calculated Member in Query Designer…

  This is one of the most useful features of the Calculated Member tool – allowing you to create dynamic aggregations without cube authoring..

 

Using SSRS to match colors

There are times when you have to match RGB colors in SSRS to Hex values that are defined by the business requirements. I save myself a lot of time by using websites that do this conversion for you. Here is one site to convert Hex to RGB and vice versa.

http://www.rgbtohex.net/hextorgb/ OR http://www.rgbtohex.net/

Simple but effective.

Getting the exact color

Sometimes you need the exact color codes (RGB or Hex) to match incoming graphics with SSRS colors. I would suggest that if you are not provided with company colors schemes already in this format (RGB/Hex), use the standard colors in SSRS in then work from there.

1.      Select the color you want. Do this by picking any object (text, rectangle, background, etc.) and Select properties. Choose the appropriate object to needs a specific color and then expression. In this example, I’m using Khaki. Select Expression…

2.      Under the Expression Dialog box, you will see the option More Colors in the Value Box. Select More Colors. This will bring you to the Select Color…

 

3.      Select the Color Circle Bar, move it to the top position. And this will give you the Color Sample “Khaki” and another split color for you to manipulate. We can record the RED, GREEN,BLUE color code for Khaki and use that to color object that need to match SSRS objects.

 

NOTE: Where trying to match graphics from incoming applications into SSRS, try using the PNG graphic format instead of JPG. PNG is a good choice for storing iconic graphics in SSRS and often has zero loss of quality regardless of the sizing (which should be small in SSRS). I use this all the time and am never disappointed when importing graphics of this type into SSRS.

Tabular Processing Failures

Tabular cube development is great. But like any other part of MSBI, you 'll run into issues from time to time. Recently I generated the following error while attempting to process a tabular cube....

OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.

A connection could not be made to the data source with the DataSourceID of '8e03aac2-05c6-4712-87d4-af620a257aeb', Name of 'SqlServer <Server and/ Connection Name>.

An error occurred while processing the partition 'dimXXXXX_74d208d3-9774-4094-83c1-a7e7615bfcc0' in table 'dimXXXXX_74d208d3-9774-4094-83c1-a7e7615bfcc0'.
The current operation was cancelled because another operation in the transaction failed.

This occurred shortly after making subsequent updates to the cube across several weeks. My initial thought was that something was corrupted with the project files since it was all working just fine a week ago.

To resolve this issue,  I had to first remove any Connections that were not being used. This included two of the five connections I created in the project. You can remove the connection string if there are no tables tied to the connection. To determine if there are tables connected to the connection, you could simply try to delete it.  To do this, try the following…

1. In the Menu, Select Model
2. Select Existing Connections…
3. In the Select Existing Connection Dialog box, Attempt to delete a connection…The Tabular Model Designer will give you can error indicating “One or more tables are still associated with the data source that is being deleted. Remove the associated tables before deleting the data source: ….” It will list the associated tables.
4. If the Connection is allowed to be deleted, it is safe because that connection is not tied to any tables imported

One that was done, I then changed all the connection strings from using the SQL Server Native Client 11.0 (which is fine as long as the SQL Server is on the same machine as the SSIS development / package environment), but to the Microsoft OLE DB Provider for SQL Server which is a bit more flexible (for local and remote servers).

Both of these together resolved my issue.

But the most important thing is how did I get to have some many connections?

 Well this was caused by improperly adding the new tables. When I would add new tables to the existing tabular cube, I would mistakenly start by selecting “Import from Data Source” instead of correctly using  “Existing Connections”. When you specify Import from Data Source  you will then be forced to add a new Connection which will increase the list of Connections. If you do this enough, you have multiple connection strings that are all the same other than the connection name. This gets really annoying when you process the cube and are prompt for a password for EACH connection. This also potentially introduces annoying issues this discussed earlier.

 

Great resource for SSIS logging

 There are a lot of ways you can create logging in SSIS and some of the most elaborate requires creating essentially a framework consisting of using package configurations, store procedures, and varies queries. Much of this can be done for you automatically in SQL 2012 with its many new logging capabilities. However, for a fast implementation of logging and a reporting solution I would recommend the strategy at one of my fellow bloggers.

One of the better sites around on SSIS logging is at http://troywitthoeft.com.

Logging SSIS processing

Common T-SQL Tasks when building Data Warehouse

I’m often scrambling to find code that I use during the beginning of data warehouse design to accomplish tasks that are easily done in T-SQL. Almost all deal with data conversion/transformation.  Typically I’m creating Date or Unknown Surrogate Keys, Replace values in columns, and having to document this at the same time. 
 

Four SQL Tasks I do all the time (but commonly forget the syntax)...

1.      Converting dates to the YYYYMMDD format. I do this task constantly and recommend this naming convention for date dimension surrogate keys.. This example assumes DateSK will be the name of the Date Surrogate Key.

 SELECT CONVERT(VARCHAR(8), GETDATE(), 112) as DateSK

2.      Remove or replacing a string with a blank (or something else). In this example, I will replace the instances of XYZ with nothing (which is the same as removing).

                SELECT REPLACE (ColumnName,’XYZ’,’’)

3.      Pull all the column names as well as Database Name, Table Name, and data type to prepare for data dictionaries when building data warehouses.

SELECT

       TABLE_CATALOG as [Database],

       Table_name  as [Table Name] ,

       column_name  as [Column Name],

       DATA_TYPE as [Data Type]

                        FROM  information_schema.columns
                        WHERE  table_name = 'Resource_Table$ '
                        ORDER BY ordinal_position

 

4.      Sometimes you simply need to insert data into a dimension with a specific values on a column that specified as INDENTITY. This is common when you have dimension but now you need to add UNKNOWN entries where the Surrogate Key as -1 or some value that represent unknowns.
 
            SET IDENTITY_INSERT Products ON
            INSERT into Products (ProductsSK, ProductName)
            VALUES(-1, 'Hammer')
            SET IDENTITY_INSERT Products OFF

Those are the four tasks I commonly use as but often forget the syntax.

Sorting Dimensions in SSAS

One of the most common tasks that is relatively easy to do in all Microsoft data applications is to sort data. Unfortunately, this is not true when developing cubes in SSAS. You’ll think there would be a simple “sort” option or something where you right-click and sort by XYZ. Nope! Instead, you have to configure sorting using several steps.  

Date dimension data will likely be the most commonly needed type of data to sort. Sorting won’t be an issue for numeric data, but it will be for the alphanumeric data like month names. For example, when I browse my Data dimensions for that represent month names, I get the following months names in this specific order: April, August, December, February….You get the idea, it would be in alphabetical instead of chronological order.. This is of course is useless in the real word.

To fix this we need to specify that the Month Name be sorted by the Month number.  When you design your data source view, it is important that when you bring in a column that needs to be ordered outside of the natural alphabetic or numeric order, then that column needs an associated column just for ordering. In the case we have Month name (that cannot be ordered alphabetically) and Month number (that we’ll use to sort Month name). Here is the process to sort by Month Name by Month number.

1.      Select the Month (or whatever represents the column used for sorting) attribute in the Dimension Structure and Select Properties.

      2.      Change the following properties for the Month Name. 

a.      AttributeHierarchyEnabled property to False  

                    b.      AttributeHierarchyOptimizedState property to NotOptimized
                    c.      AttributeHierarchyOrdered property to False

 

 

3.      We then need to establish Attribute Relationships. In the diagram, right-click the Month Name attribute and then select New Attribute Relationship.

       4.      In the Create Attribute Relationship dialog box, the Source Attribute is Month Name. Set   the Related Attribute to Month.

5.      Since we expect this type of relationship to not change, in the Relationship type list, set the relationship type to Rigid. Select OK to apply changes.

 

6.      Go back to the Dimension Structure tab. Select the Month Name in Attributes. Change the Orderby property to Attribute Key and change the property OrderByAttribute to Month.

You can now reprocess the Date dimension and apply the changes. If now go back into the Dimension browser we can see that the Month names appear in the correct order.

 

We can use this method to sort anything that has an associated sort column. Any item dimension that must be order in some way other than alphabetically, we have to use this type of method.

SSAS cube dimension processing failure.

I was just developing a SSAS cube with relatively no problems. Once I began to process the dimensions , the Cube processing failed and returned the following errors...

OLE DB error: OLE DB or ODBC error: The SELECT permission was denied on the object 'dimTask', database 'Project', schema 'dbo'.; 42000.



Impersonation Information Tab

The first thing I checked was the Impersonation Information tab on the Data Source.

Secondly, I then looked in SSMS and needed to verify that this account had the appropriate SQL Security role to process data from the Project DB (or whatever database). To resolve this problem, I opened up SQL Server Management Studio (SSMS), Under Security, Select the specified account (SSASProc in this scenario), Select the Server Roles in the Login Properties page. I then gave this account sysadmin server role.

 

SQL Server Management Studio

 This solved my SSAS Cube processing problem. I hope this works for you.

Simple Performance Monitoring for SSIS

I come from an operating system support and monitoring background so I’ve always been aware of the manageability tools available in Windows operating systems and the tools available on the market. I also have learned that the native tools in windows have improved so much over the last decade that anyone can build a relatively simple yet power monitoring process.

 

Performance Monitor, System Monitor, or Reliability and Performance Monitor (or whatever we call it next)

 

Performance Monitoring (or whatever we want to call it today) has been introducing since Windows 3.51 and has gotten better over the releases on subsequent Windows operating systems.  It’s still relatively complex for users that don’t understand operating systems components, but even with some basic computer OS knowledge (i.e. I know what I CPU, RAM, and Disk is used for) you can gather and understand some of the basic information performance statistics. In our discussion, I’ll speak about SSIS related performance stats.

 

Data Collector Sets via Permon.exe

Within Performance Monitor application, there is an additional toolset called Data Collector Sets. Data Collector Sets are used to establish what will be collected, how frequently, and in which format.

What to Collect?

Adding SSIS Performance Counters

This will depend heavily on what your role is and what needs to be monitored from a performance standpoint. From a purely SSIS standpoint (specifically SSIS 2008), I recommend the following performance counters to be captured…

•    SQLServer:SSIS Pipeline 10.0 - Buffers in use 
•    SQLServer:SSIS Pipeline 10.0 -Rows read
•    SQLServer:SSIS Pipeline 10.0 -Buffers spooled
•    SQLServer:SSIS Pipeline 10.0 - Rows written
•    SQLServer:SSIS Service 10.0 – SSIS Package Instances

These five counters will give you a good view of the overall performance of your SSIS package processing. Buffers represent the amount of memory available to do SSIS work. So if you allocate 1MB for activity the Buffers is use should present up to 1MB. This is useful because it tells you how much memory is being used and if it has met the upper limit that has been allocated. This gets a bit more complicated as you add more packages and run them in parallel because if you allocate different amounts a memory between packages it would be difficult to see if one package has maxed out is assigned amount of memory. Its gets even more complicated if you commonly specify that DefaultMaxBufferRows exceeds the available to the DefaultBufferRows in the package setting. Because of this, we need to look at Buffers Spooled to see if we are sending SSIS work to disk (very bad). 

How Frequently?

The frequency of how you should monitor SSIS activity depends on when it executes, how many packages are being monitored, and how much disk space you are willing to assign to this monitoring process. Since the premise around this post is using Windows performance counters to address large SSIS installations, this assumes that your environment SSIS processing is an issue. Therefore, a collection interval of about 15 seconds would be suitable. If your packages typically take a few minutes to process, we don’t need to monitor this activity as that would generally be a suitable processing expectation. However, SSIS processing that takes hours is where we will have the most use of collecting this performance data.

 

What Format?

As a SQL guy, my preferred method would be to use a SQL format which allows you to insert the data into an existing SQL Database. To have this work, you need to have to establish a DSN so that the Data Collector Set properties can connect and insert to the targeted database. If you don’t have access to a database DSN (which I would not be surprised as most BI developers will not often have created these) then you can simply retain the performance monitor format (*.mlb) or write the results to a CSV file.

 

Overall

 

Get in the habit of using the native OS tools to help access the performance issues within your BI environment. Although I spoke specifically on SSIS, Data Collector sets can be created for other BI applications SSAS and SSRS processing/usage as well.