To process the data from granularity tables to main tables, we follow a mechanism called slowly changing dimensions type.
Eg: there is a customer table where it holds customer details. If there is any change, in SCD’s there should be a manipulation in the process.
Slowly changing dimensions [SCD’s]
Type -1 →
Here in emp-history after type-1 performance is done the location of vinay is changed from the location HYD to USA (i.e., replaced).
Note: one customer only one location.
Note: highest version indicates the current location of the customer.
b) modifies earlier records status to “expired”
Note: status=’current’ means the customer is in that location.
b)modifies old record end-dt to new record start-date
Note: current location of customer identified with end-date=’9999-12-31’
For every customer previous/current locations maintained.
Note: if you want to increase the number of history rows those many columns should be added to the history tables, which is burden to the system.
In real time we use frequently type-2 (date mechanism) as history maintenance mechanism [see 66 page no for practical’s]
Maintenance cleanup task: It removes files left over from a maintenance plan.
Notify operator task: It sends an e-mail msg to any SQL server agent operator.
Rebuilt index task: It arranges or re-organizes data or the data index pages by rebuilding the indexes. This improves the performance of index scans and seeks.
Reorganize index task: It defragments and compacts clustered and non-clustered indexes on table and views.
Shrink database task: It reduces the disk space consumed by database and log files by removing empty data and log prices.
Update statics task: It updates statistics of the object if there are already collected.
It execute T-SQL server database, command, queries…etc.
Go to execute T-SQL taskàrt clickàeditàexecute
T-SQL statement taskàconnectionàSRC (take any connection)
T-SQL statement: Use DB-MSBI;
Delete from EMP_NEW1;
In SSIS along with Type-1 and Type-2 we implement fixed attribute.
In this situation the important business information field taken as “fixed”. If there is any change for values in the column we can take appropriate actions like failing or ignoring the operation.
Take OLEDB source.
Connection manager: DB_MSBI
Table or view: EMP_HIST
Specify the column EID as business key.
Set change type: fixed attribute
Select fail the transformation if changes are detected in a fixed attributeànextànextàfinish.
Like above with only below two changes
Like above with only below two changes
Set change type: historical attribute
Select use a single column to show current and expired records.
Column to indicate current record: status
Value when current: current
Expired value: expired.
Select use start and end dates to identify current and expired records.
Start date column: start-date
End date column: end-date
Variable to set date values: system::container
In control flow task →
Rt click → edit
Cache mode: Full cache specify how to handle rows with no matching.
Entries: Redirect rows to no match output.
Connection: OLEDB connection manager: local host: DB-MSBI
Use table or view: EMP-HIST
Columns: Connect EID, ENAME, ELOC to HIST_table for validation click okàok.
OLEDB command: rt click → Edit
Connection manager tab: Connection manager
Component properties tab:
SQL command: update EMP-HIST set END-DT=? Where EID=? And END-DT is null.
Column names tab: Connect EID to parameter-1, ST-DT to parameter-0 click ok → ok
OLEDB source: EMP-Daily
Lookup: rt clickàedit
Take OLEDB Destination as EMP-HIST and connect the fields except END-DT.
Go to data profiling taskàrt click → edit →
Profile request →
Profile type: candidate key profile request
Column length distribution profile request
Column null ratio profile request
Column pattern profile request
Column statistics profile request
Column value distribution profile request
Functional dependency profile request
Value inclusion profile request
Create ADO.Net connection for the source table
1. SSIS menu → New connection → ADO.Net → Add → Data connection: MSBI-DB
2. Data profile task on control flow
Rt click → edit
Destination type: file connection.
Destination: New connection → create file → c:party-profile-request.
Click Quick profile →
ADO.Net connection: MSBI-DB
Table or view: party
Select the required options
Click ok → ok.
3. Execute data profile task and observe the file.
4. Start menu→program→SS 2008→Integration service →data profile viewers→open→specify file path (c:party-profile-result) and monitor the analysis by selecting each attribute.
Note: In other ETL tools for profiling we need to go for 3rd party tools.
|SCCM||SQL Server DBA|
|Team Foundation Server||BizTalk Server Administrator|
As a content writer and storyteller, Raunaak Mitra regards himself to be a prodigy in writing. He firmly believes that language is borderless, and anyone can write as long as they have a strong narrative capability and the ability to emote feelings into words. Authors like F. Scott Fitzgerald and R. K. Narayan have influenced him to keep the writing as simple as possible for anyone to understand. Other than being well-versed about technological trends like AI, Machine Learning, AR, VR, Gaming, Microprocessors, Cloud Computing, Industry 4.0, literally any technological advancement (old or new), he also shares a knack to curate fiction on sci-fi, satire, and thriller genres.