Friday, February 27, 2009

Free Tool to read SQL Server 2005 / 2008 Transaction Log files

I'm reading: Free Tool to read SQL Server 2005 / 2008 Transaction Log filesTweet this !
Say you need to provide a solution to log and show all Create, read, update and delete (CRUD) transactions for auditing, this is more a mandatory for SOX & HIPPA regulations.

Using the standard SQL Server methods which is an undocumented statement DBCC LOGINFO will get information, but the problem is it cannot be interpreted in simple terms as per the requirement. Hence the rise of third party tools in this area is evident. In order to enable to read SQL Server transaction log files and provide an in-house tool to show the CRUD transactions on a set of databases in a customized manner is a demanding task for a DBA (now a days). The other aspect is to use Server side trace (Profiler) to track the statements on the database (which will impact the performance if the server is already stressed out for resources)

As per the note above using undocumented DBCC command you will get:
DBCC LOG([,{01234}])

  • 0 - Basic Log Information (default)
  • 1 - Lengthy Info
  • 2 - Very Length Info
  • 3 - Detailed
  • 4 - Full
DBCC log (, 4)

And it displays the following transaction log information:
  • Current LSN
  • Operation (string starts with LOP_XXXXX)
  • Context (string starts with LCX_XXXX)
  • Transaction ID
  • Tag Bits (Hex)
  • Log Record Fixed Length (int)
  • Log Record Length (int)
  • Previous LSN
  • Flag Bits (Hex)
  • Description (string)
  • Log Record (Hex)
As it is undocumented on the Microsoft articles its very hard to interpret what lies beneath those HEX values, therefore using third party tools such as APEX-SQL Log Reader and Lumigent's Log Explorer are more helpful to such users that are needed to cater the management needs. But these all tools comes for a heavy price costing dollars in four digits.

Now comes the climax of the entire article, a tool is available which can read and display contents of transaction log files in a very informative manner. The tool is called Internals Viewer for SQL Server. This tool has three main features / sub-tools: Allocation Map, Page Viewer, and Transaction Log Viewer. Also this tool integrates with SSMS and works for both SQL Server 2005 and SQL Server 2008. This tool might not be as capable as the ones listed above, but the functionality it provides that too for free is unmatched with any other tools in my knowledge.

This tool can be a very handy and economic debugger in case of a SSIS Load package failure, especially in a parent-child load package architecture. In such an architecture, tens / hunderds of SSIS child packages are executed SSIS by parent / master packages, and the transaction handling becomes so complex, that architects carelessly / helplessly amends point-in-time database recovery as the failure recovery option or end up buying one of the tools mentioned above to rollback specific transactions to recover the damaged relational datawarehouse. This also give a good hint / example of how important transaction handling remains in an ETL / SSIS project.

As per the documentation of this tool, it offers the below mentioned features:

Integration with SSMS (SQL Server Management Studio) 2005 and 2008:

  • The application is installed as a SSMS add-in
  • Internals information integrated into the Object Explorer
  • Transaction Log viewer integrated into the Query Results

Allocation Map:

  • Displays the physical layout of tables and indexes
  • Displays PFS status
  • Overlay pages in the Buffer Pool

Page Viewer:

  • Displays Data pages including forwarding records and sparse columns
  • Displays Index pages
  • Displays allocation pages (IAM, GAM, SGAM, DCM, and BCM pages)
  • Displays pages with SQL Server 2008 row and page compression

Below are some of the screen-prints of this tool:


Thursday, February 26, 2009

How to create equivalent of Virtual Cube in SSAS 2005

I'm reading: How to create equivalent of Virtual Cube in SSAS 2005Tweet this !

I came across a query from one of my blog readers (Sam) regarding an interesting topic: How to create equivalent of virtual cube (SSAS 2000) in SSAS 2005 without using migration wizard.

The answer to this lies in the Post-Migration considerations from SSAS 2000 to SSAS 2005 using Analysis Services Migration Wizard:

1) Every SSAS 2000 cube will be migrated as a cube in SSAS 2005. Any virtual cubes would be changed to cubes, with linked measure groups to the other cubes.

2) The new cubes will contain aggregations but will not be processed, so the user tools will not be able to see the SSAS 2005 database.

3) If your SSAS 2000 database contained linked objects, remote objects (for cubes, dimensions or partitions), or has drill-through enabled, those features of the cube will not be migrated.

4) Top-level dimension security is not supported in SSAS 2005, meaning that you cannot allow or disallow access to an entire dimension. If dimension security was applied for an entire dimension in SSAS 2000, you need to re-consider your security architecture in SSAS 2005.

5) Dimension and Hierarchies might be renamed during migration to prevent duplicate names and to consolidate dimensions.

So to create equivalent of virtual cube in SSAS 2005 without using the migration wizard, there are two ways of accomplishing the same:

1) Either create a new cube, add linked objects and create different perspectives for each virtual cube.

2) Another option is use the cube with major number of objects, add linked objects and create a perspective within it for the virtual cube.

Wednesday, February 25, 2009

SSIS Performance Optimization : Reduce time to load data into a SQL Server relational data warehouse without modifying packages

I'm reading: SSIS Performance Optimization : Reduce time to load data into a SQL Server relational data warehouse without modifying packagesTweet this !
In SSIS, generally the focus remains to develop the packages as efficiently as possible using the most known or practiced best practices. But less attention is given towards the DB structure comparatively which can indirectly reduce the time of execution of load packages.

During the Performance optimization / Load testing phase, when the limits of SSIS package optimization reaches it's edge; then comes to rescue the optimizations that can be done at the database level.

