Develop a multi-dimensional model to solve a business problem by creatIng a star schema.
Develop a multi-dimensional model to solve a business problem by creatIng a star schema. There are two samples for this assignment that can help you. You should complete this assignment based on the examples.
Your Specific Assignment is as Follows:
- Identify a business process or event, such as Retail Sales or Stock Trades, which you will develop in the assignment. You need not be confined to traditional areas and events; for example, one student is a baseball fan and has selected baseball as his subject data area. If you select something such as baseball, make sure you carefully identify the events to capture in your star schema. For example, I think Player Statistics is a good candidate for a fact table with facts such as homeruns, hits, and batting average (or, all of these metrics, depending on the grain you select).
- Identify where and how you will access the data necessary to build your star schema. Describe where in your company or on the Internet this data exists and how you plan to access it. Include the departments and systems you plan to access as well as web sites and social media sites.
- Design your dimensional model, identifying fact(s), fact table(s), and dimension tables as well as selected attributes and keys. Provide an annotation as you progress in your design. Highlight important design considerations and techniques in your discussion (e.g., explaining the grain you selected). This item is the central part of the assignment.
I will be looking for the following traits in item #3 above:
- Properly design dimension tables, including embedded hierarchies and single attribute, surrogate primary keys. Conformed dimensions may also be included.
- Properly declare a grain and design a fact table, including numerical and textual facts and a composite primary key of surrogate, foreign keys.
- You are able to apply advanced design principles to, for example, create a design that will manage rapidly changing, very large dimensions and other situations. Outrigger and bridge tables should also be considered.
In general, I will be looking to see how well you apply and fully exploit basic and advanced design principles to produce a robust design that can withstand change (e.g., how well your design manages rapidly changing, very large dimensions, etc.).
Since this is an assessment of understanding, try to make your answers as concise as possible. For example, your answer to items 1 and 2 above can be answered with a few sentences and bullet points. On the other hand, item 3 is the key and requires a good diagram with annotation that should accompany your design of the star schema.