Creating and maintaining Item Category in datawarehouse

I have one datasource which has Item category. This category needs to be implemented in datawarehouse. This Item catgory is implemented in multiple tables like ItemClassification, Item SubClassification, ItemType etc. Let us name data source where Item category is present as "DS I", this will ease our next conversation. If I wish to implement the same Item category for datawarehouse I face following challanges,

1) "DS I" holds 70% items, what about 30% items which comes from other data sources in data warehouse.

2) Do I have to create all items in "DS I", so that I will create ETL to extract Item category from "DS I"?

3) what is alternative for the above mentioned option 2)

Data has to come from somewhere. The preferred source is from those who determine what the product category should be (i.e. the business).

You could add the other 30% of the products to system 1 as you suggest, or you could modify the other system to maintain category. Or, you can move everyone to a single system. Or, you can build a small application that allows the business to maintain category and other attributes of products across sources (not necessarily a good idea unless there is some high level pressure to ensure users actually use it and keep it up to date). Or, you can do it in code (even a worse idea as it gets real messy and time consuming).

The discrepancy in data elements between systems is not uncommon. Look at the data warehouse as an enabler of change as it often highlights such data issues. The final solution all depends on the business's perception of the problem and the value a solution would bring.

ngalemmo ,
I am newbie in designing datawarehouse processes. I appreciate your time and reply.

Platform: SQL SERVER 2008 R2, SSIS

I didn't get the following two options mentioned by you
1) "you can move everyone to a single system"
2) "you can build a small application that allows the business to maintain category and other attributes of products across sources"

The requirement definition is given below,
1) SYSTEM I holds 70% of items required for datawarehouse.
2) Other 30% will come from other systems.
3) SYSTEM I Item Category will be used in data warehouse.

My Brain Storming,
Option I:Creating 30% items in SYSTEM I
What if new Item is added in other system, I have to assign some random value (may be "Not Available" value) to attributes for Item Category in the Item Dimension, then I have to notify Business person to add new Item in SYSTEM I, subsequently I can run SSIS package to update dimension tables for Item Category (In order to remove "NOT AVAILABLE" values for attributes in Item Dimension)

Option II: (Do not create 30% items in SYSTEM I)
From Business I can get Item Category for 30% items (which are present in other systems), Once I assign that for every new Item created in other systems I will assign intermmediate value (may be "Not Available" value), I can notify Business user of new item, will get Item category from Business user and subsequently assign it through SQL code. (this way is manual and may be not preferable)

How can I sketch the best process out of above scenario?

Ishwar,

I think what ngallemmo was trying to highlight is that this isn't really a data warehouse issue, but more a source system/master data issue, for which there are many possible solutions. (merging systems, add the item category to both systems etc).

As ngallemmo suggests, use the data warehouse as a mechanism to highlight the missing categories to the business. You can then provide a mechanism for the business to provide the missing categories .... this could be accomplished using a small app/web page, or something as simple as a spreadsheet (that is loaded via a SSIS package). You are correct - one time SQL updates are not ideal.

Yep, what LA said...

I hate option 2 by the way... too many 'I's in the solution. You need to provide a way for the business to handle it, without IT intervention. Everyone will be happier in the long run.

Thank you "LAndrews" and "ngalemmo" for replies...

Thanks too friends for your helpful replies.

I want to thank you to all because you are sharing helpful information with us.

I've used SSRS as a poor man's data maintenance app. Assuming you build a little database somewhere to store item categories for items that are sourced from DS2, you could give users two SSRS reports:

The first would list what items don't currently have a category assigned. (This could be scheduled to run periodicly.)

The second report would actually allow them to assign/maintain the category by using report parameters as the input fields. You would build a report that has parameters for the item identifier (business key) and the category (might be a drop-down parameter.) The report would call a stored procedure as its data source which would then store/update the category for that item (and return some sort of result set to be displayed in the report that would confirm the update has taken place.)

Takes all the "I"s out of the solution and allows users to do their own data maintenance, and can be thrown together in a couple hours using the tools at hand (assuming you have SSRS deployed in your environment.)

Having a fixed-format spreadsheet that you import using SSIS is another good option.