The client organization wishes to better understand shipping performance based on the observable variance in shipping estimation and the actual time of arrival. To perform analysis on this data, they require a data warehouse (data mart) that will help identify critical data regarding shipping data as gathered from the Orders Database Orders Schema. 1) The Data Mart From: Matt Kozi To: You Subject: New Data Mart Hello. I thought it would be helpful to send you a summary of what we discussed during the last meeting. The higher-ups want to kick-off a new initiative that will have our Analytics team evaluate shipping performance so that we can better understand which shipping methods are falling short on delivery time expectations and to what regions/cities this is most commonly occurring. However, I do agree with what you stated during the meeting, that giving the Analysts direct access to the Orders database was an unnecessary risk, and that providing them with a cleansed data mart would be the best choice. We had some additional meetings and everyone agrees that your data mart idea would ensure that we wouldnt need to add more users to the Orders database, and would allow us to retain full control over what data we want the Analysts to access. As it stands, Ill need you to set up and document the new data mart for data export so that we can have some of our analysts start to evaluate shipping performance to date. You mentioned that you needed to first document a [data warehouse] star schema layout so youd have a reliable understanding of what data youd need to export and after discussing with the DBA, the following information was identified for the design: Fact table ShippingEfficiency Table which will include all relevant connections from the associated dimensional tables along with a new fact table measure which will calculate the difference in date between estdateshipped and actualdateshipped Dimensional table CustomerOrder, ShipmentMethod, OrderStatus, ShippingAddress. o Also, I spoke to the legal department, they notified me that we should avoid including any cost or pricing data from the CustomerOrder tableso for the Dimensional table youre setting up, skip those attributes and change the name of the table slightly so we dont get it confused with the regular CustomerOrder table in the future. Can you build it in VISIO or something? I know it is a really basic diagram, just want it to look professional so we dont have to go back and rework thingsSend me the PDF when youre done. Matt Kozi Solutions Architect, CBR mKozi@cbr.org
