Reference no: EM132867668

You work for a consulting company Happy Analytics, and have recently been hired to engage on a two week assignment developing a datamart for Ford Canada. The VP of sales at Ford has expressed frustration with the accuracy of her internal reporting and questioned whether the numbers she is seeing are actually true. She has invited your company to create proof-of-concept data mart that corroborates her data while at the same time, act as a template to follow for future datamarts at Ford Canada. In your first information gathering session, you have collected notes about how the VPs and directors at Ford understand things. This is what you heard:

Trucks are selling more than cars, except for certain types of cars - namely the new Mustang Mach E. It seems customers stopped buying cars because they felt the electric variants were likely to be out soon and it didn't make sense to buy gas powered. Trucks on the other hand were not anticipated to convert to electric any time soon. Some dealers, especially in eastern regions were able to sell gas variants of cars, but branches found in the more populous cities had more success with electric variants. Each variant (gas/electric) had models that performed well in sales, and others that performed poorly. It seemed to depend on cost-per-kilometer to operate, which was a measure available for each model. Total units sold by branch seemed to surge in the spring and summer, but tended to be weaker in the fall and winter seasons, except October - that was a high selling month. Some dealers were more successful than others, with total revenue sometimes double that of other dealers within the same city. The sales team is interested in knowing what promotions have been successful in the past for each of the models in different cities. The VP of sales wants to know revenue and number of units sold per dealer, per branch, per model, per date. Regions are used to group provinces. Dealers can have multiple branches.

You are reflecting back at this word-soup you just collected, trying to make sense of things.

1. Sketch an ER Diagram to help visualize the moving pieces.

2. Declare the grain

3. Identify Dimensions

4. Identify measures

5. Sketch a star schema sufficient to address the task at hand.