SSIS Package Configuration

Please refer to the video for details. SSIS Package Configuration


ssispackage-configuration

------------------

 Finalpoint:

PackageConfiguration: XML Indirect method:

The .dtsconfig file which holds the connection to database , the path of the file is put in
an environment variable.

PackageConfiguration:SQL Server

Configure and it creates a Table , say "SSISConfigurations"  in the specified database.

In this configuration , specify a Filter that identifies it as unique along with PackagePath.

SSMS:Navigate to the specified database, risht click choose "Generate Script" , then choose
under "Table ".. "Script - True... , choose the table "
"SSISConfigurations" , in query window it puts the script.

To move production:

This 
"SSISConfigurations" script give to dba and the environment variable that holds the connection 
detail.

 -----------------


1.Environment variable: (My Computer - Properties -Advanced - Environmental Variable -Product Id ,
Right Click-Solution Explorer-Package Configuration option [ Enable Package Configuration ]-Environmental variable-,Wizard -Map Variable  Value
2.Variables - New User Variables : regedit-HKEY_CURRENT_USER-Add -Key-
    Right Click-Solution Explorer-Package Configuration-Registry-,Wizard -Map Variable  Value
3.xml configuration - 2 methods :Direct ( create the Connection string as Env. variable,map in package configuration), Indirect
i.Direct- Right Click-Solution Explorer-Package Configuration-choose a new file to store all parameters - say PkgConfig.dtsConfig
ii.Environmental variable - ConfigPath point to the PkgConfig.dtsConfig ( it has connection string)
4."SQL Server Configuration" - Server Name -.[dbo].[SSISConfigurations]  , Configuration filter:VariableDemo { this makes package unique}
5. Go to DB(in explorer)- right click-Task-Generate Script - Script Data(True),Tables(SSISConfigurations):Puts script into Query window
and with the values the DBA has to just run that script (Test or Prod) and create the necessary Environmental variables.

Oracle OBIEE ( Oracle Business Intelligence Enterprise Edition)

Operational Data Store (ODS):Data Warehousing (DWH):Enterprise Data Warehouse (EDW)

DWH Concepts and Fundamentals


Operational Data Store (ODS) : - Staging Area. Data updated from ERP etc.

Data Warehousing (DWH) : Data from Staging Area loaded for data storage.

Enterprise Data Warehouse (EDW) : Data Sources from different Business Area updating data (Data Marts etc.) [ If clear pictures of Business processes are known , then this is having integrated data.]

Bill Inmon (Normalization process , RDBMS) vs. Ralph Kimball (Dimensional Modeling)

datawarehousing-inmon-kimball

DW - Normalization

Answers collected from the below site.Please refer to the site for details. database-normalization

Normalization of Database (eliminate data duplication & maintain data integrity)
1NF: student(s_id,s_name) subject(subject_id,student_id,subject)
Each column have a unique value.
Each row of data have a unique identifier i.e. Primary key.
Table:Student , Subject (sid,sname,subject - sname repeats in rows)
2NF: All 1NF + if table has concatenated primary key ( no partial dependency
of any column on primary key.) ( if any column depends on one part of  concatenated key then table fails 2NF)
Customer(cust_id,cust_name,ord_id,ord_name,sale_det)
PK:(cust_id+ord_id)
cust_name ( depends on only customer_id:FAILURE)
ord_name   ( depends on only ord_id:FAILURE)
Customer(cust_id,cust_name)  Order_Detail( ord_id, ord_name)
Sales(cust_id,ord_id,sale_det)
3NF: move the transitive functional dependency to another table.non-prime attribute must depend on primary key.
Student(stud_id,stud_name,dob,zip(street,city,state - transitive) -move to)
Student(stud_id,stud_name,dob,zip)  Address(Zip,Street,city,state)
BCNF:higher version of 3 NF does not have multiple candidate keys.

SQL SERVER – Difference Between Candidate Keys and Primary Key
Primary key: not null & uniquely defines the row              Candidate Key: can be null, unique

DW - SCD - Slowly changing dimensions

Dimension - in data management & data warehousing - logical grouping of data
[geographical location, customer , product]
Type 0 - Values remain as they were at the time the dimension record was first inserted. [ history ]
Type I - This methodology overwrites old with new data, and therefore does not track historical data.
Supplier_Key Supplier_Code Supplier_Name Supplier_State
123         ABC xyz                               CA
Supplier_Code is the natural key.
Supplier_Key is a surrogate key ( joins use integer rather than character keys.)
Type II:This method tracks historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys and/or different version numbers.
Start_Date,End_Date( null - current one)
Type IV:Is usually referred to as using "history tables".Where one table keeps the current data, and an additional table is used to keep a record of some or all changes.

Slowly_changing_dimension

Oracle - Tuning SQL Statements

Answers collected from the below site.Please refer to the site for details.
Tuning SQL Statements
1.Oracle SQL Analyzer:statement TopSQL:Hint Wizard:SQL Tuning Wizard
2.Index:full table scans:Oracle cost-based optimizer
3.specifying Hints:

Oracle SQL Analyze applies these "rules-of-thumb" when you tune a statement with the Tuning Wizard, and supplies alternative SQL statements when possible.

Use NOT EXISTS instead of NOT IN ( NOT IN uses full Table scan)
Use NOT EXISTS or NOT IN with hints instead of MINUS (MINUS does not use Indexes)
Use TRUNC differently to enable indexes ,Use operators differently to enable indexes
Do not use columns on both sides of operator,Use WHERE in place of HAVING,
Use UNION ALL instead of UNION

The logic of the NOT EXISTS clause tells Oracle not to return the row if it finds a match in both tables.

NOT IN" vs "MINUS": "NOT IN" is much slower than "MINUS" as for "NOT IN" for each row the subquery all rows are scanned.In "MINUS" at one time both table Full Scan is done.

SELECT mod_code FROM SRS.Table1 WHERE mod_code NOT IN (SELECT mod_code FROM SRS.Table2);

SELECT mod_code FROM SRS.Table1 MINUS SELECT mod_code FROM SRS.Table2;