please i need help on this assignment

For this assignment, you need to use the Grocery Store Dimensional Modeling Data Mart to recommend at least 10 specific actions that the management can take to increase profitability. For each recommendation, describe the specifics of your recommendation and justify why if the management were to follow your recommendation, the profitability would increase. Basically, for each recommendation, you need to justify why you are making this recommendation. Dont just take my word for it, see the results of the analytics for yourself. Some recommendations could be one dimensional whereas others are multidimensional. A single dimensional recommendation is one that touches only one dimension and/or fact table only. For example if you recommend to change a stores layout to increase profitability that is a one dimensional analysis. Sometimes these kind of analyses are referred to dimensional browsing. Multi- dimensional recommendations, on the other hand, use two or more dimensions along with the fact table. For example, changing prices of product in a given store, that is based on store dimension and product dimension. The most interesting recommendations are multi-dimensional.

Sales fact

Store

Product

Time

Promotion

To get you started, I have included some sample ways for you to use to base your recommendations on. You can use these or develop your own. 1. Create a total profit column in sales fact table that is equal to (total sales total cost) and total profit per customer column (that is total profit/customer count). Then create summary tables or graphs that show total profit, profit per customer, total unit sales, total sales, total cost and the contribution to total profit for each store or day of the week, brand or promotion, etc. Then use these tables to find the unprofitable stores, day of the weeks, or brands etc and find out why they are unprofitable. You find that out by going to the respective dimension table. For example, for an unprofitable store, look at store table and see what can they do, would you recommend that they need to remodel or increase the size of the store, or does the location matter? For an unprofitable brand, can you recommend that you renegotiate some new pricing scheme with the vendor, or increase the selling price, etc. 2. 3. Find out which is the least profitable product or brand or store in each city or region and then find out why? Do people in LA eat less lasagna than people in Boston? What about customer count? Can you recommend a promotion in LA for example, buy one lasagna and get a dollar off on your next purchase? Find out what are the most profitable products or brands and try to sell more of them by increasing level of promotion for them. Are some promotion campaigns that costing us more than the profit they generate? If so, why continue them? Are the stores that offer financial services more profitable? If so, make all the store having them, if not could you stop offering financial services? 4. 5. 20 time_key product_keypromotion_Istore_key dollar_sales unit_sales dollar_cost customer_count 1 1 1 15 78.35 58 81.19 38 2 1 1 16 102.85 76 113.32 65 3 1 1 1 116.63 86 128.15 59 4 1 1 7.6 6 6 8.51 4 5 1 1 11 7.23 S 7.77 4 6 1 1 6 87.59 65 90.47 41 7 1 1 17 133.32 99 148.5 60 8 1 1 1 14 32.98 24 35.18 13 9 I 1 1 20 2.12 2 2.28 I 10 1 1 3 38.46 28 38.68 18 11 1 1 8 128.25 95 143.95 66 12 1 1 6 22.01 16 23.79 11 13 1 1 1 9 44.1 33 47.59 20 14 I 1 1 4 10.91 8 11.54 8 15 1 1 6 6 51.23 38 53.08 36 16 1 11 13 16.56 10 14.18 8 17 I 11 19 3.82 2 3.27 2 18 1 11 9 84.91 50 72.29 37 19 1 11 8 45.58 27 36.72 17 20 1 1 11 20 65.96 39 55.17 29 21 1 11 4 43.48 26 37.52 20 22 1 11 4 110.61 65 94.09 46 23 1 11 3 3 77.68 46 68 37 24 1 11 17 35.55 21 28.7 12 25 1 11 7 7 0.04 0 0.03 0 26 1 11 11 13.84 8 11.34 7 27 1 11 10 25.35 15 14 28 1 11 11 128.06 76 107.59 55 29 1 11 10 55.73 33 45.11 26 30 1 11 4 16.55 10 13.97 6 31 1 11 18 46.13 27 40.01 17 32 1 11 2 54.54 32 47.94 21 33 1 11 5 43.03 25 35.89 13 34 1 11 10 146.11 86 125.49 76 35 1 11 19 91.76 54 74.15 44 36 1 11 9 19.37 11 17.29 9 37 1 11 7 81.12 48 66.67 48 38 1 11 3 58.37 35 49.89 33 39 1 11 11 143.18 85 126.38 71 40 1 II 15 57.43 34 48.79 24 41 1 11 14 42 61.06 38 42 1 11 11 86,03 51 70.79 41 43 1 11 10 149.84 89 125.42 58 44 1 11 6 89.54 53 73.63 42 45 1 11 8 80.78 48 66.17 40 46 1 11 15 132.18 78 107.88 71 47 1 11 5 11.13 7 8.97 6 48 1 1 11 8 20.2 12 16.4 7 49 1 11 1 90.08 53 77.12 32 50 1 1 11 10 127.14 75 106.78 72 51 1 11 15 107.24 63 93.44 32 52 I 11 9 166.53 99 146.59 84 53 1 11 9 46.84 28 39.14 20 22.06 71.47 Drop Off store_key name 1 Store No. 1 2 Store No. 2 3 Store No. 3 4 Store No. 4 5 Store No. 5 6 Store No. 6 7 Store No. 7 8 Store No. 8 9 Store No. 9 10 Store No. 10 11 Store No. 11 12 Store No. 12 13 Store No. 13 14 Store No. 14 15 Store No. 15 16 Store No. 16 17 Store No. 17 18 Store No. 18 19 Store No. 19 20 Store No. 20 store_numbstore_street_addrecity store_county 1 999 Main Street New York New York 2 73 Main Street Chicago Cook 3 1 Main Street Atlanta Fulton 4 575 Main Street Los Angeles Los Angeles 5 123 Main Street San Francis San Francisco 6 353 Main Street Philadelphia Philadelphia 7 839 Main Street Pittsburgh Allegheny 8 651 Main Street New OrleanOrleans 9 912 Main Street Seattle King 10 752 Main Street Dallas Dallas 11 55 Main Street Cincinnati Hamilton 12 546 Main Street Minneapolis Hennepin 13 578 Main Street Louisville Jefferson 14 968 Main Street Phoenix Maricopa 15 228 Main Street Denver Denver 16 333 Main Street St. Louis St. Louis 17 764 Main Street Washington DC 18 253 Main Street Miami Dade 19 999 Main Street Boston Suffolk 20 160 Main Street Nashville Davidson store_state store_zip sales_districsales_regiorstore_manastore_phonestore_FAX floor_plan_jphoto_procefinance_ser firs NY 91089 New York Eastern Jones (164) 187-7(774) 240-5 Modern None None IL 14594 Cook Mid West Smith (952) 347-1-(410) 820-1.Original Full ATM GA 54315 Fulton South East Davis (249) 418-8 (589) 502-2.Compact ATM CA 52944 Los Angeles Pacific Johnson (676) 380-5 (707) 829-2 Modern Kiosk ATM CA 86969 San Francis Pacific Green (791) 157-7 (796) 504-6 Original None ATM PA 51458 Philadelphia Eastern Brown (391) 693-2 (188) 354-4Compact Full ATM PA 91949 Allegheny Eastern White (845) 759-9 (814) 291-1. Modern Drop Off ATM LA 61163 Orleans South West Williams (464) 847-3.(958) 941-5'Original Kiosk ATM WA 5764 King Pacific Stuber (189) 237-3.(726) 637-6.Compact None ATM TX 34876 Dallas South West Merz (609) 767-6(386) 463-1 Modern Full ATM OH 44999 Hamilton Mid West Erickson (793) 462-4 (674) 600-5'Original Drop Off ATM MN 57554 Henn pin Mid West Kalman (689) 796-3.(522) 334-2.Compact Kiosk None KY 60041 Jefferson Mid West Inmon (401) 623-9 (192) 932-4 Modern None ATM AZ 86844 Maricopa Mountain Strehlo (302) 466-2 (720) 101-2 Original Full ATM CO 21488 Denver Mountain Ollom (818) 481-2 (956) 162-2.Compact Drop Off Platform MO 64373 St. Louis Mid West Mantle (157) 699-8 (818) 840-3 Modern Kiosk Platform DC 36161 DC Mid Atlanti Mays (443) 710-1-(890) 459-8. Original None Platform FL 67141 Dade South East Maris (957) 831-1 (640) 578-9 Compact Full ATM MA 19552 Suffolk Eastern Ruth (478) 856-5 (688) 547-7 Modern ATM TN 44859 Davidson Mid West Cobb (454) 131-3 (560) 502-3. Original Kiosk ATM Drop Off A B D E F G I . | J K L M M N O P Q R S 1 1 2 6 OZ 3 6 OZ 4 5 6 7 can 8 9 6 OZ 5 10 11 product_\ descriptio full_descr SKU_num package_s brand subcatego category departme package_t diet_type weight weight_ur units_per units_per cases_per shelf_wid shelf_heig shelf_depth_cm 1 Lasagna Lasagna 6 90706287 6 oz Cold Gour Frozen For Food Grocery box Diet 48 192 8 5 3 5 2 Beef Stew Beef Stew 16005393 6 oz Cold Gour Frozen Foi Food Grocery box Regular 48 192 8 3 5 3 Turkey Dir Turkey Dir 57986858 8 oz Frozen Bir Frozen For Food Grocery box Diet 8 oz 96 384 8 7 3 5 4 Chicken D Chicken D 67955177 8 oz Frozen Bir Frozen Foi Food Grocery box Regular 8 oz 96 384 8 7 3 5 5 Extra Nou Extra Nou 46817560 2 oz Chewy Inc Candy Food Grocery can Regular 2 oz 48 192 12 3 6 3 6 Lots of Nu Lots of Nu 84276830 2 oz Chewy Inc Candy Food Grocery Regular 2 oz 48 192 12 3 6 3 7 Sweet Toc Sweet Toc 51770124 2 oz Chewy Inc Candy Food Grocery bottle Regular 2 oz 72 288 12 3 8 3 8 Fizzy Light Fizzy Light 33411763 6 oz Big Can Soft Drink Drinks Grocery can Diet 48 192 8 5 6 5 9 Fizzy Clas: Fizzy Clas: 95946398 6 oz Big Can Soft Drink Drinks Grocery can Regular 6 oz 48 192 8 5 6 5 10 Athletic D Athletic D 88602993 6 oz Big Can Soft Drink Drinks Grocery can Diet 6 oz 48 192 8 5 6 5 11 Strong Co Strong Co 59015963 8 oz National E Soft Drink Drinks Grocery can Regular 8 oz 48 192 8 5 6 5 12 Clear Refr Clear Refr 94794170 6 OZ National E Soft Drink Drinks Grocery can Diet 6 oz 48 192 8 6 5 13 Paper Ton Paper Tow 22563048 8 oz Squeezabl Cleaning Supplies Househol plastic ba NA 8 OZ 16 64 16 9 13 5 14 Dry Tissue Dry Tissue 20716427 5 oz Squeezabl Cleaning Supplies Househol box NA 48 192 16 4 2 3 15 Wet Wipe Wet Wipe 65053589 8 oz Squeezabl Cleaning S Supplies Househole plastic bo: NA 8 oz 48 192 16 8 5 4 16 Salty Corr Salty Corr 80323441 2 oz American Salty Snac Food Grocery bag Regular 2 oz 24 96 12 12 5 4 17 Dried Grit Dried Grit 15536655 2 oz American Salty Snac Food Grocery bag Diet 2 oz 24 96 12 12 5 4 18 Power Chi Power Ch 44513822 3 Oz American Salty Snac Food Grocery bag Regular 3 oz 24 96 12 12 5 4 19 Onion Slic Onion Slic 49269095 3 Oz Western Salty Snac Food Grocery bag Regular 24 96 12 6 6 4 20 Buffalo Je Buffalo Je 40806606 3 oz Western V Salty Snac Food Grocery bag Regular 24 96 12 5 3 2 12 13 14 S 15 5 OZ 16 17 18 19 20 3 OZ 21 3 Oz time_key date 1 2 3 4 5 5 6 7 8 9 0 10 11 12 13 14 15 16 17 18 day of weeday_numberday_number_ovweek_number_ilweek_numbyear 9/26/12 Saturday 1 273 39 39 9/27/12 Sunday 2 274 40 40 9/28/12 Monday 3 275 40 40 9/29:12 Tuesday 4 276 40 40 9/30/12 Wednesday 5 277 40 40 10/1/12 Thursday 6 278 40 40 10/2/12 Friday 7 7 279 40 40 10/3/12 Saturday 8 8 280) 40 40 10/4/12 Sunday 9 281 41 41 10/5/12 Monday 10 282 41 41 10/6/'12 Tuesday 11 283 41 41 10/7/12 Wednesday 12 284 41 41 14 10/8/12 Thursday 13 285 41 41 10/9/12 Friday 14 286 41 41 10/10/12 Saturday 15 287 41 41 10/11/12 Sunday 16 288 42 42 10/12/12 Monday 17 289 42 42 10/13/12 Tuesday 18 290 42 42 10/14/12 Wednesday 19 10 291 42 42 10/15/12 Thursday 20 292 42 42 10:16:12 Friday 21 293 42 42 10/17/12 Saturday 22 294 42 22 44 42 10/18/12 Sunday 23 295 43 43 10/19/12 Monday 24 296 43 43 w wody 10/20/12 Tuesday 25 297 43 43 10/21/12 Wednesday 26 298 43 43 10/22/12 Thursday 27 299 43 43 10/23/12 Friday 28 300 43 43 10/24/12 Saturday 29 301 43 43 10/25/12 Sunday 30 302 44 44 10/26/12 Monday 31 303 44 44 10/27/12 Tuesday 1 304 44 44 10/28/12 Wednesday 2 305 44 44 10/29/12 Thursday 3 306 44 44 10:30:12 Friday 4 4 307 204 44 44 10/31/12 Saturday S 308 44 44 11/1/12 Sunday 6 309 45 45 11/2/12 Monday 7 310 45 45 11/3/12 Tuesday 8 8 311 45 45 11/4/12 Wednesday 9 312 45 45 11/5/12 Thursday 10 313 45 45 11/6/12 Friday 11 314 45 45 11/7/12 Saturday 12 315 45 45 11/8/12 Sunday 13 316 46 46 11/9/12 Monday 14 317 46 46 11/10/12 Tuesday 15 318 46 46 11/11/12 Wednesday 16 ac 319 46 46 11/12/12 Thursday 17 320 46 46 11/13/12 Friday 18 321 46 46 11/14/12 Saturday 19 324 322 46 46 11/15/12 Sunday 20 323 47 47 Sunday 11/16/12 Monday 21 324 47 47 11/17/12 Tuesday 22 325 47 47 11/18/12 Wednesday 23 326 47 47 11/19/12 Thursday 24 327 47 47 11/20/12 Friday 25 328 47 47 11/21/12 Saturday 26 329 47 47 11/22/12 Sunday 27 330 48 48 11/23/12 Monday 28 331 48 48 11/24/12 Tuesday 29 332 48 48 11/25/12 Wednesday 30 333 48 48 11/26/12 Thursday 1 1 334 48 48 11/27/12 Friday 2 335 48 48 11/28/12 Saturday 3 336 48 48 11/29/12 Sunday 4 337 49 49 11/30/12 Monday 5 49 49 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 S3 54 55 S6 57 58 59 60 61 62 63 64 65 66 holiday tla 2012 N 2012 N N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 Y 2012 N N AVIAN 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 Y 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 Y 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N 2012 N N 2012 N 2012 N 2012 N 338 promotion_]promotion_price_reduciad_type display_typ coupon_typ ad_media_t display_pro promo_cost promo_begin_datepromo_end_date 1 Blue Ribbor Temporary Daily Paper None None Paper None 2000 10/1/12 10/15/12 2 Red Carpet Markdown Sunday Pap None None Paper None 1000 10/1/12 10/15/12 3 Ad Blitz None Paper and RNone None Paper and RNone 7000 11/10/13 11/17/13 4 Ads and Ra None Paper and R Rack None Paper and R Smith 3000 11/11/13 11/18/13 5 Shelf Talker None None Shelf Tag None None Jones 500 10/1/13 12/31/13 6 POS GrabbeNone None Register None None Smith 600 10/1/13 12/31/13 7 Two for On Coupon On Sunday Pap None Paper Paper None 1000 11/15/12 11/30/12 8 Cents Off Coupon OnlMailer None Mailer None None 1000 11/15/13 11/30/13 9 Redemption Redemption Attached None Attached None None 5000 12/1/13 12/31/13 10 Big Promo Temporary Daily Paper End Aisle None Paper Smith 8000 12/15/13 12/31/13 11 No Promotii None None None None None None 0 10/1/94 12/31/14