I have been evident of development environments, where the relational data warehouse contains all the facts and dimensions with all foreign-key constraints enabled along with whatever indexes created to boost the read operations for SSAS during cube processing.

A typical ETL cycle goes as follows: Extract - Maintenance (Backup / Archive / Stage) - Transform - Maintenance (Backup / Archive / Stage) - Load - Maintenace (Backup / Archive / Stage). Prior to the load phase, for eg. if the indexes are disabled instead of dropping then there are two advantages:

1) By disabling the indexes prior to the load, you avoid all the overhead required to maintain the indexes.
2) By using the disable/enable method, you do not have to maintain scripts to recreate the indexes following the data load.

An index can be disabled by using the ALTER INDEX statement as follows:
ALTER INDEX { index_name ALL } ON objectname

To enable an index, it must be rebuilt to regenerate and populate the B-tree structure. You can accomplish this by using the following command:

ALTER INDEX { index_name ALL } ON objectname

MS BI ( SSIS / SSAS / SSRS ) related Hotfix in Cumulative Update Package 12 for SQL Server 2005 Service Pack 2 and Service Pack 3 Bug Fix List

I'm reading: MS BI ( SSIS / SSAS / SSRS ) related Hotfix in Cumulative Update Package 12 for SQL Server 2005 Service Pack 2 and Service Pack 3 Bug Fix ListTweet this !
Microsoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 2 hotfix to an installation of SQL Server 2005 Service Pack 2. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

Cumulative Update 12 for SQL Server 2005 Service Pack 2 contains hotfixes for SQL Server 2005 Service Pack 2 issues that have been fixed since the release of SQL Server 2005 Service Pack 2.

KB Article



FIX: The values of some SQL Server 2005 SSIS pipeline counters are always 0 when you try to view them in a terminal session.


FIX: An empty result set is returned when you run an SSIS package that contains an Execute SQL Task in SQL Server 2005 Service Pack 2.


FIX: Error message when you export a report to a PDF file in SQL Server 2005 Reporting Services: "Non-negative number required"


FIX: Error message when you synchronize a database in SQL Server 2005 Analysis Services: "Errors in the metadata manager. The dimension with ID of '', Name of '' referenced by the '' cube, does not exist"

Review the entire list of hotfixes available in Cumulative Update Package 12 for SQL Server 2005 Service Pack 2 for more detail.

Those who wish to Download Cumulative Update Package 12 for SQL Server 2005 Service Pack 2 and Install these hotfixes, please read this excerpt from an article (above link refers to this article): "However, it is intended to correct only the problems that are described in this article. Apply it only to systems that are experiencing these specific problems. This cumulative update package may receive additional testing. Therefore, if you are not severely affected by any of these problems, we recommend that you wait for the next SQL Server 2005 service pack that contains the hotfixes in this cumulative update package. "

Download Microsoft SQL Server 2005 Service Pack 3

List of Bugs that are fixed in SQL Server 2005 Service Pack 3

Monday, February 23, 2009

EvaluateAsExpression property of variables in SSIS

I'm reading: EvaluateAsExpression property of variables in SSISTweet this !
Once a variable is created in a package, it can be seen in the variables window but it's properties are not visible in the properties pane instantly. It's a kind of bug with BIDS, the package needs to be closed and re-opened again and only after that the property pane lists the variable. Select the variable in the property pane and of all the available properties, there would be two properties named "EvaluateAsExpression" and "Expression" property.

These properties are some on the most under-used or under-rated gems in SSIS as per my views. "EvaluateAsExpression" make possible what I call, variable inside a variable. In a real time implementation, I have seen that extensive script tasks were used in order to get the same output which can be achieved by the correct use of both of these properties.

In your top-level or the controller packages, variables should be created which reads value from the configuration files and, inside child packages, new values from these values can be deduced using these properties instead of using script tasks. Also this would centralize the logic and would leave less margin for errors and reduce maintenance overhead.

Eg: Consider two packages, one is a master package and one is a child package. I define three variables: ustrDB, ustrServer, ustrTable; in my master package. Consider the child package is configured with parent package configuration package setting. Say in child packages I am using two source connection adapters, one of OLE DB and other of ADO.Net type. To form a connection string, we just need two variables, form an expression concatenating the drivers and the three variables available from the parent package. Set this expression in the "Expression" property just as you would concatenate a string and before that set the "EvaluateAsExpression" property to true.

In case if these properties are not used, usage of Script Task becomes inevitable. I have worked with datawarehousing projects having more that 50-60 variables in the controller/parent package and more that 40 child packages using at least half of these variables. Imagine the duplication of code in all the packages, maintenance overhead and issues while debugging the same. To add to it, once you migrate this to 64-bit environment and the nuke is ON as Script task has many issues in 64-bit environment.

Use these properties once and you would be a fan of it as I am ! More documentation on this topic can be found on BOL.

Saturday, February 21, 2009

Download Mastering SQL Server 2005 Profiler Ebook Free

I'm reading: Download Mastering SQL Server 2005 Profiler Ebook FreeTweet this !
One more Ebook available for free download.

Download Ebook

Mastering SQL Server 2005 Profiler
By Brad McGehee

Friday, February 20, 2009

Feature Consideration for SQL Server / SSIS project estimation

I'm reading: Feature Consideration for SQL Server / SSIS project estimationTweet this !
In an SSIS project, before starting low-level implementation or design, one thing that needs to be put on the checklist is the feature-list available in the procured/to-be procured version of SQL Server vs the actual implementation requirements.

