DBPedias

Your Database Knowledge Community

Matt Wollner

  1. Add SSD to Dell Precision

    I just received a new Dell Precision M4600 from work. I wanted to install Windows on a SSD so that it would fly. I know that if you are configuring the m4600 on the Dell website, you are given the option to add a 2nd 128GB (SATA3) Mobility Solid State Drive for just $480.00.

    Since my laptop was ordered for me, I did not have the option to add this on, and $500 is a bit much when you can purchase a 128GB SSD for $100-$200. I called Dell to find out what my options were. After several calls and live chats, I was told that there were 0 options to add on a new SSD disk.

    I did a little digging on my on and found 2 options.

    1. Replace the DVD drive with a HD Caddy and install a 2.5″ SSD. The main problem with this approach is that you will loose your internal DVD drive.
    2. Install a mSATA SSD. This can be installed in the WWAN slot. I don’t have a WWAN card so that makes this an easy decision.

    I purchased a Kingston 64 GB mSATA SSD for $89 on Amazon. Here are the very simple steps to install the mSATA card

    1. Remove Battery
    2. Remove the Base Cover
    3. Install mSATA card in the WWAN slot. You will also need 1 tiny screw. I don’t know the size or name. I just hapended to have one.

      Dell has some really good step by step instructions to installing components.
      Dell’s Precision M4600 Owner’s Manual - http://support.dell.com/support/edocs/systems/wsM4600/en/SM/index.html

    After putting the cover and battery back in I turned on my PC and Windows instantly recognized the drive. I went into my Disk management and formatted the drive. Being a SSD it took about 15 seconds. Then I installed window to the new drive.

    When installing Windows you want to make sure that all you user files and program files get installed to your main hard drive and not your SSD. Make sure that you run RegEdit and edit the following before installing any other programs or creating any users

  3. Table Size History

    When maintaining a data warehouse, it is import understand the size and disk requirements of your tables over time. My current client has asked that I generate a report showing table disk usage over time, so that we can predict table growth.

    I was looking around and found the procedure sp_spaceused. This returns the row count, data usage and index usage for a given table. I decided to create a fact table to save the table info on a daily basis. The SSIS package should not be too difficult to create but it turned out to be a much bigger challenge then I expected.

    First I created a Fact Table and couple of Dimension Tables. I took my standard Dim.Date and Created a Dim.TableDefinition to store the table name and other table related info. I used sys.tables to get the base info. 

    CREATE TABLE Fact.TableSize(
	TableSizeKey int IDENTITY(1,1) NOT NULL,
	RunDateKey int NULL,
	LastInsertDateKey int NULL,
	TableDefinitionKey int NULL,
	TableRows bigint NULL,
	ReservedSpace bigint NULL,
	DataSpace bigint NULL,
	IndexSpace bigint NULL)

CREATE TABLE Dim.TableDefinition(
	TableDefinitionKey int IDENTITY(1,1) NOT NULL,
	SchemaName varchar(50) NOT NULL,
	TableName varchar(50) NOT NULL,
	TableSchemaName varchar(100) NOT NULL,
	TableDescription varchar(max) NULL)

--Populate Dim.TableDefinition
SELECT
	S.name + '.' + T.name  AS TableSchemaName
	,S.name AS SchemaName
	,T.name  AS TableName
	,NULL AS TableDescription
FROM sys.tables T
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
WHERE LEFT(T.name, 3) IN ('dim','fac')

    To create the SSIS package I started off in the Data flow. I first created an OLE DB Source, with the SQL command 

     sp_spaceused 'dbo.Table1'

    and clicked on the Columns tab. I was sad to see that the Editor only pulled back the default set of columns that the procedure returns, when no parameters are passed in.

    To get the proper output I stubbed out a select statement before calling the procedure.

    IF 1 = 2 BEGIN
	DECLARE @SchemaStub TABLE
		(TableName VARCHAR(50)
		,TableRows VARCHAR(50)
		,ReservedSpace VARCHAR(50)
		,DataSpace VARCHAR(50)
		,IndexSpace VARCHAR(50)
		,UnusedSpace VARCHAR(50))

	SELECT  TableName
		,TableRows
		,ReservedSpace
		,DataSpace
		,IndexSpace
		,UnusedSpace
	FROM @SchemaStub
END

EXEC sp_spaceused 'dbo.DimDate'

    The next problem I encountered is when I tried to pull the table name from a variable. 

    IF 1 = 2
	…
