Lookup join - Partial cache - Package Validation Error

In a former post i blogged about the full cache and i was figuring out how the partial cache is working. So i changed the full cache mode to partial cache and immediately i ran into an error.

and more specific:

TITLE: Package Validation Error

------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at DFT_AdventureWorks [Lookup [411]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".
Error at DFT_AdventureWorks [Lookup [411]]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties.
Error at DFT_AdventureWorks [SSIS.Pipeline]: "component "Lookup" (411)" failed validation and returned validation status "VS_ISBROKEN".
Error at DFT_AdventureWorks [SSIS.Pipeline]: One or more component failed validation.
Error at DFT_AdventureWorks: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)

I was trying to figure out what was going on and i found out i has something to do with this query in the lookup transform:

--16989 out of 19820
SELECT CustomerID, AccountNumber
FROM Sales.Customer
WHERE CustomerID % 7 <>0;

I've found out there are a couple things wrong with this query:

• --16989 out of 19820 is not allowed in the query window.
• ; is also not allowed in this window.

What i also found is that when you press OK immediately after you corrected the error, the error dot will stay there!! But when you go to the Columns tab and when you press ok after this, the error disappears(??). Bit awkward and this cost me a lot of frustration before i found out about this.

Greetz,
Hennie

Using the lookup join - Full cache

The lookup component solves join issues by using caching one of the datasets in memory and then compares each row arriving from the other dataset in its input pipeline against the cache. In the example below you can see the full-cache mode, whereby the lookup component stored all the rows resulting from a specified query in the memory. The benefit of this mode is that lookups against the in-memory cache are very fast.

So, first i started to build a package according to the below diagram:

First I build a query, something like this:

SELECT
OH.SalesOrderID,
OH.OrderDate,
OH.CustomerID
FROM Sales.SalesOrderHeader OH

This is the main stream of data that will flow trough the pipeline. It’s also a best practice to use the largest table for passing through a lookup transform with the data flow input. After this, open the Lookup transformation editor. And fill in the general tab like below:

Don’t forget to select the no matching entries.

After this, open the connection tab. This tab will show the query which is used for combing the data with the main data flow stream:

--16989 out of 19820

SELECT CustomerID, AccountNumber

FROM Sales.Customer

WHERE CustomerID % 7 <>0;

I added the WHERE clause for showing the non-matching record going into a other direction. Next is to select the joining columns and this is the CustomerID. After selecting these fields everything should be ready for running and you can see the result below:

Conclusion is that this transform can be used in loading a dimension. You could use the match output for the matching records and the no match records could be processed separately. These non-matching records are in fact ;-) dummy records. No records should disappear when you build a starschema because calculations will show wrong results. When a customerID is not found in the customer dimension, it should point to a dummy record in customer dimension, mostly the -1 record.

So that's it for now,

Hennie

Merge join vs Lookup transform vs relational join

In this post i'll compare the two join components: the merge join and the lookup join with the relation join in order to determine which solutions is the best possible way to solve your problem regarding joining data from certain data sources. This post is based on SQL Server 2008 R2.

Merge Join
The merge join is used for joining data from two or more datasources. The more common ETL scenarios will require you to access two or more disparate datasources simultaneously and merge their results together into a single datasource. An example could be a normalized source system and you want to merge the normalized tables into a denormalized table.
The merge join in SSIS allows you to perform an inner or outer join in a streaming fashion. This component behaves in a synchronous way. The component accepts two sorted input streams, and outputs a single stream, that combines the chosen columns into a single structure. It’s not possible to configure a separate non-matched output.

Lookup
The lookup component in SQL Server Integration Services allows you to perform the equivalent of relational inner and outer hash joins. It’s not using the algorithms stored in the database engine. You would use this component within the context of an integration process, such as a ETL Layer that populates a datawarehouse from multiple non equal source systems.
The transform is written to behave in a synchronous manner in that it does not block the pipeline while it’s doing its work, mostly. In certain cache modes the component will initially block the package’s execution for a period of time.

Conclusion: There's only one reason for using the merge join and that is a lack of memory, otherwise always use the lookuptransform. There's done lot of improvement of the lookup transform in SQL Server 2008.

Greetz,

Hennie

Plotting spatial data on a map (Part III)

