SCD Task Performance Optimization

• Create an index on your dimension table for the business key, followed by the current row identifier. If a clustered index does not already exist, create this index as a clustered index, because this will prevent a query plan lookup from getting the underlying row. This will help the lookup that happens in the SCD as well as all of the updates.


• The row - by - row updates can be changed to set - based updates. To do this, you will need to change the OLE DB command to a Destination adapter to stage the records to a temporary table, then in the Control Flow, add an Execute SQL Task to perform the set - based update.



• If you remove all the OLE DB command transformations, then you can also change the Insert Destination to use fast load and essentially bulk insert the data, rather than performing row - at - a - time inserts.

Drawbacks of using SCD Task in your SSIS package : SCD Task Usage Considerations

• For each row in the input, a new lookup is sent to the relational engine to see if changes have happened. In other words, the dimension table is not cached in memory. That is expensive! If you have tens of thousands of dimension source records or more, this can be a limiting feature of the SCD.


• For each row in the source that needs to be updated, a new update statement is sent to the dimension table (and updates are used by the changing output, historical output, and inferred member output). If you have a lot of updates happening every time your dimension package runs, this will cause your package to run slow.


• The Insert Destination is not set to fast - load. This is because deadlocks can occur between the updates and the inserts. When the insert runs, each row is added one at a time, which can be very expensive.


• The SCD works well for historical, changing, and fixed dimension attributes, and changes can be made to the downstream transformations. However, if you open up the SCD wizard again and make a change to any part of the wizard, you will automatically lose your changes.

Tools for SQL Server Bundle : Toad for SQL Server V4.1

I was going through a Review of Toad for SQL Server article on SQLMag, and the product seems to be definitely promising. I have not yet used the product, but just by getting to know the features it offers, one can say that this product deserves an evaluation.

By reading the below excerpt of the original article, one can easily say that this tool is a bundle of features that SSMS, DB Pro GDR, Internals Viewer for SQL Server, and BIDS Helper offers collectively and still some features would remain unique to this tool. The only issue with this product is the price, which seems to be a little bit high than reasonable.

"There are three versions of Toad for SQL Server. The Professional Version costs $595. Toad for SQL Server Xpert, $1,045, adds T-SQL tuning wizards and capabilities, and Toad for SQL Server Development Suite, $1,295, adds benchmarking capabilities.

Toad's object explorer fairly closely matches the functionality provided by SSMS' object explorer, but double-clicking an object such as a stored procedure or table instantly loads the script for the selected object along with an extremely impressive bevy of context-sensitive metadata. The data includes object sizes, constraints, permissions, and dependencies. This metadata display represents one of Toad's greatest strengths, as it shows exactly the kind of things that DBAs and other serious database professionals would want to see when interacting with objects.

