Enterprise Resource Report
10 Pages2253 Words260 Views
Added on 2019-10-16
Enterprise Resource Report
Added on 2019-10-16
ShareRelated Documents
Enterprise Resource ReportIntroduction:Data Ware House (DWH):1.It is a database contains historical information2.According to Inmon: A DWH is a subject-oriented, integrated, time varying means changes with time, non-volatile means cannot be deleted, collection of data in support of the management's decision-making process.3.Data mart: It is a single business unit for different departments & organized for quick analysis.It is aHigh Performance Query Structure (HPQS), it is subset of dwh supports business needs of department in the organization.4.Staging Data: Initial data store from multiple data sources (or) databases.5.Multidimensional Analysis :Scrubbing of data (or) Modifying the data (or) New data derived from existing data sothat data can be viewed at different level of details. 6.Top Down Approach ( Bill Inmon):In this approach, first data is loaded into repository - DWH & then from repository - data is loaded into data marts(single business unit).It performs 1 or 2 years in this small clients cannot wait for this time. Hence big clients uses this top down approach.7.Bottom Up Approach ( Ralf Kimbal):In this approach, first data is loaded into data mart which are independent & then data is merged into centralized database.In this data marts are independent data marts.Most of the companies are using Bottom- Up approach.1
Enterprise Resource ReportOLTP (ONLINE TRANSACTIONAL PROCESSING)It records daily operations & transactions of a businessOLTP System deals with operational data. Operational data are those data involved in the operation of a particular system.In an OLTP system data are frequently updated and queried. So quick response to a request is highly expected. Since the OLTP systems involve large number of update queries, the database tables are optimized for write operations.Except DWH Tables other database tables are OLTP’s.Part – AAdvantages of Excel Reporting:Analyze data and interpret data at granular level with embedded functions easily.Any data which is unclear, we can cleanse the data and make it easy-to-read, and create avery good professional-looking charts.Perform different analysis by pulling the required columns based on the requirement andsimilarly we have can create the PivotTables.Create user-friendly templates and forms to make information gathering a simple process.Collaborate with other Excel users to efficiently work on group projects such as buildingworksheets.Overall Dashboard – looks like below1.Create production data tables a.Creation of orders table2
Enterprise Resource Reportcreate table orders_table ( Order_ID int, Order_Date Date, Customer_Name varchar, Customer_Segment Short Text, City Short Text, State_or_Province Short Text,Region Short Text, Product_Category Short Text, Product_Sub-Category Short Text,Product_Name Short Text, Sales Int, Discount Int, Profit Int, Unit_Price Int)b.In the above table we are creating the table with column names and declare the data type for those columns with table name.c.It includes Order_ID, which will be unique for every order, Order_Date will be same for the customers who have ordered the products in different category.d.Creation of order_returns table;Create table Order_returns_table (OrderID int, flag varchar)In the above table is created to old the OrderID and flag which show the status of the Order.2. Decide on the relevant dimensions required for the data warehouse, and then create tables to store the dimensions of a data warehouse.create table orders_dimension (OrderID int, OrderDate Date, CustomerCategory Short Text,Region Short Text, ProductCategory Short Text)In this case, this I am selecting the specific columns which are treated as dimensions and pulled the column which act as primary key to make a join for any other table like return. And the tablename is orders_dimension table act as dimension.In this table OrderID, OrderDate CustomerCategory, region, ProductCategory are pulled as dimensions.3. Decide what facts are required and then create tables to store these facts create table orders_fact_sales (OrderID int, Sales Int, Discount Int, Profit Int, UnitPrice Int)In this case, a table is created for the purpose of measures and the table name is orders_fact_sales and the columns are OrderID which is used to make a join if required with theother tabs like dimension table and also pulled the other columns like sales, discount, profit and Unit_Price as different measures.4.Create a data warehouse via a query that joins the dimension and fact tables.create table orders_dim_fact_vw(OrderID int, OrderDate Date, CustomerCategory Short Text,Region Short Text, ProductCategory Short Text, , Sales Int, Discount Int, Profit Int, Unit_Price Int) as(select a.Order_ID, a.Order_Date, a.Customer_Segment, a.Region, a.Product_Category, a.statusfrom orders_dimension a left outer join orders_fact_sales b on a.OrderID = b.OrderID3
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
Structured Query Language - PDFlg...
|9
|1603
|187
Data Warehouses vs Data Martslg...
|4
|608
|272
Design and Implementation of a Data Warehouse for GreenHomeHelplg...
|11
|1392
|73
Management Information System - Assignment Solvedlg...
|12
|4190
|62