NDS to DDS Design Questions

Hello Everyone,

I am currently working on a Data Warehousing project and I am stuck on how to transform my data from my NDS to my DDS (mainly how to structure my DDS to hold the data from my NDS). The basic gist of the project is that I have three sets of data, one coming from a phone switch (Aspect), one coming from another phone switch (Avaya) and one coming from our Email Response Management (ERM) system (KANA). What I did in my NDS is made generic tables for the data (ex: Call, CallTransaction, Email, and EmailTransaction). The Call table holds a header of the call (who called, the number it came in on, etc). The CallTransation table holds the details of the call (one record would be the call coming into the queue, one is the agent talking to the customer, one is the transfer to another agent, etc). I did the same thing for the email tables.

Here are the three problems that I cannot seem to overcome:

1. Would my Call and Email tables be Dimension tables or would I keep them as Fact tables and do a join between fact tables (which I though is frowned upon in warehousing).
   
2. In my NDS I keep configuration values such as Queue1 and Queue2 roll into Program1 under Department1. These configuration tables are stored in SCD2 format (Effective and Expiration DateTime’s). For example, in Aspect we have Queue1ID = 50 and Queue2ID = 100. In my configuration I store that Queue1ID 50 belongs to Program1 from DateA to DateB, but from Program2 from DateB to DateC. This allows me to see the configuration at any point in time. In my NDS this works fine because I do a join to the Aspect QueueID based on Effective/Expiration DateTime’s to get the data that I want. I would like to just have a SurogateKey that I use, but unfortunalty due to business requirements, the configuration can happen after the data from the source system has been loaded into the NDS. The NDS configuration can change at any time during the month. How then would I transfer this into a DDS? I don’t think that updating the DDS when configuration changes is a good idea, and I think that doing joins based on application keys rather than SurogateKeys is not a good idea either.
   
3. One of the KPI’s I have to track is Number of Emails Inbound by day. In reality this seems easy, but I’m not sure how you would do this in a Data Warehouse. It was my understanding that you never want to update/delete DDS data, therefor I wouldn’t want to load anything into the DDS that is not complete. An email can come in today but not be completed for two days. This would mean that for the length of that email (and all emails like it) reporting would be wrong out of the DDS as it wouldn’t be loaded into the DDS until the date of completion rather than creation.

Any help would be very appreciated!


Thanks,

Christopher Haws

Call and email tables are fact tables. You join these by drilling across on a conformed dimension. What does NDS and DDS stand for? You can update data in the data warehouse. Type 1 dimensions are updated every time they change. Accumulating snapshot tables are updated as well. It all depends on the business process.

BoxesAndLines wrote:What does NDS and DDS stand for?

I think NDS is Normalised Data Store and DDS is Dimensional Data Store. As far as I can remember, NDS is in 3NF containing all the historical master data by using surrogate keys as opposed to ODS with only current version of master data related by natural keys.

I guess you could have accumulating snapshot fact tables in DDS for the measurements on calls and emails. In an essence, you have a number of milestone dates and time lags in the fact tables that are revisited and updated accordingly by ETL.