In the first two parts (part 1 and part 2) i explained the geocoding process and now let's plot the spatial information on a map with SQL Server 2008 R2 Report Builder 3.0. This article is bases on a article of siddharth Mehta from the SQLserverperfomance site. You can download Report Builder 3.0 from here. Report Builder 3.0 introduces additional visualizations including maps, sparklines and databars which can help produce new insights well beyond what can be achieved with standard tables and charts.

1. Ok this is the starting point. I added some columns but you won't need it for this exercise.

2. Open Report Builder, create a new blank report and a new datasource. Something like this:

 

3.  Create a dataset:

4. Startup the map wizard:

5. Select SQL Server spatial query

6. Choose spatial data and map view options:

7.. Choose color theme and data visualization

5. And this is the result.

  

 

So, this is geocoding....

Greetz,

Hennie

Inmon vs Kimball according to....Inmon himself

What the... a couple of weeks ago Inmon produced an article on his blog. A smile appearred on my face. Inmon talking about the comparison between Kimball and Inmon. Interresting. Let's see what he has to say. He starts a bit grumpy and i had a deja vu remembering him presenting at the BI event where i already had the impression that he was a bit grumpy. But ok....Let's see what he has to say...

So first he explains the two different systems, first Kimball and then Inmon. In the part about Kimball he explains that Kimball "in order to resolve differences of granularity between fact tables, conformed dimensions are used.". Hmm i never thought about that, in that manner. In my view conformed dimensions are used for getting an integrated view between fact tables and so you can use the conformed dimensions for 'hopping' between business processes and combine the measures from this different facts. So this way you can consolidate your datawarehouse. It's not quite clear what he meant by this statement. What are we trying to resolve with conformed dimensions, concerning granularity? When building an integrated view of facts with different granularities still needs some additional logic. When one fact is connected to one dimensionrecord, another fact record can connect to two dimension records. From my viewpoint, conformed dimensions are not a solution for resolving granularities between facts. They can aid building integrated views, like dashboards, etc.

Inmon explains some stuff about his metholodogy, which i agree most of it. The only point i want to make is that Datavault by Linstedt is DW2.0 proof and could be a better alternative than the relational approach of Inmon, but that's my opinion.

Okay, the bulletpoints (of Inmon):

• The long-term or short-term nature of the solution. If what you want is speed of development and good short term results, Kimball is the way to go. If you want a long-term architectural solution, then the Inmon approach is the way to go (Comment: Agree)
• If you want a tactical solution, then Kimball is the way to go. If you want a strategic solution, then Inmon is the way to go (Comment: Agree)
• The Kimball approach is very fragile. Whenever requirements undergo significant change, the Kimball solution is “brittle.” (Comment: Agree).
• In the Kimball approach, there is no single version of the truth, no system of record. In the Inmon approach, at the integrated granular level, there is a carefully crafted single version of the truth (Comment: Interresting. In the projects 've done building kimball datawarehouses we always try to build a clean ETL and what i mean by this, at first i let the garbage in the data and showed the data quality in the cubes (building a cube is easy when you have a star created already). After that i started to squeze the bad data by building error marts. So there is not really a base where the data is collected in a consolidated system. So, he is right about this).
• The Kimball approach produces results in a short amount of time. The Inmon approach produces results over a longer period of time.(Comment: Yup, Kimball is quickstarter for enterprise datawarehouses but Inmon will take the lead later in the project. on the other hand for implementing a Inmon like datawarehouse it's more likely to have more support from the higher management. With Kimball it's easier to implement the 'Think big, start small' approach)
• The Kimball architecture does not produce a foundation that can be built on over time as requirements change (Comment: agree with that)
• The Kimball architecture focuses on a narrow set of infrastructure components such as tables and indexes. The Inmon architecture is far more robust than the Kimball architecture including such things as unstructured, textual data, near line storage, archival storage and processing the tight integration of metadata into the infrastructure (Comment: Kimball approach can be built upon unstructured data, but he hasn't or doesn't want to incorporate that in his methodology. This is a less important argument)

So, what can we learn about this? So, here's my advice:

1. Choose Inmon when the information needs are not quite clear, when the information request are varied, when information requests are very ad hoc, the 'system of record' principle is important, when the datawarehouse is very strategic to a organisation or when there is time to build a consolidation layer.
2. Choose Kimball when you built a datawarehouse as a silod system, when the information requests won't change that very much, when the datawarehouse is not very strategic but more tactical (departmental) or when you want to show quick results.

Greetz!
 
Hennie