A duplicate attribute key has been found when processing....

Hi folks,

I was bit of experimenting with the hierarchies in analysis services and i came into an error, that i want to show you. I've installed the adventureworksDW database and built a dimension on the dimdate table of that database. I've created a small hierarchy:

A simple hierarchy as you can see. When i process this,  the following error occurs:

This occurs because the monthname appears multiple times in multiple years and they has to be unique. Okay well there's a keycolumn property of the month attribute. Simple, just make the key unique and problem solved! So i added the datekey to key column and filled in the name column:

Process Succeeded!!! Okay, let's continue with something more interesting. This is too easy stuff. Hey well wait...This is my hierarchy:

This isn't what i want. This hierarchy has a lot of multiple members with the same value. Choosing a dimension key for making unique values is not a proper thing to do! Let's change the datekey to calendar year.

Process Succeeded! Let's see the result:

A simple post about a simple problem but it took my a while before i got it solved (at first i had a more complex hierarchy). Just making attributes unique by just adding a unique field could give you undesired results.

Greetz,
Hennie

Lookup join with connection manager and transform

This blog is about the cache connection manager (CCM) and the cache transform. These two allows you to load the lookup cache from any source. The cache connection manager holds a reference to the internal memory cache and can both read and write the cache to a disk-based file. The cache transform is a new feature in SQL Server 2008.

The lookup transform can utilize this cached data to perform the lookup operation. The cache connection manager can persist the cache to a local file, allowing the cache to be shared between multiple SSIS packages and steps within a package. A perfect example of where this will useful is in the extract, transform and Load (ETL) packages where when we are processing the fact table we have to lookup to to application vs surrogate key. In my datawarehouse projects I call this the Lookup table (LU)). So if we create a lookup cache per dimension, the time of loading of the fact will be decreased when we use this features. It will even speed up more when a dimension (cache) is used by multiple facts.

To demonstrate how to use the new lookup and cache transforms, we will create a simple SSIS package a s shown below:

The DFT_Cache_Customer will cache the information from the Customer table into a .caw file. For this example is used the following query:

1. The first thing you have to do is create cache connection manager. Right click in the connection manager area of the control flow design surface and selkect new connection. Select CACHE from the add SSIS connection manager dialog and then click on the Add button

2. The next thing is to set some properties that need to be configured. On the general tab click the Use file cache checkbox and enter a filename for this cache; this will persist the cache to a local file, allowing it to be reused by other tasks in the package or other packages.

3. Click on the columns tab to configure the data columns to be stored in the cache.
 

4. Build the customer cache (DFT_CacheCustomer)  

5. The OLE DB Source selects the customer data : SELECT CustomerID, AccountNumber FROM Sales.Customer.
 

6. Then setup the cache transform component. This one loads the cache . The cache transform requires a cache connection manager which defines the cache. You could create a new onebut we already created on in the connection managers pane.
 

7. Click on mappings to map the input columns in the dataflow to the destination columns in the cache.

 
8. From a former post you can recognize the following SSIS snippet
 

9. The lookup transform needs two adjustements. The first one is to set a Connection type to Cache connection manager. This allows to pre load the cache from the file we just created.

 
10. The next thing you have to select is the Cache connection:

 
Conclusion: As is already said in the intro, this feature can speed up a lookup function of loading a fact and especially dimension surrogate keys. Even more when the cache is used multiple times.
 
Bye,
Hennie

Change Data Capture (CDC)

Change data capture is a way of capturing changed data from a source system within a specified window of time. These changes includes inserts, updates and deletes and the required window of time may vary from minutes, hours to weeks. It’s a new feature since SQL Server 2008 and it’s based on replication and designed with the SSIS team.

CDC is available on certain editions of SQL Server, namely SQL Server 2008 Enterprise, Developer and Evaluation editions. As an ETL developer you could be tempted to turn CDC on for every table, but that is a bad idea. Although CDC has low overhead, it does not have zero overhead.

Below you can see the steps for turning CDC on.

Enable CDC on at database level. This will let SQL Server set up the required infrastructure that we’ll need later. It creates a schema called cdc as well as security, functions and procedures.

USE AdventureWorks2008R2

GO
--enable CDC on the database
EXEC sys.sp_cdc_enable_db
GO

--Check CDC is enabled on the database
SELECT name, is_cdc_enabled
FROM sys.databases
WHERE database_id = DB_ID()

Make sure that the SQL Server agent is running on the same server where CDC is turned on. Start up SQL Server 2008 R2 configuration manager and check whether the agent is running.
 