Express, WorkGroup, Web Edition for SQL Server 2005 were almost not equipped at all with SSIS. With SQL Server 2008 Express, Express Advanced, WorkGroup and Web edition, Micorosoft has demonstrated some generosity and these editions now contain some basic SSIS capabilites.

Standard Edition contains BI studio and restricted set of transformation and tasks. So don't be under the impression that if you have the standard edition, you have entire SSIS capabilites at your disposal. Still I would say that many mid-scale projects would suffice with the set of features available with the standard version.

For summary, the tasks / transforms that are available only with Enterprise Edition of SQL Server 2005 / 2008 in general are as mentioned below:
  • Data Mining Training Destination
  • Data Mining Query Component
  • Fuzzy Grouping
  • Fuzzy Lookup
  • Term Extraction
  • Term Lookup
  • Dimension Processing Destination
  • Partition Processing Destination
  • Data Mining Query Task

Refer to the links below for a full list of feature set supported by SQL Server 2005 and SQL Server 2008.

SQL Server 2005 Features Comparison by Editions

SQL Server 2008 Features Comparison by Editions

Why and How to create a Filtered Index in SQL Server 2008

I'm reading: Why and How to create a Filtered Index in SQL Server 2008Tweet this !
When an index is built, every value in the index key is loaded into the index. In effect, each index is a mini-table containing all the values corresponding to just the columns in the index key. Therefore, it is possible for a query to be entirely satisfied by using the data in the index. An index that is constructed such that SQL Server can completely satisfy queries by reading only the index is called a covering index.

When an index is created, SQL Server generates a structure called a histogram that stores information about the relative distribution of data values within a column. The degree to which values in the column allow you to locate small sets of data is referred to as the selectivity of the index. As the number of unique values within a column increases, the selectivity of an index increases. The query optimizer chooses the most selective indexes to satisfy a query because a highly selective index allows the query processor to eliminate a very large portion of the table so as to access the least amount of data necessary to satisfy your query. Indexes with low selectivity and a low percentage of unique values are not considered by the query optimizer, but they still incur an overhead for write operations.

To handle the cases where significant skew exists in the data, SQL Server 2008 allows you to create filtered indexes. A filtered index is simply an index with a WHERE clause. Only the index keys matching the WHERE clause are added to the index, allowing you to build indexes that focus on the highly selective portions of the table while allowing SQL Server to choose another method for the less selective range.

Filtered indexes have the following restrictions:

  • They must be a nonclustered index.
  • They cannot be created on computed columns.
  • Columns cannot undergo implicit or explicit data type conversion.


ON Person.Address(City)
INCLUDE (AddressLine1, AddressLine2)

After going through many articles on the web, I found one article which I think is the best article on Filtered Indexes in SQL Server 2008. This article is a blog post by Randy Dyess from Solid Quality Mentors.

Thursday, February 19, 2009

Download Microsoft SQL Server 2008 Books Online (January 2009)

I'm reading: Download Microsoft SQL Server 2008 Books Online (January 2009)Tweet this !
SQL Server 2008, the latest release of Microsoft SQL Server, provides a comprehensive data platform. Books Online is the primary documentation for SQL Server 2008. Books Online includes the following types of information:
  1. Setup and upgrade instructions.
  2. Information about new features and backward compatibility.
  3. Conceptual descriptions of the technologies and features in SQL Server 2008.
  4. Procedural topics describing how to use the various features in SQL Server 2008.
  5. Tutorials that guide you through common tasks.
  6. Reference documentation for the graphical tools, command prompt utilities, programming languages, and application programming interfaces (APIs) that are supported by SQL Server 2008.
  7. Descriptions of the sample databases and applications that are available with SQL Server 2008.

Download Microsoft SQL Server 2008 Books Online (January 2009)

Performance Monitor Counters commonly used for SQL Server 2000 / 2005 / 2008 performance analysis / measurement / monitoring

I'm reading: Performance Monitor Counters commonly used for SQL Server 2000 / 2005 / 2008 performance analysis / measurement / monitoringTweet this !
A consolidated list of performance counters that can be classified for use with SQL Server 2000 / SQL Server 2005 / SQL Server 2008 is as below:

Network Interface
Paging File
SQL Server:Access Methods
SQL Server:Backup Device
SQL Server:Broker Activation
SQL Server:Broker Statistics
SQL Server:Broker TO Statistics
SQL Server:Broker /DBM Transport
SQL Server:Buffer Manager
SQL Server:Buffer Node
SQL Server:Buffer Partition
SQL Server:Cache Manager
SQL Server:Catalog Metadata
SQL Server:CLR
SQL Server:Cursor Manager by Type
SQL Server:Cursor Manager Total
SQL Server:Database Mirroring
SQL Server:Databases
SQL Deprecated Features
SQL Server:Exec Statistics
SQL Server:General Statistics
SQL Server:Latches
SQL Server:Locks
SQL Server:Memory Manager
SQL Server:Plan Cache
SQL Server:Replication Agents
SQL Server:Replication Dist.
SQL Server:Replication LogReader
SQL Server:Replication Merge
SQL Server:Replication Snapshot
SQL Server:Resource Pool
SQL Server:SQL Errors
SQL Server:SQL Statistics
SQL Server:SSIS Service
SQL Server:Transactions
SQL Server:User Settable
SQL Server:Wait Statistics
SQL Server:Workload Group Stats

Special SQL Server 2008 Performance Counters:

SQL Server Databases: Tracked transactions/sec - Number of committed transactions recorded in the commit table for the database.

SQL Server Databases: Write Transactions/sec - Number of transactions which wrote to the database in the last second.

SQL Server Buffer Node: Remote node page lookups/sec - Number of lookup requests from this node which were satisfied from other nodes.

