Trigger is a action which will perform when the table gets insert, update or delete. We can choose the options on what the trigger should get invoked and create the syntax as below
CREATE Trigger triggername
FOR INSERT,UPDATE,DELETE -- We can add or remove the criteria here
-- Action steps needs to carried out should be given here.
Creating Trigger in SQL Server Table was last modified: November 15th, 2017 by
SSIS Commit Rollback
In SSIS each and every component will commit its transaction after it got executed. But if we have a scenario where the commit should take place only after a set of component execution in ETL and for this we have an option in the Connection Object – > Properties -> Set RetainSameConnection as True.
This will ensure to commit only if all the components went success belongs to the same connection object.
You can give explicit commit statement like
- Begin Transaction
- Commit Transaction
- Rollback Transaction -> This you can write under the ONERROR event
https://www.sqlpublic.com/wp–content/uploads/2017/10/img_59f0cd54baa1f.png” rel=”nofollow”>https://www.sqlpublic.com/wp–content/uploads/2017/10/img_59f0cd54baa1f.png” alt=”” />
SSIS commit and Rollback was last modified: November 15th, 2017 by
For tracking the database for any incidents like droping any objects and event monitoring.
Starting the Trace
EXEC SP_Configure ‘default trace enabled’,1
Stopping the Trace
EXEC SP_configure ‘default trace enabled’,1
To find the trace information for any sql server table
select * from sys.traces where id =1
How to find trace or log information in sql server ? was last modified: October 14th, 2017 by
Search particular column sql database
You can search for a particular column that present in the list of database tables in sql server.
SCHEMA_NAME(SCHEMA_ID) + '.' + TBL.NAME AS TableName,
COL.NAME AS ColumnName
FROM SYS.TABLES AS TBL
INNER JOIN SYS.COLUMNS COL
ON TBL.OBJECT_ID = COL.OBJECT_ID
WHERE COL.NAME LIKE '%ENTER COLUMN NAME%'
ORDER BY 1
Query to search for particular column from sql database was last modified: October 10th, 2017 by
data source Initialization failed
This error occurs due to Excel Version Compatibility. When a New Data Model is opened in an unsupported old Excel Version then it will throw out the “Initialization of data source failed” error.
Initialization of data source failed – When Opening PowerPivot Excel was last modified: October 6th, 2017 by
Dimension Tables Types
Dimension table contains the master data information. A dimension table has a primary key column that uniquely identifies each dimension record. The dimension table is associated with a fact table using the primary key.
confirmed dimension — The value will be refered by more than one dimension table
junk dimension — Combined values of transaction
degenerate attribute — If a dimension data kept on a fact table for reference
Role Playing Dimension — Date dimension which act as a orderdate dimension and purchasedate dimension.
Types of Dimension Tables in SSAS was last modified: October 5th, 2017 by
Fact Table Types
Fully Additive Facts: This measure which can be added across all the associated dimensions.
Example: sales amount is a fact which can be summed across different dimensions like customer, geography, date, etc.
Semi-Additive Facts: These are facts which can be added across only few dimensions.
Example: bank balance is a fact which can be summed across the customer dimension but it cannot be added across the date dimension
Non-Additive Facts: These are facts which cannot be added across any of the dimensions in the cube.
Example: profit margin is a fact which cannot be added across any of the dimensions. we can not add against product dimension.
Derived Facts: Derived facts are the facts which are calculated from one or more facts,
For example: profit margin.Which is calculated when needed
Factless Facts: Fact table will not have a measure they will have only the key values(Foreign key) refered to the dimension are called factless fact table.
Types of measures/facts tables was last modified: October 1st, 2017 by