SSAS allows a large amount of flexibility in its ability to handle inconsistencies between keys in the fact table and the available keys in the dimension table, that is, the way that it treats fact data when there is no dimension key found to match it to. This post looks at error configurations in SSAS and the outcomes of various processing options.
When the measure group is created it inherits a default error configuration. This can be seen in the properties of the measure group (as below). In this situation, the error for a KeyNotFound error (which occurs when the fact key does not find an appropriate dimension key) will raise an error. Because the default behaviour is to allow zero errors, (KeyErrorLimit = 0) and the error action is to stop processing (KeyErrorLimitAction) processing will fail when the fact is processed without a corresponding dimension key.
Default Error Configuration
If we process without referential integrity, you will see an error and warning in the process dialog indicating processing stoped because of a limit was reached and warning showing the attribute keys that could not be found.
When the measure group is processed through an xmla command, the same error message is returned as the results for processing.
Over-riding the default behaviour at processing
The default processing behaviour can be altered at processing by setting the error configuration for the process operation. Generally, there are two approaches for managing these issues. Firstly, you can choose to recognise the error, manage it but not stop processing (that is, you stop logging the error) or secondly, you can choose to ignore the error completely (so that it does not register as an error).
Recognising the Error
To stop logging, you simply change the On error action setting dim the ‘Dimension key errors’ tab to stop logging (as below). While the measure group will process successfully, you will be warned that key errors have occurred.
The generated XMLA includes an ErrorConfiguration tag for the limit action;
When the measure group is processed, processing is successful however, the errors are logged.
While this may seem a suitable method for processing the measure group, the method has implications for SQL Agent jobs use a step to process and execute the command as XMLA (an SQL Server Analysis Command step type). In this situation, even though the cube processes as expected, the agent step raises an error. Note that the step only fails when a facts key cannot be found. When there are no fact/dimension errors, the step runs as successful.
Ignoring the Error
An alternate method of control is to ignore the error completely. In this instance we simply set the ‘Key Not Found’ error to ignore the error (as below);
In this situation, the an agent job will process regardless of any fact key errors and there will be no warnings to indicate which keys were not found.
Changing the Default Measure Group Behaviour
Needless to say that managing this error configuration can occur at the measure group level (measure group properties à custom error configuration as above). The benefit of setting the error configuration for the measure group is that individual partition processing does not require specific error configurations in-order to process.
Including the Fact
The final consideration for the treatment of fact / dimension key errors should be how to treat a fact record that has no related dimension key. The two options for this are to convert the record to an unknown member in the violating dimension or to disregard the record completely. Both options can be set in the KeyErrorAction property for the measure group.
I have recently been involved in a large project that created a large number of SSIS packages which in turn lead to an extremely large number of connection managers. In BIDS this is unmanageable! True, SSIS does allows the creation of a shared data sources which can in turn be added to a package (essentially creating a project data source), however, the use of these data sources does have some drawbacks. Firstly, when the package is saved; it saves the connection string associated with the data source. When the data source is changed, the package retains the old connection until the package is reopened in BIDS. Secondly, the connection is lost when the package is published (to the file system). Furthermore, there is no convenient method of obtaining information about the connections amongst the packages. To manage the connections, I created a small app that has the following features.
The lists all packages I in a directory (listed in the right pane). In doing so it builds a library of all connections in the packages. So (for example), if we chose a connection type, only the connections that have that type (and the packages that have those connections) are retained in the connection and package list box.
If we then chose a connection, only the packages that use that connection are highlighted. Because I was looking for consistency in the connection strings, I show the first package connection string and identify if there are any different connection strings amongst the other packages (by highlighted by a salmon colour).
Since the goal is to manage the connections I’ve also included a ‘New Connection String’ change mechanism, a check to evaluate expressions (as connection strings) and the ability to output a csv report for all connections and packages in the library.
|Identifying packages using connection by type and name|
The principle part of the code revolves around the ManagedDTS library which can be found in the following location / dll ‘C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll’
This assembly allows all SSIS (or dts) management including the creation, alteration and control flow (see http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.aspx ).
In order to alter an existing package we need to create an Application instance and use it to load the package. The following code achieves this;
Microsoft.SqlServer.Dts.Runtime.Application _app = new Microsoft.SqlServer.Dts.Runtime.Application();
Package _p = _app.LoadPackage(this_is_my_file_name, null);
The connection managers within the package can then be managed with the connectionmanager class (for example iterating over them by)’
foreach (ConnectionManager _c in _p.Connections)
or by referring to a specific connection through the connectionmanager class and specifying its index, identity or name;
ConnectionManager _c = _p.Connections["other_oledb_1"];
ConnectionManager _c = _p.Connections;
Finally, we can use the properties / methods to determine the connections type, connection string, expression (connection string expression);
_c.ConnectionString; // connection string
_c.CreationName; // type
Setting properties is relatively easy (just by setting the property value). The only exception to this is setting the connection string expression (which required a call of the .SetExpression method);
_c.ConnectionString = connection_string;
The interesting thing I found for this was that when the connection manager has a connection string expression attached to it, the connection string will not be changed unless the expression is changed. Then when the expresssion is changed, the connection string also changes. I thought this was a little weired.
There may have been scope in the project to use configuration files however, the auditing requirement and the use of child package execution causes a failure within the child package before the configuration was applied. That is, there are some audit events that are fired before the connection can be changed and where these would otherwise err, the child package fails.
I am happy to share this project with anyone that wants it. Just drop me a line and I’ll send it through (subject to the standard disclaimer!)