SQL Server Buffer Node: Local node page lookups/sec - Number of lookup requests from this node which were satisfied from this node.

SQL Server General Statistics: Connection reset/sec - Total number of connection resets per second.

SQL Server General Statistics: Tempdb recovery unit id unit - Number of duplicate tempdb recovery unit id generated

SQL Server General Statistics: Tempdb rowset id - Number of duplicate tempdb rowset id generated

SQL Server SQL Statistics: Misguided plan executions/sec - Number of plan executions per second in which a plan guide could not be honored during plan generation

SQL Server SQL Statistics: Guided plan executions/sec - Number of plan executions per second in which the query plan has been generated by using a plan guide.

Deprecated Features: Usage – this is a really nice counter which lists feature usage since last SQL Server startup

References: BOL, Benjamin-Wright Jones Blog, Jimmy-May Blog

Wednesday, February 18, 2009

Microsoft Offer : Download SQL Server 2008 MS Press E-book Free

I'm reading: Microsoft Offer : Download SQL Server 2008 MS Press E-book FreeTweet this !
Introducing Microsoft SQL Server 2008
by Peter DeBetta, Greg Low, and Mark Whitehorn

ISBN: 9780735625587

Download EBook
Learn about major new features in SQL Server 2008 including security, administration, and performance.

Introducing Microsoft SQL Server 2008:

Chapter 1: Security and Administration

Chapter 2: Performance

Chapter 3: Type System

Chapter 4: Programmability

Chapter 5: Storage

Chapter 6: Enhancements for High Availability

Chapter 7: Business Intelligence Enhancements

Tuesday, February 17, 2009

Change Tracking in SQL Server 2008

I'm reading: Change Tracking in SQL Server 2008Tweet this !
SQL Server 2008 introduces a new feature implemented through the CHANGE_TRACKING database option. Change tracking is a lightweight mechanism that associates a version with each row in a table that has been enabled for change tracking. Each time the row is changed, the version number is incremented. Instead of building systems to avoid changes from multiple users overriding each other, applications need only compare the row version to determine if a change has occurred to the row between when the row was read and written.

After change tracking has been enabled for the database, you can choose which tables within a database that change tracking information should be captured for. Over time, change tracking information accumulates in the database, so you can also specify how long tracking information is retained through the CHANGE_RETENTION option and whether tracking information should be automatically cleaned up with the AUTO_CLEANUP option.

A nice explanation on this topic can be found on this screencast by Mike Taulty.

For an exhaustive explanation on this topic, check it out on BOL.

Monday, February 16, 2009

Developing SSIS packages for 64-bit Windows on 32-bit Windows

I'm reading: Developing SSIS packages for 64-bit Windows on 32-bit WindowsTweet this !
64-bit Flavors: x86-64 and IA-64

In computing, IA-64 (short for Intel Architecture-64) is a 64-bit processor architecture developed cooperatively by Intel Corporation and Hewlett-Packard (HP), and implemented in the Itanium and Itanium 2 processors. The goal of IA-64 was to produce a "post-RISC era" architecture that would address some of the key challenges faced by older architectures, to enable more efficient performance scaling in future processor designs.

AMD's AMD64 architecture, first delivered in 2003, found success in the marketplace, the Itanium was delayed, and Intel developed and announced the EMT64 architecture, which is very similar to AMD64. Both architectures are 64-bit upgrades to the x86 architecture, and the two are often referred to generically as the "x86-64 architecture". In 2006, Intel delivered x86-64 processors based on the Intel Core microarchitecture.

What is WOW64?

Windows XP x64 Edition uses a technology named WOW64, which permits the execution of 32-bit x86 applications. It was first employed in Windows XP 64-bit Edition (for the IA-64 (Itanium)), but then reused for the “x64 Editions” of Windows XP and Windows Server 2003.

Since the X86-64 architecture includes hardware-level support for 32-bit instructions, WOW64 simply switches the process between 32- and 64-bit modes. As a result, X86-64 architecture microprocessors suffer no performance loss when executing 32-bit Windows applications. On the IA-64 architecture, WOW64 was required to translate 32-bit x86 instructions into their 64-bit IA-64 equivalents—which in some cases were implemented in quite different ways—so that the processor could execute them.

Designing Integration Services Packages on 64-bit Computers

You cannot design packages in BI Development Studio or debug scripts in the Script task on Itanium-based operating systems.

When you run a package in 64-bit mode, you might not be able to connect to as many data sources as you can when you run a package in 32-bit mode. Some .NET Framework Data Providers and native OLE DB providers might not be available in 64-bit versions. For example, the Microsoft OLE DB Provider for Jet, which connects to Access databases and Excel spreadsheets, is not available in a 64-bit version.

The 32-bit SSIS Designer displays only 32-bit providers that are installed on the local computer. To configure a connection manager to use a specific 64-bit provider, you must install the 32-bit version of the provider on the development computer for use at design time. Even though the 32-bit version of the provider is installed, you can still run the package in 64-bit mode, both in the development environment and after deployment. The 32-bit and 64-bit versions of a provider have the same ID. Therefore, the SSIS runtime will select the appropriate version of the provider to use. For example, you run the package in the development environment on a 64-bit computer. By default, the package uses the 64-bit version of the provider because the default value of the Run64BitRuntime project property is True.

You cannot run packages that contain scripts—for example, in Script tasks or Script components—in 64-bit mode unless the scripts have been precompiled. By default, the value of the PreCompile property is True for both the Script task and the Script component.

You cannot use the Execute DTS 2000 Package task in a package running in 64-bit mode. The SQL Server 2000 Data Transformation Services runtime is not available in a 64-bit version.

