Hello, Can anyone please share their or known project on creating a Data Warehou

Question

Hello,

Can anyone please share their or known project on creating a Data Warehouse with some example like University or retail store or some insurance company or hospital Data Warehouse.

The requirement of the project: Creating OLAP Queries(i want actual queries), Bus Matrix and the dimensional model consisting of fact table and dimensions. please don't just give the basic explanation about each requirement I need the actual project creating a data warehouse with all those requirements.

Thanks in Advance.

Urgency: ASAP, Please!

Explanation / Answer

Answers :

Kindly find below an example schema representing data warehousing on retail store , I hope this will be helpfull as an reference.

OLAP queries : (Representing the details to be included)

Order Management Schema order_master order_no customer_id item_loc item_price item_id promo_id sales_hist_id
item_attribute warehouse_id shipment_id order_detail order_no item_id date_creation shipment_id order_status
item_type order_qty Customer customer_id customer_name customer_city customer_cty customer_type
shipment shipment_id item_id order_no item_qty to_loc from_loc warehouse (wh) wh_id wh_name wh_city wh_country wh_type
item_price item_id item_type item_store item_promos item_id item_type promo_type discount_amount promo_id item_sale_hist
item_id sale_id item_store item_wh order_no date_sale promo_id shipment_id sale_qtyitem_attrib item_id item_type item_color
item_size item_stype item_dept item_brand item_loc_inv wh_id loc_type loc_id stock_on_hand

item_sales_master supplier_id(FK) order_no(FK) store_id(FK) promo_id(FK) item_id invoice_id(PK) wh_id date(FK)
item_units supplier supplier_id(PK) supplier_country supplier_city supplier_name date_joined sup_loc_id supplier_type order_detail order_no(PK)
item_id date_creation shipment_id item_type order_qty store store_id(PK) store_name store_city store_cty store_type item_promos promo_id (PK)
item_type promo_type discount_amount item item_id(PK) item_color item_size item_dept item_brand navigation bridge item_id
invoice_id number_of_levels bottom_flag item_inventory loc_type loc_id (wh) stock_on_hand item_id(PK) item_price item_id(PK)
item_type item_price warehouse wh_id(PK) wh_name wh_city wh_country wh_type sale_date date(PK) sale_year sale_month sale_day Sales Transaction Schema

Item_sales_master Supplier Order_ detail Item_ inventor y Item_ price Ware house Store Item_ promos Sale_ date Item Logical Model : Sales Schema

Warehouse Dimensioning:

Confirmed Dimensions warehouse (wh) wh_id wh_name wh_city wh_country wh_type store store_id(PK)
store_name store_city store_cty store_type supplier supplier_id(PK) supplier_country supplier_city supplier_name date_joined sup_loc_id supplier_type item item_id(PK)
item_color item_size item_dept item_brand order_detail order_no item_id date_creation shipment_id order_status item_type order_qty Item_sale_master Order_master

E-commerce Dimension Table Detailed Diagram :

Product Dimension Gender Categories Styles Color Description Product Price Band Price (3) (13) (89,243) (26) (89,243) (11,237,312 est.) (18) (5) Size (38)
Slowly Changing Dimensions: Categories Gender Size Color Price Band Collection Review comment Review rate (9)

Bus Matrix:

BUS MATRIX Date and time Supplier Item Order Store Customer Warehouse Promos Procurement x x x x Warehouse management x x x x x
Shipping to stores x x x x Marketing x x x x x Sales x x x x x x Inventory Management x x x x Delivery x x x x x Customer Services x x x

Business Process Procurement Shipping Marketing Finance Customer Service Online sales transaction X X X X X In store Sales Transaction X X X X
Supplier sales Transaction X X X Order processing X X X Delivery process X X Return Policy X X X

HIGH LEVEL BUS MATRIX Date and Time Supplier Item Order Store Customer Warehouse Promos Sales Transaction
X X X X X X X Order Processing X X X X X X X Delivery Process X X X X X X Return Policy X X X X X X X

Fact Tables Granularity Fact Date & Time Supplier Item Order Store Customer Warehouse Promos Sales Master Per Transaction Items Sold Amount Earned
x x x x x x x Order Details Per Item x x x x x x x Inventory Management Per Order x x x x x x Billing Per Transaction x x x x x x Shipping Per Order
x x x x x x Store return policy Per Item x x x x x x X

Business Process Sales Fact table Item_sales_master Grain region_month_itemtype Facts Itemunits_totalsales Sale_date Sale_month Warehouse Region Store Region

Busines s Process Sales Transaction Order Processi ng Delivery Process Return Policy Fact Tables Granularity Fact Date &
Time Supplier Item Order Store Customer Warehouse Promos Sales Master Per Transaction Items Sold Amount Earned x x x x x x x
Order Details Per Item x x x x x x x Inventory Management Per Order x x x x x x Billing Per Transaction x x x x x x
Shipping Per Order x x x x x x Store return policy Per Item x x x x x x X

AGGREGATE TABLE:

Business Process Sales Fact table Item_sales_master Grain region_month_itemtype Facts Itemunits_totalsales Sale_date Sale_month Warehouse Region Store Region

TRANSFORMATION RULES:

Data Type Conversion The source data type is generalized and changed into the destination data type.
Constant It will add a predefined value to the destination field. Missing Values Missing fields will be filled with an appropriate value.
Duplicate Rows This transformation rule will identify and delete duplicate rows. Look-Up Incorrect values and unknown values can be looked up from the table.

E-commerce User & Task Analysis :

Operational – Lowest level entry point with limited data on a specific business process – Informative explanatory visualization
External Customer – Lower level entry point with both summary and drill down capability – Hybrid persuasive exploratory – informative explanatory visualization
Executive/Management – High level entry point with summary data and drill down capability – Primarily informative explanatory visualization
Business Analyst – High level entry point with summary data, drill down and drill across capabilities – Hybrid exploratory – informative explanatory visualization

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.