Description:
Lord & Taylor Retail Food Stores, which is one of the organization that
sells various numbers of products. Lord & Taylor have various stores
across USA, which maintains Sales, Profits with lots of historical data. This
project was to create Sales Data Warehouse and generate reports using
Reporting services. The aim of sales data Warehouse is to analyze the sales
and profit in month wise and year wise. The data gets refreshed yearly and
is maintained historically. This Data warehouse plays a major role in
enabling various stores to view the data at a lowest level and help them to
make decision to bring more revenue to company with new policies.
The data in the DWH is processed into OLAP data marts for reporting and
analysis purposes.
Informatica used as an ETL tool for loading data into the data warehouse
from different data sources: Data integrated from various data sources such
as OLTP databases and flat files.
Data can be captured by Store, Zone, accounting year, quarter and brand.
Tables:
1) Sales_Categ_Fact: Keeps daily sales of all categories in all stores. Into this
table data can be sliced by brand, by category, by calendar year, month, week
and by pricing tier.
2) Sales_Operations_FACT: This table keeps daily sales summary for each
store. Data can be sliced and captured by calendar year, month and week and by
pricing tier. (Storeid,Gross sale, taxable sales, non-tax sales, Trans_dt
3) Product_dim: Contains all the categories like Garments, Furniture, Grocery,
Wine etc.,
4) Orderdetails_dim
5) Customer_dim
6) Store_dim
7) Sales_by_product_FACT
Functionalities:
1) Load daily sales of all categories of products for all stores into
dwh
2) Load customer and store details into target db
3) Loading the Gross_sales, taxable sales, Non-tax sales data into
dwh for store wise.