Selecting 32-bit or 64-bit Package Execution in a SQL Server Agent Job

When you configure a SQL Server Agent job with a step of type SQL Server Integration Services Package, the job always runs the package in 64-bit mode on a 64-bit computer, because this step type invokes the 64-bit version of dtexec.exe to run the package. This assumes that you have installed and are running the 64-bit version of SQL Server 2005 and SQL Server Agent on the 64-bit computer. If you have installed and are running the 32-bit version of SQL Server 2005 and SQL Server Agent on the 64-bit computer, this step type invokes the 32-bit version of dtexec.exe to run the package.

If you want to run a package from a 64-bit SQL Server Agent job in 32-bit mode, select a job step type of Operating system, and enter a command line or use a batch file that invokes the 32-bit version of dtexec.exe. You can use the dtexecui.exe utility to create the command line, and then copy and paste the command line into the job step.

Entire list of 64-bit considerations for SQL Server Integration Services can be found on this post of Dougbert's Blog.
References: Wiki, Dougbert's Blog, MSDN BOL

No Difference between SSIS 2005 Architecture and SSIS 2008 Architecture

I'm reading: No Difference between SSIS 2005 Architecture and SSIS 2008 ArchitectureTweet this !
SSIS 2005 Architecture

SSIS 2008 Architecture

Image Courtsey: MSDN

Saturday, February 14, 2009

Data warehouse articles authored by Ralph Kimball and Kimball Group

I'm reading: Data warehouse articles authored by Ralph Kimball and Kimball GroupTweet this !
Language, culture, and country-by-country compliance and privacy requirements are just a few of the tough data quality problems global organizations must solve. Start by addressing data accuracy at the source and adopting an MDM strategy, then follow these six other best-practice approaches.
By Ralph Kimball—August 1, 2008

With new compression, partitioning and star schema optimization features, Microsoft's SQL Server 2008 is catching up with the state of the industry in data warehousing. Here's why these three capabilities are crucial for scalability and performance on any platform.
By Warren Thornthwaite—June 23, 2008

To deliver better intelligence, BI and data warehousing teams need business acumen, interpersonal skills and communication competencies. Here are helpful tips and 12 invaluable resources for career development and success.
By Warren Thornthwaite—May 11, 2008

You can still hand-code an extract, transform and load system, but in most cases the self-documentation, structured development path and extensibility of an ETL tool is well worth the cost. Here's a close look at the pros and cons of buying rather than building.
By Joy Mundy—April 06, 2008

What satisfies, or doesn't satisfy, the customer? Use one of these three powerful data warehouse design approaches to gauge satisfaction and help marketers tease out the customer experience behind various behaviors.
By Ralph Kimball—February 4, 2008

How do you cope with an executive's request to "bring back a time series of activity for all subscribers who were in platinum status as of X date," or "show me a time series of orders by sales region according to the sales organization as of Y"? Here's how data warehouse pros can cope with the common requirement to look back in time.
By Joy Mundy—December 9, 2007

These 34 subsystems cover the crucial extract, transform and load architecture components required in almost every dimensional data warehouse environment. Understanding the breadth of requirements is the first step to putting an effective architecture in place.
by Bob Becker—October 21, 2007

Data warehousing and business intelligence success cannot be taken for granted. You must create an ongoing education and communication program to maintain your success and extend it across the organization.
by Warren Thornthwaite—August 27, 2007

When developing fact tables, aggregated data is NOT the place to start. To avoid "mixed granularity" woes including bad and overlapping data, stick to rich, expressive, atomic-level data that's closely connected to the original source and collection process.
by Ralph Kimball—July 30, 2007

How do you cope with "abused users, overbooked users, comatose users, clueless users" and "know-it-all users" during the requirements gathering stage of a data warehouse/BI project?
by Margy Ross—June 1, 2007

The choice between deploying relational tables or OLAP cubes is not a trivial matter. Weigh these 34 pros and cons of each approach early in the design of your extract-transform-load system.
by Ralph Kimball—April 27, 2007

Best practices are precision tools that should be wielded precisely and skillfully. This article describes five best practices drawn from the Kimball Method that often are described incorrectly.
by Bob Becker and Ralph Kimball—March 26, 2007

It's time to migrate master data management upstream to an integration hub or, ideally, an enterprise MDM system. And if you have yet to do anything about data consistency, take these four steps toward integration and stewardship.
by Warren Thornthwaite—February 7, 2007

Off-the-shelf apps may offer built-in analytics, but the best approach to supporting operational decisions is to rely on a solid data warehouse that cleans, integrates.
by Joy Mundy—December 1, 2006

DW/BI professionals are often tasked with making evolutionary upgrades and improvements to minimize cost and upheaval in the current analytic environment. We explore four upgrades that can breathe new life into legacy data warehouses.
by Margy Ross—October 1, 2006

These step-by-step guidelines will help dimension managers and users drill across disparate databases.
by Ralph Kimball—August 1, 2006

Data stewards are the liaisons between business users and the data warehouse team, and they ensure consistent, accurate, well-documented and timely insight on resources and requirements.
by Bob Becker—June 1, 2006

How to build, test and deploy standard reports for key business processes.
by Warren Thornthwaite—April 1, 2006

How to plan, prioritize and design the primary vehicle for delivering business intelligence.
by Warren Thornthwaite & Joy Mundy—February 1, 2006

This Swiss Army knife for BI and data warehousing supports planning, integration and stewardship
by Margy Ross—December 1, 2005
Are you missing potential business opportunities because you're not exploring your data?
by Warren Thornthwaite—October 1, 2005