Now it’s possible to enable CDC functionality on a table. For instance:
 
USE AdventureWorks2008R2

GO

--enable CDC on a specific table
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'HumanResources',
@source_name = N'Employee',
@role_name = N'cdc_Admin',
@capture_instance = N'HumanResources_Employee',
@supports_net_Changes = 1;

And this results in :


Job 'cdc.AdventureWorks2008R2_capture' started successfully.
Job 'cdc.AdventureWorks2008R2_cleanup' started successfully.

You could check whether CDC is enabled for any particular table with the following query:

--Check CDC is enabled on the table

SELECT [name],
is_tracked_by_cdc
FROM sys.tables
WHERE [OBJECT_ID] = OBJECT_ID(N'HumanResources.Employee')

--Or use the CDC help procedure
EXECUTE sys.sp_cdc_help_change_data_capture
@source_schema = N'HumanResources',
@source_name = N'Employee'

So now CDC is turned on for the employee table. When this enabled SQL Server uses a default naming convention to create a shadowtable in the same database, called cdc.HumanResources_employee_CT. This table has the same schema as the source table, but is has extra metadata columns that CDC needs to do its magic.

SELECT * FROM cdc.HumanResources_Employee_CT

__$start_lsn = identifier of the original transaction
__$segval = Order of transactions
__$update_mask =
__$operation = type of operation (1 = delete, 2 = insert, 3 = update (before image), 4 (after image), 5 = merge)

Okay let’s make an update to this table and see what will happen. Hey, for every update there are two records: one before and one after image.

Ok now lets play with some of the API stored procedures and functions. With the following code you can retrieve the actual values of latest present records.

USE AdventureWorks2008R2

GO

DECLARE @dBeginTime AS Datetime = GETDATE() -1
DECLARE @dEndTime AS Datetime = GETDATE()
DECLARE @bFromLsn AS Binary(10) = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @dBeginTime)
DECLARE @bToLsn AS Binary(10) = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @dEndTime)


DECLARE @bMinLsn AS Binary(10)= sys.fn_cdc_get_Min_lsn('HumanResources_Employee')

IF @bFromLsn < @bMinLsn SET @bFromLsn = @bMinLsn

SELECT * FROM cdc.fn_cdc_get_net_changes_HumanResources_Employee(@bFromLsn, @bToLsn, N'all with mask');

Ok let’s use this information with SSIS. We could build a robust and efficient ETL solution.
 

 
Thats it for now!!
 
Gr,
Hennie

Using the lookup join - Partial cache

This blog is about the partial-cache mode. In another post of mine, i explained a basic example of the lookup transform. In this post I will go a bit deeper into the lookup transform. The partial – cache mode gives you a middle ground between the no-cache and full-cache options. In the mode the component only caches the most-recently used data within the memory boundaries specified. As soon as the cache grows too big, the least-used cache data is thrown away.

The partial-cache mode provide another cache feature called the miss cache. If you use these two together you can gain further performance. So first I reused the package from first post about this item:

All the other tabs I showed in my former post (1) are almost the same: in the General tab the partial cache is changed to partial cache and i corrected the error in the connection tab

The next screendump shows the advanced tab:

I just changed the cache mode from full cache to partial and did some adjustments in the settings of the advanced tab.

Greetz,
Hennie

Failure adding assembly to the cache

Hi,

A small post about GACUTIL. Currently, i'm building a custom source adapter for SSIS, reading a custom source. For this purpose i'm using Visual Studio Express 2010 C#. So, i am building the assembly and i want to register this in the Global Assembly Cache (GAC) with GACUTIL. So i executed this:

"C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin\gacutil" /if "C:\ssis\SSISSourceAdapter\SSISSourceAdapter\bin\Release\SSISSourceAdapter.dll"

and this gave me following error:

Failure adding assembly to the cache: This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

So i thought that i should download the .net 4.0 framework SDK released in v7.1 from here . So, i have now three folders in C:\Program Files\Microsoft SDKs\Windows: v6.0A, v7.0A and v7.1. I executed GACUTIL from v7.1 folder and still it didn't work. After some search on the internet, i found out there's a folder in the v7.0A folder: C:\Program Files\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools. I executed with this GACUTIL commandline from this subfolder:

"C:\Program Files\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools\gacutil" /if "C:\ssis\SSISSourceAdapter\SSISSourceAdapter\bin\Release\SSISSourceAdapter.dll"

When i executed GACUTIL from this folder, the insertion was accepted. Not sure why the GACUTIL from v7.1 folder didn't work.

Greetz,
Hennie