Explain why referential integrity isn't a major focus of the FACT table within a data mart based on the data warehouse star schema? What issues would arise if update and delete were enforced within the key CONSTRAINTs)? This answer can be included on the PDF Visio file (PDF) of the dimensional table. 4) Sample Data From: Matt Kozi M..i@cbr.org To: You Subject: Wrapping up Can you provide a sample script for the data? Just 5-lines of data per table would suffice. That way we can test this out before we pass it along. I know, I know, not the most exciting work, but hey, look at it this way. You're almost done the project! Btw, you can just use data from the existing orders database you originally built a few months back if that's easier, else, just make some new data by hand or through generatedata.com. Doesn't matter to me. Just toss these commands to the end of the SQL file from earlier, submit it and head home, thanks. Matt Kozi Solutions Architect, CBR m..i@cbr.org


 

Transcribed Image Text:

Tasks: Referring to narrative section 6.4.1.1. "Orders Database" (Case - CBR - 6.4.1.1.- Orders Database.pdf). 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). 1) Data Mart From: Matt Kozi Mkozi@cbr.org 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 Analyst's 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 wouldn't 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, I'll need you to set up and document the new data mart for data export so that we can have some of our analyst's start to evaluate shipping performance to date. You mentioned that you needed to first document a [data warehouse] star schema layout so you'd have a reliable understanding of what data you'd need to export and after discussing with the DBA, the following information was identified for the design: Fact table-Shipping Efficiency 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. I spoke to the legal department, they notified me that we should avoid including any cost or pricing data from the CustomerOrder table, so for the Dimensional table you're setting up, skip those attributes. Also, let's change the name of the table slightly so we don't 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 don't have to go back and rework things.....Send me the PDF when you're done. Matt Kozi Solutions Architect, CBR mKozi@cbr.org 2) Table Scripts From: Matt Kozi MKozi@cbr.org To: You Subject: URGENT: Need those table scripts Now that you've gotten the model built, I need you to move onto writing out the script to build the tables for our new data mart. P.S. Also, I really hate to bother you with this again, but don't forget to set the composite key for the fact table, last time you skipped this and it became a big mess.... Additionally, while you did set the constraints last time you forgot to ensure you didn't allow updates or deletes through the connections. We need the key connections, but referential integrity obviously isn't going to work here. So just... follow instructions this time? Okay? Forward the SQL file to me so I can pass it off to our DBA. Matt Kozi Solutions Architect, CBR mKozi@cbr.org Tasks: Referring to narrative section 6.4.1.1. "Orders Database" (Case - CBR - 6.4.1.1.- Orders Database.pdf). 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). 1) Data Mart From: Matt Kozi Mkozi@cbr.org 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 Analyst's 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 wouldn't 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, I'll need you to set up and document the new data mart for data export so that we can have some of our analyst's start to evaluate shipping performance to date. You mentioned that you needed to first document a [data warehouse] star schema layout so you'd have a reliable understanding of what data you'd need to export and after discussing with the DBA, the following information was identified for the design: Fact table-Shipping Efficiency 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. I spoke to the legal department, they notified me that we should avoid including any cost or pricing data from the CustomerOrder table, so for the Dimensional table you're setting up, skip those attributes. Also, let's change the name of the table slightly so we don't 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 don't have to go back and rework things.....Send me the PDF when you're done. Matt Kozi Solutions Architect, CBR mKozi@cbr.org 2) Table Scripts From: Matt Kozi MKozi@cbr.org To: You Subject: URGENT: Need those table scripts Now that you've gotten the model built, I need you to move onto writing out the script to build the tables for our new data mart. P.S. Also, I really hate to bother you with this again, but don't forget to set the composite key for the fact table, last time you skipped this and it became a big mess.... Additionally, while you did set the constraints last time you forgot to ensure you didn't allow updates or deletes through the connections. We need the key connections, but referential integrity obviously isn't going to work here. So just... follow instructions this time? Okay? Forward the SQL file to me so I can pass it off to our DBA. Matt Kozi Solutions Architect, CBR mKozi@cbr.org