Is that sales pitch flying in the face of conventional wisdom? Start asking questions now.
by Ralph Kimball—September 1, 2005

Be sure to develop a DW/BI operations plan before deployment
by Joy Mundy—July 1, 2005

Good listening and conversational skills will uncover hidden needs and 'shadow functions.'
by Margy Ross and Ralph Kimball—May 1, 2005

The three fundamental techniques for changing dimension attributes are just the beginning
by Margy Ross and Ralph Kimball—March 1, 2005

Three little letters—E,T, and L—obscure the reality of 38 subsystems vital to successful data warehousing.
by Ralph Kimball—December 4, 2004
Before designing an ETL system, you must first understand all of your business needs.
By Margy Ross, Ralph Kimball— November 13, 2004

Do you know the difference between dimensional modeling truth and fiction?
By Margy Ross & Ralph Kimball—October 16, 2004

Techniques for realigning your DW/BI environment to deliver better business value.
By Bob Becker, edited by Margy Ross—August 7, 2004

By Ralph Kimball & Margy Ross—June 12, 2004

Successful warehouses grow—get ready for the opportunities and obstacles
By Joy Mundy, edited by Margy Ross—May 1, 2004

Reaching past the dashboard hype for some clarity
by Stephen Few, edited by Margy Ross—March 20, 2004

Fundamental differences between the Bus Architecture and Corporate Information Factory
by Margy Ross & Ralph Kimball—March 6, 2004

Managing a data warehouse is similar to running a restaurant
by Margy Ross & Ralph Kimball—January 1, 2004

As with construction, you must plan before building the ETL.
by Warren Thornthwaite, Edited by Margy Ross—December 10, 2003

Use the five-stage analytic framework to deliver more from the data warehouse
by Bill Schmarzo, Edited By Margy Ross—November 18, 2003

Although there's no substitute for atomic details, look into complementary consolidations
by Margy Ross & Ralph Kimball—October 30, 2003

Use this checklist to review your dimensional models
by Margy Ross & Ralph Kimball—October 10, 2003

Our data-warehousing approach is sometimes referred to as bottom-up, but it's far from it
by Margy Ross & Ralph Kimball—September 17, 2003

Business sponsors can make or break a data warehouse program. What habits make or break a business sponsor?
by Margy Ross edited by Ralph Kimball—September 1, 2003

Dimensional design techniques bind events into stories
by Jim Stagnitto edited by Ralph Kimball—August 10, 2003

RFID tagging will create not just a tidal wave of data, but lifetime employment for data warehouse designers
by Ralph Kimball—July 18, 2003
Start by discarding your current concepts of ETL
by Neil Raden edited by Ralph Kimball—June 30, 2003

Take the idea of a real-time data warehouse with a grain of salt, then realize the possibilities
by Neil Raden edited by Ralph Kimball—June 17, 2003
Absolutely yes, or absolutely no, depending...
by Gary Nissen edited by Ralph Kimball—May 31, 2003

The conventional view of data warehouse total cost of ownership is myopic and wrong
by Ralph Kimball—May 13, 2003

The data warehouse takes a pledge to preserve history
by Ralph Kimball—April 22, 2003

Drilling across means asking for the same row headers from another fact table
by Ralph Kimball—April 5, 2003

Drilling down just means 'show me more detail'
by Ralph Kimball—March 20, 2003

It's the most important dimensional design step after identifying data sources
by Ralph Kimball—March 1, 2003

Contrary to conventional data warehouse wisdom, physical centralization is not the question
by Margy Ross—February 1, 2003

The logical foundation of dimensional modeling
by Ralph Kimball—January 1, 2003

Friday, February 13, 2009

Download Microsoft Business Intelligence VPC v7.1 / Release 7.1

I'm reading: Download Microsoft Business Intelligence VPC v7.1 / Release 7.1Tweet this !
For reference, below are the applications that are installed within MS BI VPC v6.0:

· Windows Server 2003 R2 Service Pack 2
· Internet Explorer 7
· Office Enterprise 2007
· Office Visio 2007
· SharePoint Portal Server 2007
· SQL Server 2005 Service Pack 2 (w/ SSIS, SSAS, SSRS)
· SQL Server 2005 Data Mining Add-ins
· SQL Server 2005 SharePoint Integration Add-in
· ProClarity Analytics Server 6.3
· ProClarity Dashboard Server 6.3
· ProClarity Desktop Professional 6.3
· ProClarity Web Professional 6.3
· PerformancePoint Planning Server 2007
· PerformancePoint Planning Add-In for Excel
· PerformancePoint Planning Business Modeler
· PerformancePoint Monitoring Server
· PerformancePoint Monitoring Scorecard Builder
· OfficeWriter for Word Plug-In
· OfficeWriter for Excel Plug-In
· Project REAL Reference ImplementationBusiness Intelligence Metadata Samples

MS BI VPC v7.1 has an expiration date of April 15, 2010. This VPC is using Windows 2003 Server R2 SP2 Enterprise Edition, PPS 2007 with SP1, PAS 6.3 with Hotfix 2215, and SQL Server 2005 Enterprise Edition with SP2. It also contains additional presenter scripts, samples, as well as service packs.

Below are the links to download this VPC. It is made up of 7 RAR files.


Thursday, February 12, 2009

MDX Tutorial : Generate MDX visually for SSAS using ProClarity

I'm reading: MDX Tutorial : Generate MDX visually for SSAS using ProClarityTweet this !
In OLAP / SSAS parlance, at least basic knowledge of MDX is a de-facto requirement. There are quite a few books published on SSAS flavor of MDX. It can be said that MDX is one of the best kept secrets in OLAP, as number of books and reference material available is scarce. Also understanding of Cube and developing the slicing query keeping N number of axis in mind is also not easy.