Toad also provides other features beyond its responsive and customizable UI. For example, all versions of Toad include log reader functionality (which allows you to review SQL Server's log files and do undo and replay operations), schema and data comparison and synchronization tools, and custom reporting and automation engines or frameworks. These tools alone can almost justify the cost of the Professional version in some environments.

Of course, Toad comes with the basics that you need to manage and develop databases. There's an ER diagramming tool, a visual query builder, support for backup and restore operations, and the ability to view and manage jobs. In short, Toad offers everything you need to replace SSMS, plus an assortment of other tools and utilities."

As per the product features page, it offers the following features and benefits:

Group Execute – Toad for SQL Server provides the ability to execute SQL scripts across multiple SQL server instances streamlining administration and development requirements for implementing database changes in a whole sale manner.

Compare and Synchronize – Compare and synchronize data, schemas and servers in Toad for SQL Server’s compare and synch tool in a few simple clicks. This allows SQL Server DBA’s and developers to work within maintenance windows in the face of growing datasets, easing the pain of meeting SLAs and reducing potential for failure.

Transaction Log Reader – Toad for SQL Server allows you to recover data stored in transaction log files, rolling back operations and reconstructing transactions.

Xpert Tuning – Completely automate the process of resolving SQL performance issues. Xpert tuning will identify, tune and benchmark problematic SQL in your SQL Server environment, regardless of where your SQL resides. Xpert tuning also provides the ability to investigate indexing strategies through automated index generation.

Recall SQL Scripts – Toad for SQL Server automatically saves every SQL statement executed against the database environment. These statements can be searched and recalled allowing users to quickly locate statements for review and can also be saved for long-term use.

Database Administration – Toad for SQL Server simplifies database administration tasks for creating, altering and managing database objects, SQL code, users, logins, and security across multiple servers.

Version Control - Toad for SQL Server provides integration with many popular version control vendors including: Visual Source Safe, Subversion and CVS.

Intellisense – Automate the coding process with intellisense by completing database code activity in the ‘smart’ SQL Editor window.

SQL Performance Optimization – Optimal SQL performance is ensured through an automated process of identifying, optimizing and testing SQL statements so that the best performing SQL is deployed in the environment.

Difference viewer – Difference Viewer helps you to identify discrepancies between files, data and objects, then edit duplicate data; you can also export data to Excel with linked queries as well as add your own notes to database objects.

Query Builder – Toad for SQL Server facilitates the process of coding SQL by letting you quickly drag and drop tables to create queries, from simple to complex.

Grid Customization – Customize the data grid styles visible in the toolset by applying skins to the views – quickly modifiable in the Configuration wizard.

Reporting – Report Writer in Toad for SQL Server allows you to quickly build a report from the console, facilitating documentation of database objects with the schema reporting. It also includes support for pivot grids, and a chart designer.

Visual Data Modeling – Visualize tables, dependencies and database relationships in your SQL Server environment in a data modeling view.

Download Toad for SQL Server Xpert 30 Days Trial

Download Toad Development Suite for SQL Server 30 Days Trial

BI Interview Question : Types of Dimension

Slowly Changing Dimensions: Attributes of a dimension that would undergo changes over time. It depends on the business requirement whether particular attribute history of changes should be preserved in the data warehouse. This is called a Slowly Changing Attribute and a dimension containing such an attribute is called a Slowly Changing Dimension.

Rapidly Changing Dimensions: A dimension attribute that changes frequently is a Rapidly Changing Attribute. If you don’t need to track the changes, the Rapidly Changing Attribute is no problem, but if you do need to track the changes, using a standard Slowly Changing Dimension technique can result in a huge inflation of the size of the dimension. One solution is to move the attribute to its own dimension, with a separate foreign key in the fact table. This new dimension is called a Rapidly Changing Dimension.

Junk Dimensions: A junk dimension is a single table with a combination of different and unrelated attributes to avoid having a large number of foreign keys in the fact table. Junk dimensions are often created to manage the foreign keys created by Rapidly Changing Dimensions.

Inferred Dimensions: While loading fact records, a dimension record may not yet be ready. One solution is to generate an surrogate key with Null for all the other attributes. This should technically be called an inferred member, but is often called an inferred dimension.

Conformed Dimensions: A Dimension that is used in multiple locations is called a conformed dimension. A conformed dimension may be used with multiple fact tables in a single database, or across multiple data marts or data warehouses.

Degenerate Dimensions: A degenerate dimension is when the dimension attribute is stored as part of fact table, and not in a separate dimension table. These are essentially dimension keys for which there are no other attributes. In a data warehouse, these are often used as the result of a drill through query to analyze the source of an aggregated number in a report. You can use these values to trace back to transactions in the OLTP system.

Role Playing Dimensions: A role-playing dimension is one where the same dimension key — along with its associated attributes — can be joined to more than one foreign key in the fact table. For example, a fact table may include foreign keys for both Ship Date and Delivery Date. But the same date dimension attributes apply to each foreign key, so you can join the same dimension table to both foreign keys. Here the date dimension is taking multiple roles to map ship date as well as delivery date, and hence the name of Role Playing dimension.

Shrunken Dimensions: A shrunken dimension is a subset of another dimension. For example, the Orders fact table may include a foreign key for Product, but the Target fact table may include a foreign key only for ProductCategory, which is in the Product table, but much less granular. Creating a smaller dimension table, with ProductCategory as its primary key, is one way of dealing with this situation of heterogeneous grain. If the Product dimension is snowflaked, there is probably already a separate table for ProductCategory, which can serve as the Shrunken Dimension.

Static Dimensions: Static dimensions are not extracted from the original data source, but are created within the context of the data warehouse. A static dimension can be loaded manually — for example with Status codes — or it can be generated by a procedure, such as a Date or Time dimension.

Reference: Ralph Kimball Articles

Tool to plan security requirements of any BI Solution

While going through a book, I just skimmed through a chapter that described various measures to plan for security requirements of a Business Intelligence solution. One of the recommendation was use of MSAT tool. So I thought of mentioning it on this blog. This tool is not designed to work with SSIS, SSAS and SSRS security in specific. But seems to be a useful tool in environments where compliance is a big consideration, for example - security consideration is a big concern on any project associated with a financial domain client. I am not sure how useful it would be for BI projects, but this seems to be a useful document to produce for quality and audit perspective, especially security audits like BS7799.

Following is an excerpt from the Microsoft Security Assessment Tool home page:

MSAT consists of over 200 questions covering infrastructure, applications, operations, and people. The questions, associated answers, and recommendations are derived from commonly accepted best practices, standards such as ISO 17799 and NIST-800.x, as well as recommendations and prescriptive guidance from the Microsoft Trustworthy Computing Group and other external security sources.

The assessment is designed to identify the business risk of your organization and the security measures deployed to mitigate risk. Focusing on common issues, the questions have been developed to provide a high-level security risk assessment of the technology, processes, and people that supports your business.

Beginning with a series of questions about your company's business model, the tool builds a Business Risk Profile (BRP), measuring your company’s risk of doing business due to the industry and business model defined by BRP. A second series of questions are posed to compile a listing of the security measures your company has deployed over time. Together, these security measures form layers of defense, providing greater protection against security risk and specific vulnerabilities. Each layer contributes to a combined strategy for defense-in-depth. This sum is referred to as the Defense-in-Depth Index (DiDI). The BRP and DiDI are then compared to measure risk distribution across the areas of analysis (AoAs)—infrastructure, applications, operations, and people.

In addition to measuring the alignment of security risk and defenses, this tool also measures the security maturity of your organization. Security maturity refers to the evolution of strong security and maintainable practices. At the low end, few security defenses are employed and actions are reactive. At the high end, established and proven processes allow a company to be more proactive, and respond more efficiently and consistently when needed.

Risk management recommendations are suggested for your environment by taking into consideration existing technology deployment, current security posture, and defense-in-depth strategies. Suggestions are designed to move you along a path toward recognized best practices.