END
EXEC sp_spaceused  @objname  = ?

    Error: Parameters cannot be extracted from the SQL command….

    I also tried declaring @objname at the top, but that did not work either. So I decided to change the select statement into a variable.

    Back on the Control Flow I created an Execute SQL Task. I used a SELECT against sys.tables to get a list of all fact and dimension tables. The Result Set of the task is saved to an Object variable, vTableList. I then used a For Each Loop, for each table value in the vTableList. I then used a Script Task to replace the table name in the select statement above. 

    
        Dts.Variables("vSpaceUsedSQL").Value = "IF 1 = 2 BEGIN DECLARE @SchemaStub TABLE( " & _
            "TableName VARCHAR(50),TableRows VARCHAR(20),ReservedSpace VARCHAR(20),DataSpace VARCHAR(20),IndexSpace VARCHAR(20))" & _
            " Select TableName,TableRows,ReservedSpace,DataSpace,IndexSpace " & _
            " FROM    @SchemaStub END" & _
            " EXEC sp_spaceused '" & Dts.Variables("vTableName").Value & "'"

    To wrap up the package
    1. I added a SQL Task to delete any data from today, incase it is run twice in the same day.
    2. Set the RunDateKey to the key value of GETDATE()
    3. I created some pretty Reporting service reports to show my table sizes over time.

  4. SSIS Logging and Auditing Framework

    Thank you for all of you that came out to my presentation at the Atlanta Microsoft BI user group, last Monday. We had a pretty good turn out, 40+.

    The slide deck can be found on the user group web site. http://atlantabi.sqlpass.org/Resources.aspx

    Here are the links to the other posts that I referred to during the presentation.

  5. Customize SSIS Logging – Avoid dups in Parent Packages
  6. Table Auditing with XML

  7. SSIS Logging and Auditing Framework

    Thank you for all of you that came out to my presentation at the Atlanta Microsoft BI user group, last Monday. We had a pretty good turn out, 40+.

    The slide deck can be found on the user group web site. http://atlantabi.sqlpass.org/Resources.aspx

    Here are the links to the other posts that I referred to during the presentation.

  8. Customize SSIS Logging – Avoid dups in Parent Packages
  9. Table Auditing with XML

  10. Table Auditing with XML

    When working with meta data tables it can be very useful to track when people make changes to the data. How do you track any change against any number of columns for any table? I have implemented a fairly simple solution that uses triggers and stores all changes as XML into one change log table.

    I know triggers are very expensive and should be avoided unless the are truly needed. There are several DBAs out there that abuse them and I am sent into help figure out why their performance is so bad. I feel that in this instance they can be quite handy. Again, this is intended for meta data table or smaller tables. If you are working on large tables, this can significantly slow down updates to the table.

    The table
    The logging table has the Table Name, Previous Records as XML, Current Records as XML, User, Time Stamp.

    	CREATE TABLE dbo.MetaDataChangeLog(
		MetaDataChangeLogId INT IDENTITY(1,1) NOT NULL,
		TableName VARCHAR(255) NOT NULL,
		PreviousRowXmlValues XML NULL,
		CurrentRowXmlValues XML NULL,
		RowCreateSource VARCHAR(255) NOT NULL,
		RowCreateDateTime DATETIME NOT NULL,
	 CONSTRAINT PK_MetaDataChangeLogId PRIMARY KEY CLUSTERED (MetaDataChangeLogId ASC)
	) 

	ALTER TABLE dbo.MetaDataChangeLog ADD  DEFAULT (GETDATE()) FOR RowCreateDateTime
	GO

    The Trigger (I am going to use my SSIS Config table in this example).
    The trigger is going to be set up for AFTER INSERT, UPDATE, DELETE. It then does a SELECT *, from the INSERT and DELETE data sets, FOR XML AUTO. Updates have both an insert and a delete. The insert will have a delete value of NULL and vice versa.

    	CREATE TRIGGER ETL.trSSISConfigurationsChangeLog
	   ON   ETL.SSISConfigurations
	   AFTER INSERT,UPDATE,DELETE
	AS
	BEGIN
		SET NOCOUNT ON;

		DECLARE @previousRowXmlValues XML
		DECLARE	@currentRowXmlValues XML
		DECLARE @rowCreateSource VARCHAR(100)

		--Get the previous value
		SET @previousRowXmlValues = (SELECT * FROM DELETED FOR XML AUTO)
		--Get the current value
		SET @currentRowXmlValues = (SELECT * FROM INSERTED FOR XML AUTO)
		--Gets the SQL User that has made the update
		SET @rowCreateSource = SYSTEM_USER

		-- Insert the changes into the Log table
		INSERT INTO MetaDataChangeLog(
			TableName, PreviousRowXmlValues, CurrentRowXmlValues, RowCreateSource)
		VALUES (
			'ETL.SSISConfigurations',	@PreviousRowXmlValues, @CurrentRowXmlValues, @RowCreateSource)
	END
    The Data
    This is what some sample data looks like. I inserted a row, updated it, then deleted it. (If you affect more then 1 row at a time all rows will be captured in row in the same XML.

    Insert 1 row
    PreviousRowXmlValues: NULL
    CurrentRowXmlValues:

    			<INSERTED ConfigurationFilter="vTestVariable" ConfiguredValue="Test" PackagePath="\Package.Variables[User::vTestVariable].Properties[Value]" ConfiguredValueType="String" />

    Update 1 row
    PreviousRowXmlValues:
    			<DELETED ConfigurationFilter="vTestVariable" ConfiguredValue="Test" PackagePath="\Package.Variables[User::vTestVariable].Properties[Value]" ConfiguredValueType="String" />
    CurrentRowXmlValues:
    			<INSERTED ConfigurationFilter="vTestVariable" ConfiguredValue="Updated Test" PackagePath="\Package.Variables[User::vTestVariable].Properties[Value]" ConfiguredValueType="String" />

    Delete 2 rows
    PreviousRowXmlValues:
    			<DELETED ConfigurationFilter="vTestVariable2" ConfiguredValue="Test" PackagePath="\Package.Variables[User::vTestVariable].Properties[Value]" ConfiguredValueType="String" />
			<DELETED ConfigurationFilter="vTestVariable" ConfiguredValue="Test" PackagePath="\Package.Variables[User::vTestVariable].Properties[Value]" ConfiguredValueType="String" />
    CurrentRowXmlValues: NULL

    Data saved in Log Table


    View the data
    That’s great that we now have a log of each change to our ETL.SSISConfigurations. But clicking on each of the XML statements is not the cleanest way to view the changes. For this we can create a view on top of each table and use XQuery to display the data. I do a CROSS APPLY, instead of an inline statement, so that if more then 1 row was affected in the change, it will be shown in the results. You will see that 5195 has two records, one for the Delete (old value) and one for the Insert (new value). 5196 deleted 2 rows so 2 rows show up. Simon Incegives a good example of how to flatten XML in SQL.




    	CREATE VIEW [Audit].[vSSISConfigurationsChangeLog] AS 

	SELECT  MetaDataChangeLogId 	AS ChangeId,
			'DELETED'			AS ChangeType,
			RowCreateSource		AS ChangeSource,
			RowCreateDateTime		AS ChangeDateTime,
	        R.Data.value('(@ConfigurationFilter)[1]', 'VARCHAR(255)') AS ConfigurationFilter,
	        R.Data.value('(@ConfiguredValue)[1]', 'VARCHAR(255)') AS ConfiguredValue,
	        R.Data.value('(@PackagePath)[1]', 'VARCHAR(255)') AS PackagePath,
	        R.Data.value('(@ConfiguredValueType)[1]', 'VARCHAR(20)') AS ConfiguredValueType
	FROM    MetaDataChangeLog MDC
	CROSS APPLY MDC.PreviousRowXmlValues.nodes('/DELETED') AS R(Data)
	WHERE   TableName = 'ETL.SSISConfigurations' 

	UNION	

	SELECT  MetaDataChangeLogId 	AS ChangeId,
			'INSERTED'			AS ChangeType,
			RowCreateSource		AS ChangeSource,
			RowCreateDateTime		AS ChangeDateTime,
			R.Data.value('(@ConfigurationFilter)[1]', 'VARCHAR(255)') AS ConfigurationFilter,
	        R.Data.value('(@ConfiguredValue)[1]', 'VARCHAR(255)') AS ConfiguredValue,
	        R.Data.value('(@PackagePath)[1]', 'VARCHAR(255)') AS PackagePath,
	        R.Data.value('(@ConfiguredValueType)[1]', 'VARCHAR(255)') AS ConfiguredValueType
	FROM    MetaDataChangeLog MDC
	CROSS APPLY MDC.CurrentRowXmlValues.nodes('/INSERTED') AS R(Data)
	WHERE   TableName = 'ETL.SSISConfigurations'

    Now we have a single table that will track all data changes. You can quickly query the table to view the data or you can write a XQuery against the XML and work with it as if it was a table.

  11. Table Auditing with XML

    When working with meta data tables it can be very useful to track when people make changes to the data. How do you track any change against any number of columns for any table? I have implemented a fairly simple solution that uses triggers and stores all changes as XML into one change log table.

    I know triggers are very expensive and should be avoided unless the are truly needed. There are several DBAs out there that abuse them and I am sent into help figure out why their performance is so bad. I feel that in this instance they can be quite handy. Again, this is intended for meta data table or smaller tables. If you are working on large tables, this can significantly slow down updates to the table.

    The table
    The logging table has the Table Name, Previous Records as XML, Current Records as XML, User, Time Stamp.

    	CREATE TABLE dbo.MetaDataChangeLog(
		MetaDataChangeLogId INT IDENTITY(1,1) NOT NULL,
		TableName VARCHAR(255) NOT NULL,
		PreviousRowXmlValues XML NULL,
		CurrentRowXmlValues XML NULL,
		RowCreateSource VARCHAR(255) NOT NULL,
		RowCreateDateTime DATETIME NOT NULL,
	 CONSTRAINT PK_MetaDataChangeLogId PRIMARY KEY CLUSTERED (MetaDataChangeLogId ASC)
	) 

	ALTER TABLE dbo.MetaDataChangeLog ADD  DEFAULT (GETDATE()) FOR RowCreateDateTime
	GO

    The Trigger (I am going to use my SSIS Config table in this example).
    The trigger is going to be set up for AFTER INSERT, UPDATE, DELETE. It then does a SELECT *, from the INSERT and DELETE data sets, FOR XML AUTO. Updates have both an insert and a delete. The insert will have a delete value of NULL and vice versa.

    	CREATE TRIGGER ETL.trSSISConfigurationsChangeLog
	   ON   ETL.SSISConfigurations
	   AFTER INSERT,UPDATE,DELETE
	AS
	BEGIN
		SET NOCOUNT ON;

		DECLARE @previousRowXmlValues XML
		DECLARE	@currentRowXmlValues XML
		DECLARE @rowCreateSource VARCHAR(100)

		--Get the previous value
		SET @previousRowXmlValues = (SELECT * FROM DELETED FOR XML AUTO)
		--Get the current value
		SET @currentRowXmlValues = (SELECT * FROM INSERTED FOR XML AUTO)
		--Gets the SQL User that has made the update
		SET @rowCreateSource = SYSTEM_USER

		-- Insert the changes into the Log table
		INSERT INTO MetaDataChangeLog(
			TableName, PreviousRowXmlValues, CurrentRowXmlValues, RowCreateSource)
		VALUES (
			'ETL.SSISConfigurations',	@PreviousRowXmlValues, @CurrentRowXmlValues, @RowCreateSource)
	END
    The Data
    This is what some sample data looks like. I inserted a row, updated it, then deleted it. (If you affect more then 1 row at a time all rows will be captured in row in the same XML.

    Insert 1 row
    PreviousRowXmlValues: NULL
    CurrentRowXmlValues:

    			<INSERTED ConfigurationFilter="vTestVariable" ConfiguredValue="Test" PackagePath="\Package.Variables[User::vTestVariable].Properties[Value]" ConfiguredValueType="String" />

    Update 1 row
    PreviousRowXmlValues:
    			<DELETED ConfigurationFilter="vTestVariable" ConfiguredValue="Test" PackagePath="\Package.Variables[User::vTestVariable].Properties[Value]" ConfiguredValueType="String" />
    CurrentRowXmlValues:
    			<INSERTED ConfigurationFilter="vTestVariable" ConfiguredValue="Updated Test" PackagePath="\Package.Variables[User::vTestVariable].Properties[Value]" ConfiguredValueType="String" />

    Delete 2 rows
    PreviousRowXmlValues:
    			<DELETED ConfigurationFilter="vTestVariable2" ConfiguredValue="Test" PackagePath="\Package.Variables[User::vTestVariable].Properties[Value]" ConfiguredValueType="String" />
			<DELETED ConfigurationFilter="vTestVariable" ConfiguredValue="Test" PackagePath="\Package.Variables[User::vTestVariable].Properties[Value]" ConfiguredValueType="String" />
    CurrentRowXmlValues: NULL

    Data saved in Log Table


    View the data
    That’s great that we now have a log of each change to our ETL.SSISConfigurations. But clicking on each of the XML statements is not the cleanest way to view the changes. For this we can create a view on top of each table and use XQuery to display the data. I do a CROSS APPLY, instead of an inline statement, so that if more then 1 row was affected in the change, it will be shown in the results. You will see that 5195 has two records, one for the Delete (old value) and one for the Insert (new value). 5196 deleted 2 rows so 2 rows show up. Simon Incegives a good example of how to flatten XML in SQL.




    	CREATE VIEW [Audit].[vSSISConfigurationsChangeLog] AS 

	SELECT  MetaDataChangeLogId 	AS ChangeId,
			'DELETED'			AS ChangeType,
			RowCreateSource		AS ChangeSource,
			RowCreateDateTime		AS ChangeDateTime,
	        R.Data.value('(@ConfigurationFilter)[1]', 'VARCHAR(255)') AS ConfigurationFilter,
	        R.Data.value('(@ConfiguredValue)[1]', 'VARCHAR(255)') AS ConfiguredValue,
	        R.Data.value('(@PackagePath)[1]', 'VARCHAR(255)') AS PackagePath,
	        R.Data.value('(@ConfiguredValueType)[1]', 'VARCHAR(20)') AS ConfiguredValueType
	FROM    MetaDataChangeLog MDC
	CROSS APPLY MDC.PreviousRowXmlValues.nodes('/DELETED') AS R(Data)
	WHERE   TableName = 'ETL.SSISConfigurations' 

	UNION	

	SELECT  MetaDataChangeLogId 	AS ChangeId,
			'INSERTED'			AS ChangeType,
			RowCreateSource		AS ChangeSource,
			RowCreateDateTime		AS ChangeDateTime,
			R.Data.value('(@ConfigurationFilter)[1]', 'VARCHAR(255)') AS ConfigurationFilter,
	        R.Data.value('(@ConfiguredValue)[1]', 'VARCHAR(255)') AS ConfiguredValue,
	        R.Data.value('(@PackagePath)[1]', 'VARCHAR(255)') AS PackagePath,
	        R.Data.value('(@ConfiguredValueType)[1]', 'VARCHAR(255)') AS ConfiguredValueType
	FROM    MetaDataChangeLog MDC
	CROSS APPLY MDC.CurrentRowXmlValues.nodes('/INSERTED') AS R(Data)
	WHERE   TableName = 'ETL.SSISConfigurations'

    Now we have a single table that will track all data changes. You can quickly query the table to view the data or you can write a XQuery against the XML and work with it as if it was a table.

  12. Presenting @ Atlanta Microsoft BI user group

    I will be presenting at the next Atlanta Microsoft BI user group. Monday, July 25th, 2011.
    http://atlantabi.sqlpass.org/

    Topic: SSIS Logging and Auditing Framework
    Level: Beginer / Intermediate

    Meeting Place
    Matrix Resources
    115 Perimeter Center Place
    Suite 250 (South Terraces Building)
    Atlanta, GA 30346

    Overview: Proper logging in your SSIS packages can help save hours in determining what went wrong with your package execution. Auditing may be required to track what package touched what data and when. This session will review some best practices in setting up logging and auditing, walk through a standardized SSIS template that can be easily used as a starting point for all SSIS packages and review a reporting solution to quickly monitor your new package logs.

    About Me: Matt Wollner in a Business Intelligence consultant with Key2 Consulting. He has extensive experience in data warehousing design, SQL Server database, ETL processes, multidimensional database and reporting systems. He has been working with SQL server products for the past 13 years with a focus on BI for the last 10 years. Matt has successfully delivered large scale data warehousing solutions utilized by companies in the telecom, financial services, information aggregation, pharmaceutical and state and local governments.

  13. Presenting @ Atlanta Microsoft BI user group

    I will be presenting at the next Atlanta Microsoft BI user group. Monday, July 25th, 2011.
    http://atlantabi.sqlpass.org/

    Topic: SSIS Logging and Auditing Framework
    Level: Beginer / Intermediate

    Meeting Place
    Matrix Resources
    115 Perimeter Center Place
    Suite 250 (South Terraces Building)
    Atlanta, GA 30346

    Overview: Proper logging in your SSIS packages can help save hours in determining what went wrong with your package execution. Auditing may be required to track what package touched what data and when. This session will review some best practices in setting up logging and auditing, walk through a standardized SSIS template that can be easily used as a starting point for all SSIS packages and review a reporting solution to quickly monitor your new package logs.

    About Me: Matt Wollner in a Business Intelligence consultant with Key2 Consulting. He has extensive experience in data warehousing design, SQL Server database, ETL processes, multidimensional database and reporting systems. He has been working with SQL server products for the past 13 years with a focus on BI for the last 10 years. Matt has successfully delivered large scale data warehousing solutions utilized by companies in the telecom, financial services, information aggregation, pharmaceutical and state and local governments.

  1. 1
  2. Next ›
  3. Last »