BIDS does a good job by providing MDX query editor with intellisense. Still my experience with Proclarity has been sweeter than with BIDS. Just try and use Proclarity Desktop Professional v6.3 for a visual experience of slicing and dicing Cube ( which is similar to Office Web Component in BIDS for browsing data ) and for generating MDX just by drag and drop.

Also Proclarity has some of the fascinating charts and bars which are still not available in Performancepoint Analytics module. Though Proclarity has been procured by Microsoft long back, but as per my views, it still stands taller where it comes to OLAP data analysis.

Visit the Tutorials link from the Community section for series of MDX articles and MDX tutorials on SSAS flavor of MDX.

Wednesday, February 11, 2009

SSRS : Automated deployment script using Report Server Script (.rss) and RS utility

I'm reading: SSRS : Automated deployment script using Report Server Script (.rss) and RS utilityTweet this !
An automated deployment utility for deploying SSRS reports is one of the basic needs for almost any reporting project. During the development phase, it starts with creating a new solution, adding it to version control followed by addition of individual reports. As the first delivery comes, the general deployment approach that is used is deploying using BIDS.

Coming back to business, RS.exe is the utility that ships with SSRS which can be used for creating an automated deployment script for deploying reports. The RS.exe tool references a Report Server Script (.rss) file, which contains Visual Basic.Net code based on the Web Service Description Language (WSDL) API.

Detailed information about RS Utility can be found from BOL.

Refer to the
Reporting Services Script Samples, for a look into code and object model for creating .rss files for commonly required management and deployment functions.

Tuesday, February 10, 2009

SSIS Knowledge Base articles : How to in SSIS

I'm reading: SSIS Knowledge Base articles : How to in SSISTweet this !
SQL Server Integration Services ( SSIS ) Knowledge Base articles

To find one of these articles online, just type in the article number at

In some cases, the title listed here may not match the final title of the published article. The articles are listed here in descending order by article number, which should roughly correspond to "most recent first".

SQL Server 2008 Integration Services

KB Article



FIX: DataProfileViewer cannot be started successfully in SQL Server 2008


getKB - SSIS: DataProfileViewer: Unhandled Exception when click "View Single Column Profile By Column" button.


Missing buttons in Chinese (Taiwan) Execute Package Utility


When you migrate a script task created by using Release Candidate 0 version or Community Technology Preview of SQL Server 2008, a reference to the SQL Server Integration Services 2005 may be added to Microsoft.SqlServer.ScriptTask assembly


Visual Studio 2008 Configuration Settings


SSIS Data Mining Model Training Destination Cannot Persist Edits


How to share a package configuration file across multiple packages in SQL Server 2008 Integration Services


SSIS package fails with error DTS_E_CANTCREATESPOOLFILE


SSIS package crashes if VSTA is uninstalled


ActiveX Script tasks in an SSIS package do not run after you uninstall SQL Server 2005 from a computer that has SQL Server 2008 installed


Parsing delimited flat files with variable number of columns


SSIS Data Profile Viewer can be run on 64bit machine


Cannot successfully migrate script tasks or components on IA64


SSIS Scripts can fail to build on machines without Windows SDK


Import/Export Data Wizard mis-identifies types of columns when source is SQL Server via OLEDB and SQL query is used


SSIS Packages with scripts cannot be upgraded on IA64


SSIS perf counter lost during OS upgrade (from Win2K3 to LH)


Commit size in OLE DB dest is changed from 0 to MAX_INT (2147483647)


Yukon tools (BIDS, wizards) doesn't work in Upgrade scenario


Creating Integration Services Connection Project on Workgroup/Web SKU leads to an exception


SSIS Dataflow imports only 1 row from Sybase


Shared components might block SQL Server 2000 upgrade to SQL Server 2008


Packages with Scripts May Not Run on Windows Server 2003 for Itanium-Based Systems


SQL Server 2005 Packages Fail on Windows Server 2003 for Itanium-Based Systems


Dts object is no longer global in SQL 2008 Integration Services Script Tasks


OleDb source adapter may receive only 1 row with Sybase Ole Db Providers

SQL Server 2005 Integration Services

KB Article



007482 Legacy DTS Designer fails to open on Vista or Longhorn


getKB - [SQL 2005 CU11]: SQL 2005 Management Studio Job Step GUI omits SSIS Data Sources


getKB - SQL 2005 SSIS Web Service Task times out after 100 seconds


getKB - Re-mapping dialong not shown if the input for a multi-input component changes.


getKB - SSIS 2005 - StackOverflowException with DTEXEC.exe on a x64 platform


getKB - In case of error, SSIS duplicates data in flat file destination


OutOfMemory exception when saving complex SSIS packages from Business Intelligence Design Studio


getKB - SSIS Crash : Heap Corruption in TxAgg!CAggregate


getKB - Parallel executing and shared lookup task performs invalid lookup reference


getKB - Package Fails When Run Rrom Windows Service with Error: Variable "System::LocaleID" is already on the read list


getKB - Incorrect Data type causes truncation in 64 bit SSIS


getKB - SSIS Child Packages Using Cached Script Task


getKB - SQL Server 2005 - Transfer SQL Server Objects Task can drop logins from source server


getKB - SQL Server Integration Service Flat File Source hangs upon error row after installing SQL Cumulative Update #2


getKB - Fix: SQL 2005 Integration Services Flat File Source causes unexpected conversion errors after CU#2 build 3175 hotfix is installed


