SSIS ( SQL Server integration Services)
SSIS ( SQL Server integration Services)
OLTP (Online Transactional Process) is not good for
reporting & analysing services in order to go for analysis & reporting
we need go for few stages.
Using SSIS convert OLTP data in to Data warehouse.
After that using SSAS create cubes.
SSRS,Power BI, Excel do the reporting.
Can we do the Reporting straight away not doiung the SSIS, SSAS?
Of-course It's Possible, Problem is the Speed. In all these databases data is in Normalized format but in the Data warehouses it's in demoralized format.
To do the reporting services we can do from the following layers?
- From Original Data Sources
- Warehouse
- From Cube
When doing the ETL, OLTP to Data warehouse in between these data warehouse there can be other additional data bases.
Most of the companies they have additional data bases in between OLTP to data warehouse.
- Load Data Base
100% Same from the OLTP to Data warehouse no change. In the ETL process we store data in the Load Data base. If some problem happens (change in the data) we need to find from where in the problem occurs. In ETL process or in the Data warehouse.
- Store Data Base
In OLTP Data has been expired in 30 Days in these type of scenario Data stored in store data bases.
- Staging Data bases
Staging Data bases are the most important and famous data bases uses in the Data warehouses. Data Cleaning and Transformation.
Difference between Data cleaning and Transformation
Cleaning, Some tables have dirty data that meanings e.g blank values. so those data need to clean before transformed in to the data warehouse. ( How to do the Cleaning Part i did a separate post in this blog, you can gain more knowledge from there.)
Transformation
E.g Some table have 03 attributes (F_name,M_name,L_name) and when transformation need to add in to a one attribute (Full_name).
Transformation
E.g Some table have 03 attributes (F_name,M_name,L_name) and when transformation need to add in to a one attribute (Full_name).
05 -Types in SCD
================
0 - No Change
1 - Directly Change
2 - Keep history and a create a new raw
3 - Keep previous one value into a new column
4 - Based on type 02 but keep history in separate table.
In SSIS SCD tools we only can define first 03. If u want practice other 02 you have to use a SSIS along with a another tool.
How to create a Simple ETL in SSIS
First you go to new project - Business Intelligence
- Integration Service Project
After that Create a Connection Manager
- Right Click on Connection Manager
- Select a OLDB connection
- Select your Server
First you need to know about how to create a Group container.
- First you go to SSIS toolbox
- Under container - Select Sequence Container.
We want to create a ETL that's mean Data Flow Task
After that double click the Data flow task then it's appears the following window.
You can select the whole data base or select the attributes from the different tables.
In here i selected some attributes from Product table in Adventure works database.
The data source connected to SCD ( Slowly changing Dimension)you can drag and drop from SSIS tools.
In there you have to select Business key, from the Business key you can do the insert, update and delete.
Then Select the types of slowly changing dimension
Next place is need you to define
Fixed Attributes
- If there is a change in Fixed attribute you can stop the ETL process.
Changing attributes
- Changing attribute is the place that you need to change all the records in dimension table.
Go for the Second option.
Ones you finished its automatically create the SCD for you.
Ones you Create that Finally how to execute that
Many ways -
Right Click on the data flow then click execute task
Is every thing is ok u get the following
Why we need SCD - 0 if allowed to change in some time OLTP erroniouesly some one can update a record that no need to change.
E.g. change (B_date)
Bu that one no need to update in DW.
Comments
Post a Comment