Data WareHousing & Business Intelligence

Star Schema The star schema consists of one or more fact tables referencing any number of dimension tables. The star schema is an important special case of the snowflake schema, and is more effective for handling simpler queries. Fact Tables A fact table typically has two types of columns: foreign keys to dimension tables and measures those that contain numeric facts. A fact table can contain fact's data on detail or aggregated level. In the above image Date_id, Store_id, Product_id are foreign keys. Units_Sold contains numeric values. Dimension Tables The dimension tables store the textual descriptions of the dimensions (e.g., customer name, product description).  Dimensions are usually based on the master files in the business, for example customer master, employee master. Snow Flake Schema In computing, a snowflake schema is a logical arrangement of tables in a multidimensional database such that the entity relation...

What is ETL? ETL is acronym for Etract Transform Load Companies required data to make Strategic decisions. Data from various sources in various data formats need to store in Data Ware House with required format which is used to make Business decisions. Example: I worked with Mobile Company. We have more Stores to sell the mobile connections. Some of them are having online connectivity to the database and some of them are not having online connectivity. End of the day we need to populate the data to the DataWare house from Online database and offline Data(MsAccess Tables) to create Reports for top management. For this we have two type of data sources 1) Online Transaction Database (SQL Server) 2) MS Access table In SQL Server 2000 DTS was using to Extract, Transform, Load the data. Extract - the process of reading data from a database Transform - the process of converting data from one form into another i.e changing data types and data formats Load - the process of writing data into ...

What is SCD? . In Data Ware Housing world SCD plays important role. SCD stands for Slowly Changing Dimension. It means changes in Dimension table. Dimension table would contains Customer table. Initially consider this is a master table with only one Entry for each customer. If there are any changes in the customer details. We need to make new entry for the same customer with the changed details. For this we have two options. 1) Create new entry in the same table by creating a current record status flag 2) Update the modifications in the master table and create the entry for old records in history table with Foreign key relation. Type-1 Dimension         In this it overwrites the old data with new data. No track for history data. Type-2 Dimension         The Type 2 method tracks historical data by creating multiple records for a given natural    key in the dimensional tables with separate surrogate keys and/or different ...