getKB - Poor plan choice causing Performance Issues on SQL 2005 as compared to 2000


DML operations against Fuzzy Lookup Reference Table may result in Error 6549 while executing "sp_FuzzyLookupTableMaintenanceInvoke"


getKB - DTExec hangs while exporting to a flat file on 64 bit version


getKB - Race condition between dtexec.exe and msdtssrvr.exe, intermittently cannot enumerate connection managers


How to tell which version of SQL 2005 Integration Services (SSIS) installed


SQL Server 2005 Integration Services - Information on clustering SSIS


FIX: SSIS Package may fail intermittently with exception access violation in "dtspipeline.dll"


PRB: The SQL Server Integration Services Pipeline Performance Counters are Missing on a 64 Bit Machine.


Configure registry keys for SQLDumper to generate a full dump of SSIS


getKB - Events generated by a child package but logged in a parent package no longer has a "User:" prefix


Connecting to SSIS service from non-administrator accounts


SSIS FTP Task may fail to delete remote files on UNIX FTP server


Post SQL 2005 SP2 installation, Script Componenets in SSIS packages may fail to be edited


getKB - When importing data from a flat file using SQL Server 2005 Integration Services (SSIS), you may receive the error "Warning: 0x8020200F at Data Flow Task, Flat File Source [1]: There is a partial row at the end of the file."


SSIS Packages may fail with an error if it contains Script Component that uses VariableDispenser class


FuzzyLookup transformation fails after you install SQL Server 2005 Service Pack 2


How To create 32-bit SSIS jobs on 64-bit servers


INF: SSIS Package fails to execute as a scheduled job using a SQL Agent proxy


SQL Server Integration Services 2005 (SSIS) Script Task and Script Component may need to be recompiled after installing.Net security fixes


getKB - Hotfix Request: Aggregate component divides the aggregations into several rows.


Installation of SSIS Only On a SQL 2000 Server Causes Existing DTS Packages W/ ActiveX Scripting to Fail


Fix: SQL 2005 SSIS package Script tasks and Script Components get executed in the wrong order when nested in child packages which are copied and pasted.


.NET 1.0/1.1 application that uses MSXML fails after installing SQL Server 2005


BUG: SSIS Package With Merge Join Transformation Hangs and Does Not Complete


Unable to Connect to SSIS in MSSMS after ONLY applying the SSIS QFE2152 in X64 machine


SSIS Package Generates Exception From Concurrent Execution of Lookup Data Flow Tasks


Character Data that Contains Embedded Binary Zero is Truncated During SSIS Transfer


Script Task Editor Cannot Debug Referenced Class Libraries


getKB - SSIS Package fails with message "Package <Package Name> has been cancelled"


ContentIdea - SSIS Access Violation when using a Merge Join data flow component, when the destination experiences an error


SQL 2005 Management Studio ends unexpectedly when editing a legacy DTS package and using the Build Query button to open the Data Transformation Services Query Designer


getKB - Error when adding a Configuration to a Non-Solution dtsx Package File


getKB - Memory Leak in MsDtsSrvr.exe


getKB - SQL Server Integration Services service (MsDtsSrvr.exe) leaks memory on x64 and IA64 platforms


DTS 2000 packages fail after upgrading all SQL 2000 instances to SQL 2005


SSIS Package fails when executing via SQL Server Agent Job Step but succeeds outside of SQL Server Agent


getKB - Project REAL -- Variable locking error when package is running without debugger. - sql hotfix 50000154


Package fails when user locale is different than original package creation


Unable to open DTS Packages saved from SQL2K on Win2K computers


Expression on components inside a Dataflow task gets orphaned if the component is renamed


getKB - Connection manager sometimes fails with 0x8004D00A ("Unable to enlist in the transaction.")


Third-party apps can break SSIS component (Task/Connection/etc) object enumeration


Fail to create a deployment manifest if 2 packages in the project share the same configuration file


getKB - Memory Leak with Fuzzy Grouping\Fuzzy Lookup - sql hotfix 65


'Invalid class string' error when attempting to execute, import, or migrate a DTS 2000 package which contains an instance of the Analysis Services Processing Task


Installing SQL Server 2005 Redist package DTS fails on Windows 2000 Advanced Server Service Pack 4 if MDAC is of version less than 2.6


Script for instrumenting the flow-rate of rows through the pipeline


SSIS toolbox items show with label text in the wrong language.


Unable to export a table with the SSIS Import\Export Wizard when the table name contains special characters


SSIS package fails with message about insufficient product level


SQLOLEDB provider to return end of rowset instead of an error when an error is encountered on the server side


Unable to create a table with the SSIS Import Export wizard when the table names contain characters such as * / \ ?


Unable to Export Database when the database name contains Special Characters


How to add an incremental counter field to an SSIS data fow


Creating package templates to re-use common objects and settings such as Connection Managers, and Log Providers


Implications of duplicate package ID and the use of DTUTIL.exe to re-generate Package IDs before deploying to your production environment


Behavior and lableing for log entries posted by a Child Package


Considering Package Protection Level as part of your package deployment plan\best practices, and Batch editing with DTUTIL.exe


Sample Script to post row counts to the windows event log


How to create a dynamic subjet or message for a Send Mail Task


Message field truncated to 128 characters in .trc file created by SQL Profiler log provider


Error message: Error loading [your package name]: Failed to decrypt


Whenever I restart the SQL Server Integration Services Windows Service, currently executing packages are stopped?


HOW TO: Ensure Support for DTS Repository Packages after Upgrading to SQL Server 2005.

Related Posts with Thumbnails