Data Warehouse - Basics Fundamental Notes - Part1


Data warehouse which is build on RDMS is used for analyze the data and based on the nature of data and the analysis, people take the decisions. The objective is to bring intelligence analysis which lead to make the predictive analysis call right.

Following are few fundamental notes on data warehouse.

  • Now a days, the Data Lake is being used for Big Data analysis purpose and not limited only in RDMS stored area.

  • In particular, there are three key factors are exist in data warehouse world, and they are three V's - Volume, Velocity, and Variety.

  • In data warehouse, not only structured data maintained or analysis, the data warehouse system should able to handle unstructured data like video, audio, images as well. 

  • Centralize data warehouse using Single RDMS. 

  • Keep a note that data warehouse is NOT as Data mart. Dependent DataMart variation with Corporate Information Factory(CIF).

  • There are four rule of data warehouse
    • Integrated 
    • Subject Oriented
    • Time Variant
    • Non Volatile

  • ETL(Extract, Transform and Load) layer's objective is to  concentrate on 'E'(Extract) with minimal 'T'(Transform)

  • There are two types of staging layer(Extract operation) exist
    • Non-Persistent Staging Layer - Data In and Data Out
      • Advantages - 1) Less diskspace space; 2) Data move to next layer and keep the space empty
      • Disadvantages - 1) For rework(like data corruption or any major faulty) - data needs to bring again in staging layer from source system; 2) QA needs access to source system to validate staging layer data

    • Persistent Staging Layer - Data In and Data Stay
      • Advantages - 1) Can rebuild user layer from staging layer at any time and do not need to bring data from source system; 2) For data quality insurance, quickly validate from staging with user access layer and no need to visit source system
      • Disadvantages - 1) More disk space is required; 2) Need implementation of data governance like access for viewing old records


  • Types of ETL - 1) Initial ETL, 2) Incremental ETL

  • Incremental(refresh data) ETL pattern - a) Append, b) In-place update, c) Complete replacement, d)Rolling append

  • Data Transformation goals
    • Uniformity
    • Restructuring
  • Common Transformation Models
    • Data value unification
    • Data type and size unification
    • De-duplication
    • Dropping columns (vertical slicing)
    • Value based row filtering (horizontal slicing)
    • Correcting known errors


Comments

  1. Nanfor IbéricaOctober 24, 2022 at 11:00 AM

    This blog is very helpful and informative for this particular topic. I appreciate your effort that has been taken to write this blog for us. Microsoft Certified Power Platform Fundamentals

    ReplyDelete

Post a Comment

Popular posts from this blog

How to fix Azure DevOps error MSB4126

Image
Setting up build and release pipeline always tedious and we face many build related issues. Here is one common error I face couple of times while setting app Web or windows apps in Azure DevOps After placing the code in repos, and creating the build pipeline and run it, you can face the following error ##[error]D:\a\8\s\<your solution file>.sln.metaproj(0,0): Error MSB4126: The specified solution configuration "release|$(BuildPlatform)" is invalid. Please specify a valid solution configuration using the Configuration and Platform properties (e.g. MSBuild.exe Solution.sln /p:Configuration=Debug /p:Platform="Any CPU") or leave those properties blank to use the default solution configuration. To understand what is target platform defined in your project you need to visit or open the solution(.sln) file and review the following sections. In my case, this has been defined as x86 as target platform.         ...
Read more

How to create Custom Visuals in Power BI – Initial few Steps

Image
This post will help you to know the steps to create custom visuals in PowerBI. Microsoft defined them as well at https://docs.microsoft.com/en-us/power-bi/service-custom-visuals-getting-started-with-developer-tools . First things first, and you need to download and install NodeJS , in case if you do not have npm setup in your machine.  Second step is to run couple of npm commands to install Power BI Visual Tools and create & publish the cert. Finally, create your own package and upload with the help of npm. 1.       npm install -g powerbi-visuals-tools 2.       pbiviz --create-cert pbiviz --install-cert 3.       pbiviz new My Visual name – This will create the file under the name of your visual file. 4.      pbiviz start – You need to go to the folder where your custom visual  resides, and run this command. You can change a few things in...
Read more

SharePoint Admin Center

Image
 SharePoint Admin Center allows you to view your organization's SharePoint site including communication sites, channel sites, and sites that belong to Microsoft 365 group. This is same like Site Admin in earlier days of SharePoint versions. You can search organizational site, sort them or filter sites the way you want. Easy way to find and apply/change site settings. Under Policies, click on Sharing - This will allow you to create rules/ set control sharing at the organization level in SharePoint and OneDrive Here, in this page, you can apply settings to restrict how users are allowed to access content in SharePoint and OneDrive. Term store, the word getting populated from SharePoint 2010. You can feed metadata of metadata information of your organization level, program level, project level in Term store. helps your organization's knowledge better.  AI soon will play a vital role here and using AI, you can acyually find collect and process and finally yoour organization's d...
Read more