BI/BPM - The SeeQueL

Performance tips for SSAS | James Serra's Blog

2012-11-29T07:03:00.001-08:00

Performance tips for SSAS | James Serra's Blog: "Over the past few years I have developed a list of ways to improve performance in SSAS and in two of the tools that use it: ProClarity and PerformancePoint. Below are those performance tips along with other important items to be aware of:"

The one that really helped us was to move parameters from the where clause to a subquery. This can improve performance drastically.

'via Blog this'

Ranting about Cumulative Updates

2012-11-29T05:31:00.001-08:00

Don't get me wrong. Fix it fast and fix it often is a good motto to have when addressing software bugs. However, trying to decode what was changed between versions isn't so easy. Especially with products like Microsoft Sharepoint.

Sharepoint has had umpteen patches since Service Pack 1. Each one is outlined in separate KB articles by install file. This works out to about 100 tabs open in the browser to figure out what is in each cumulative update.

Is there not a database that has this stuff?

On another note, SQL 2012 SP1 is released, which doesn't include the fixes contained in CU3 & CU4, so download SQL 2012 SP1 and SQL 2012 SP1 CU1.

Big Data Vendor Landscape

2012-11-12T12:58:00.002-08:00

A very comprehensive post on the current (well, June anyway) picture of Big Data Vendors along with plenty of contact information.

http://www.rosebt.com/1/post/2012/06/big-data-vendor-landscape.html

Vertica is really eating everyone else's pie. They partner with many of the traditional front-end BI app developers, including Cognos, Microstrategy and Tableau.

http://www.vertica.com/partners/business-intelligence/

sql server 2008 - Table Driven WHERE Clause Using LIKE - Stack Overflow

2012-11-02T06:08:00.001-07:00

sql server 2008 - Table Driven WHERE Clause Using LIKE - Stack Overflow:

A new one for me. Joining on another table that contains LIKE clauses.

SELECT * FROM dbo
JOIN PatterTable ON dbo.Field LIKE PatterTable.pattern

'via Blog this'

LINKMEMBER MDX Syntax

2012-10-29T08:54:00.003-07:00

Some helpful tricks with LINKMEMBER. Linkmember is used when the cube has multiple hierarchies that could be linked together. Eg. Order Date & Ship Date could be linked together, even though no relationship exists at the fact level. Or in my case Security Code and Security Underlier Code.

The key issue I had was that the HideUnrelatedDimension property was set to false in the cube. This means that you need to explicitly specify the "all" member in the linkmember function, and exclude the all member from the scope of the calculation statement.

Details here.

http://rklh.blogspot.ca/2012/01/scope-statement-on-calculated-measure.html

Interesting MDX Feature

2012-10-04T10:40:00.002-07:00

Apparently there's a keyword test in MDX that returns an empty value, even if the cube doesn't contain a measure called test...

select test on 0
from Adventureworks

Easter egg?

Humor in code

2012-10-03T18:56:00.000-07:00

So I've been getting this error fairly regularly and thought that Mario's arch enemy had done something to my PC. Lately it has been shutting off without warning.

The Source was "Bowser"

The browser driver has received too many illegal datagrams from the remote computer ---- to name WORKGROUP on transport NetBT_Tcpip_{-----}. The data is the datagram. No more events will be generated until the reset frequency has expired.
http://blogs.msdn.com/b/larryosterman/archive/2011/05/02/reason-number-9-999-999-why-you-don-t-ever-use-humorous-elements-in-a-shipping-product.aspx

Apparently this means my computer is beyond repair, or at least the network card is sending out line noise.

Another one that happens every minute since I installed Windows 8:

A corrected hardware error has occurred.
Reported by component: Processor Core
Error Source: Corrected Machine Check
Error Type: No Error
Processor ID: 1
The details view of this entry contains further information.

If it's corrected, how come it keeps showing up every minute?
If it's not an error, why is it called a corrected hardware error?

I always thought the event viewer should have ads for software that fixes event viewer errors or offers up places to buy hardware. That could be a great idea for extra MS revenue...

Reading Larry's blog, I finally figured out how I fixed that insurance office network 17 years ago.
http://blogs.msdn.com/b/larryosterman/archive/2005/01/11/350800.aspx

Flipping the network cards from Auto Detect to Full Duplex solved the issue, since 1 card was blowing up the entire network.

Grouping in SQL RS

2012-09-25T11:29:00.003-07:00

One issue we recently had was that grouping wasn't filtering properly on the report. If you are having issues grouping and filtering items in Reporting Services, make sure the detail row is not deleted. If you have to, set the Row Visibility to hidden, but don't delete the detail.

This solved our filtering issue.

Database documentation tool - Elsasoft SqlSpec

2012-09-12T06:46:00.001-07:00

Documentation is probably something that many people dread. This tool automates the process of generating documentation across many platforms, including Analysis Services, Teradata and Oracle.

Database documentation tool - Elsasoft SqlSpec:

'via Blog this'

Understanding Analysis Services Deployment Modes - Analysis Services and PowerPivot Team Blog - Site Home - MSDN Blogs

2012-08-30T07:25:00.001-07:00

Analysis Services has 3 instance install modes. Multidimensional is for the "legacy" Analysis Services cubes. Tabular is for a standalone tabular server hosting persisted tabular models, or temporary models created by Visual Studio. Sharepoint mode is for integration with PowerPivot functionality in Sharepoint.

There are some key features which may force you to select both the Tabular and PowerPivot modes for different functional requirements.

Understanding Analysis Services Deployment Modes - Analysis Services and PowerPivot Team Blog - Site Home - MSDN Blogs: "Obviously, this way of connecting to a PowerPivot database is very different from connecting to a Tabular database, but the differences between Tabular and SharePoint modes go even deeper than that because the databases themselves are very different in nature. Tabular databases
are permanent databases. PowerPivot databases, on the other hand, are temporary. They can be deleted from the SSAS server and reloaded from their workbooks at any time."

'via Blog this'

Office Links

2012-08-24T07:44:00.002-07:00

Looking for links to MS Office resources? Here you go.

http://sqlblog.com/blogs/kevin_kline/archive/2012/08/23/resource-center-for-microsoft-office.aspx

Spider Schema

2012-08-08T19:21:00.001-07:00

The Spider Schema is an interesting approach to the distinct count performance issue with Analysis Services, and modelling data in general. It is an approach to fix some of the flaws with star schema design. It adds an intermediate table between dimension tables and fact tables, which house the unique key combinations for dimensions in the fact.

It sounds a bit like the Data Vault architecture promoted by Dan Linstedt, though targeted at reporting on vs vaulting the data.

More details here.

About Me | Spider Schema:

Excel 2013's Data Model Feature

2012-08-07T07:46:00.000-07:00

Excel is turning into a scaled-down version of Microsoft Access.
The new Data Model feature lets you build out tables and relationships in your spreadsheet. No more vlookups!

http://office.microsoft.com/en-us/excel-help/create-a-data-model-in-excel-HA102923361.aspx

I have highlighted a new option in the create PivotTable dialog which is to “Add this data to the Data Model”. So what is this Data Model I speak of?

“A Data Model is a new approach for integrating data from multiple tables, effectively building a relational data source inside the Excel workbook. Within Excel, Data Models are used transparently, providing data used in PivotTables, PivotCharts, and Power View reports“. Read more here…

http://blogs.office.com/b/microsoft-excel/archive/2012/08/23/introduction-to-the-data-model-and-relationships.aspx
Data models utilize the features of PowerPivot, now embedded into Excel. They are managed by the PowerPivot add-in, and more structured than Excel tables.

It would be nice to see if there is some way of 'upsizing' or linking these data tables to SQL Server, as we had with Access. Being able to give users the ability to store large datasets on the server without admin intervention could change the way they work with Excel.

Oracle ORAOLEDB Data Type Hokeyness

2012-08-01T08:08:00.003-07:00

My current project pits me against an Oracle Data Source within an Analysis Services multidimensional cube. We've had a few battles so far, and I think I'm getting closer to winning.

The first quirk is just connecting.   Using the ORAOLEDB.1 provider, we specify / as the user name with a blank password, and ensure connection is trusted. Under Advanced - Extended Properties, we specify a FetchSize=10000;defaultRowPrefech=10000; (perhaps larger). This is supposed to improve performance of processing the cube.

The second quirk is creating your DSV. When using the Oracle connection, it lists all tables in all schemas.    The Oracle environment here is defined by separate schemas instead of separate databases. Microsoft likes the database segmentation approach. In a development environment, we have access to everything.   It's not _that_ slow with 18,000 tables and 3x the amount of views, but preselecting a schema would be a nice thing to have.

This laundry list of schemas and tables has a larger impact on the audience using Power Pivot, which regulates Oracle to an "Other Data Source" while my client uses it as their primary data source. Searching through 18,000 tables, and also placing the views at the bottom in unsorted order is not the most friendly interface for building models. Exposing the data using OData links is probably the preferred approach anyway... but there has to be a better way to deal with using Oracle as a data source for Power Pivot.

One major quirk/show stopper using Analysis Services against Oracle is with data types. Sometimes the data types don't get reflected properly. A Numeric() data type in Oracle is a dynamic type, which SQL doesn't like. Casting this to NUMERIC(9,0) should theoretically provide you with Int, or Int32 in Analysis Services world.

It doesn't always. In an Analysis Services data source view, sometimes it doesn't pick up your changes. Casting a number to NUMERIC(9,0) in the Oracle View and refreshing doesn't appear to do anything. My data type is still Int64 or Decimal or whatever the Oracle decides is in my future.

The workaround is to use named queries everywhere. Any changes to the underlying data model requires changing something "case or space or anything" in the named query. Refresh View doesn't seem to work.

Analysis Services appears to then pick up data types properly.
NUMERIC(5,0) becomes Byte (SmallInt)
NUMERIC(6,0 - 9,0) Becomes Int32 (Int)
NUMERIC(10,0 - xxx,0) becomes Int64 (Bigint)

Anything with decimal places becomes a decimal. Cast your decimals and round them to ensure precision doesn't blow up the cube.

Seems like an acceptable workaround. The other way is to manually change the DSV xml. I will be avoiding that one at all costs.

When you build out your multidimensional cube, all surrogate keys and numeric identity keys should be of data type Int32. You can use smaller sizes for smaller dimension tables, but usually the tradeoff for size/performance vs. consistency is negligible. The exception to the Int rule is when you're expecting lots of rows in your dimensions or facts... 2 billion rows? Int64 or BigInt is what you want.

Would like to hear from others building cubes and PowerPivot models against Oracle data sources.   What tricks or quirks do you see when dealing with non-Microsoft sources?

The beginning of the end of NoSQL — Too much information

2012-07-25T10:25:00.001-07:00

Earlier this year, Gartner killed the term Business Intelligence and its associated Business Intelligence Competency Center (BICC) acronym, and "introduced" the term Business Analytics and the concept of Business Analytic Teams (BATs).

Some NoSQL-categorized vendors now prefer to be called anything but NoSQL, since this connotation lumps together various technologies into a single buzzword acronym.

CouchDB is often categorized as a “NoSQL” database, a term that became increasingly popular in late 2009, and early 2010. While this term is a rather generic characterization of a database, or data store, it does clearly define a break from traditional SQL-based databases. A CouchDB database lacks a schema, or rigid pre-defined data structures such as tables. Data stored in CouchDB is a JSON document(s). The structure of the data, or document(s), can change dynamically to accommodate evolving needs.

So if the term NoSQL is dead, what is the replacement? NewSQL? The lack of creativity is amazing.

A database is only a database if it is an organized collection of data. Are NoSQL databases really organized or are they freeform? If it's not a database, what is this unstructured set of information called?

Another term that could be headed for the Furby pile is "Big Data" which is apparently a trademark of a corporation. Massive Data sounds better to me anyway...

The beginning of the end of NoSQL — Too much information

Speaking of Furbys...
http://www.engadget.com/2012/07/06/furby-hands-on-video/

Query Languages and technology Mashups

2012-07-25T08:03:00.000-07:00

SQL (Structured Query Language, See-Kwell) has been around for awhile. IBM engineers developed SEQUEL (Structured English Query Language) in the early 70s to support building and querying relational models. It was adopted as an ANSI standard in the mid-80s. I've been using it since the days of Informix & Sybase, prior to SQL Server 6.5. Various flavours and implementations have been created since then, with the key ones being SQL Server's T-SQL, Oracle's PL/SQL, and IBM's SQL PL, and PostGres PL/SQL, and the various offshoots of those languages.

MDX (Multidimensional Expressions, or Mosha's Data Expressions) are used to query Analysis Services multidimensional and tabular cube models. Instead of a single axis of rows being returned, there is the potential to return 128 axes of data. Usually it's just rows, columns, and sometimes pages, since thinking in 128 dimensions hurts most peoples brains.

DMX (Data Mining Extensions) is a a SQL-like query language used for creating and pulling data from multidimensional mining models. The original team lead for Data Mining at MS is now CTO at a company called Predixions, implementing mining models using PowerPivot.

DAX (Data Analysis Expressions) supercedes MDX for the Analysis Services / PowerPivot tabular models. MDX is still used in the background for Excel when querying PowerPivot models, but DAX is the way you write expressions and calculated functions.

NoSQL (Not Only SQL) isn't really a language, it is a technology categorization. NoSQL databases like those used for Facebook and Google aren't necessarily relational in nature. Usually they are key-value stores. Think thin columns and deep rows. Microsoft has Windows Azure Blob Storage for it's NoSQL offering, with others on the way. HQL (Hive Query Language) is one way of querying a NoSQL database running on Hadoop. Not to be mistaken with Hibernate Query Language, used to query the ORM framework NHibernate. Most NoSQL databases fail the ACID test, and aren't necessarily good for transactional systems. They're great when you need to distribute and analyze massive amounts of data.

NewSQL is either a Sourceforge LDBC driver implementation created around 2003 that offers a simplified SQL syntax, or yet another buzzword coined last year to describe something that isn't SQL and isn't NoSQL. It's easier to use, more scalable, and performs better. It provides ACID support and all the features of new and old database technologies. Or so the vendors of this technology space suggest...

Some products in the NewSQL space include Akiban Server and (perhaps) Microsoft SQL Azure

More info here:
http://www.readwriteweb.com/cloud/2011/04/the-newsql-movement.php

Augmenting legacy SQL databases and technologies with their new "Big Data" columnstore or key-pair counterparts seems to be the Next Big Thing. Eventually one of the Big Dogs will fill this space, probably Microsoft, Oracle, IBM or SAP. For now the niche players will nibble at each other's dogfood, until somebody decides to buy out the dogfood supplier.

What we really need is a database to track and compare the companies, names, technologies, features and query languages used for each of these products. Maybe this database could just be a query language against Wikipedia. As more and more players enter the database market, research companies and consultants who understand the "Big Picture" become more important, as do content aggregators and search tools like Wikipedia, Linked-In and Google.

Using Sequence Identity Column in SQL 2012

2012-07-18T09:41:00.001-07:00

So the conclusion is that identity is 10 times slower than sequence with default cache size. You will not gain significant performance by defining very big cache size.

It sounds like sequence is the way to go when performance and concurrency are issues. Sequence requires rewriting your application inserts to handle the syntax for sequence. eg. INSERT INTO x (id) VALUES (NEXT VALUE FOR seqname)
An insert trigger could potentially replicate the functionality of the identity column.

SQL Server 2012 Sequence Internal » John Huang's Blog

Further details on the internals of identity vs. sequence are here.
http://dba.stackexchange.com/questions/1635/why-are-denali-sequences-supposed-to-perform-better-than-identity-columns

The primary reasons I wouldn't use sequence - in some cases anyway.

1. No DISTINCT keyword allowed when using sequences in query.
2. Can't use in views.
3. Adds complexity vs. identity column.
4. Can't use in defaults for column.
5. Requires trigger to implement identity-style insert logic.
6. Can be updated without setting identity insert option.

Tuning Analysis Services

2012-07-16T10:19:00.003-07:00

Tuning Analysis Services for better aggregation - parallel partitioning.

Set AggregationMemoryLimitMax and AggregationMemoryLimitMin for performance with multiple-cpu environments.

http://geekswithblogs.net/ManicArchitect/archive/2010/11/02/142558.aspx

Data Quality in an Enterprise Warehouse

2012-07-11T02:33:00.001-07:00

Current product releases for enterprise customers, especially O/Ses and database systems, are usually at least 1-2 versions behind in my experience. Enterprise IT doesn't like adopting the latest and greatest technologies until they have been proven, and rightfully so. Some of the latest releases could stop your business in a second with a critical support issue.

Not adopting the latest technologies can also stop your business, though less like a train wreck and more like an old mall department store. If maintenance isn't kept up, prices don't appear competitive and new products don't keep filling the shelves, the store is probably doomed to stagnate to a slow death. If software patches and releases aren't kept up, support lifecycles expire and similar platforms become harder to integrate. Upgrading through 3 versions of an O/S while maintaining compatibility with your in-house proprietary apps becomes not just painful, but nearly impossible.

It's usually the same with data. Fixing problems immediately without doing root cause analysis can be just a band-aid solution. Not fixing problems at all could be even worse, and fixing historical data could cost more than it's worth.

Historians say that it is harder to predict the past than it is to predict the future. The internet is making predicting the past a bit easier, at least the past beyond the mid 90s.

Here's an article from 2005 that's still relevant, regarding data cleanliness in the warehouse.

Despite the great efforts from Inmon, Kimball, and the Others, the world of Data Warehousing is still facing great challenges. Even in 2005, after 14 years of Inmon explaining the concept, more than 50% of today’s data warehouse projects are anticipated to fail [Robert Jaques]. In fact, Ted Friedman, a Principal Analyst in Gartner wrote in 2005, “Many enterprises fail to recognize that they have an issue with data quality. They focus only on identifying, extracting, and loading data to the warehouse, but do not take the time to assess the quality.”

Today’s data warehouses suffer from poor quality of the data. Whether or not the poor quality of data existed a decade ago is a questionable hypothesis. In the 90s, the new breed of software products and the ease of implementing data-moving techniques have opened several avenues that resulted in data duplication. As a result, any data inconsistencies in source systems have been remedied by scrubbing and cleansing them on “local copies” of the data sets rather than taking efforts to correct them at the sources.

If Inmon or Kimball had foreseen the wave of software product proliferation in the 90s that implemented duplicated functionality in an organization, they might have stressed on architecting for better quality.

The premise behind many data warehousing strategies is to bring "all the things" into a central area for reporting, and perhaps analysis. More so just reporting, or "giving the users the numbers" instead of the Semantic Meaning behind them. Just like a real warehouse, the historical data starts to collect dust. Without a proper inventory control system, it becomes hard to find things. The older data elements appear a bit yellowed and sun-bleached, and perhaps have warped or had some design flaws when they were originally input that were only caught in later versions. The newest items may look shiny due to improved validation processes during input time, but could have critical defects due to ETL "Chinese Whispers" gone haywire.

The way companies deal with interpreting and fixing bad data is probably the number one reason why most BI projects fail.

As in a warehouse, it can be hard or just doesn't make ROI sense to go back and fix those old elements. It is much easier to write them off as being known bad data. This can cause grief and confusion for those trying to perform historical comparisons and trend analysis on the data.

In a dimensional model, a measure is also called a fact. What is a fact if it is not quite true? It doesn't become an opinion, it becomes an error. Loading errors into a fact table is not a good idea. So we have a conundrum.

The missing component here is being able to publish scores that tell us the data is flawed, and commentary describing why we are not going to fix it, or how data quality is being addressed, or at least how to interpret the data, and ways to ignore data based on a threshold of quality.

As we move toward the "big data" experience, identifying trust-levels within data sources becomes more important. Facts become opinions instead of errors. Opinion tables sound much nicer to me than Error tables, and for dealing with data that doesn't have to be down to the penny, like internet sentiment analysis, perhaps opinions may work.

I propose an approach to augmenting current star/snowflake dimension models. Opinionate your data models.

1. Add an opinion table to your fact table. Ensure it uses the same grain as the fact table.
2. Include status columns for each column in your fact that you wish to track errors.
3. Include an aggregated status column, and a checksum that can be matched against the source record for auditing purposes.
4. Include a type 2 with history slowly-changing dimension for managing when each fact table load occurred and what the most current load is.
5. Track all errors, questionable data, or data out of the statistical norm using this table.
6. Ensure you have dimensions for ratings, tags and link to a wiki for people to maintain commentary.

This table load could be automated using standard ETL tools, or manually augmented using data input forms. If you're using Excel 2007/2010 and Analysis Services cubes, you could use the writeback feature to assign scores to existing data and provide 1/0 values for true/false flagging. Some tools, like Oracle and SQL, automatically create statistics and histograms on tables which you could leverage to find out what data are outliers. Data Quality Services tools can be trained to understand your data and identify suggestions or even fix your data.

Gamify the process by giving out weekly prizes for those business users that find the most issues with the data, and those technical users that propose the best solutions for fixing the issues.

Sometimes automating data processes can uncover unsettling facts, especially with people & processes that resist automation. Improving data quality gives more power to the users of that data, and less power to the controllers of the data. This isn't always a good thing.... depending on your opinion.

SSIS SCD Wizard Performance Issue « Data Warehousing and Business Intelligence

2012-06-20T05:37:00.001-07:00

In Microsoft BI, when we do a slowly changing dimension type 2, we instinctively do SCD Wizard. The problem with SCD Wizard is the performance. For each source row coming to the SCD box, SSIS does a lookup on the target dimension table to find out if the row already exists or not. That is a crazy idea. And SSIS also update the target dimension table on row by row basis. That is also a crazy idea.

If your dimension is 1000 rows and the source data is 1000 rows, the SCD takes a few minutes. But if your dimension is 500k and the source data is 500k, it’s 5-6 hours performance. The whole idea is fundamentally flawed. On the welcome screen of the SSIS box, there should be a warning: Warning, do not use SSIS SCD Wizard if your dimension is above 1000 rows.

So, if your dimension is 1m rows, what should you do?

SSIS SCD Wizard Performance Issue « Data Warehousing and Business Intelligence

Filtering by logged in user

2012-06-19T08:37:00.001-07:00

How to get the current user name in MDX.

WITH MEMBER Measures.UserID

as
'UserName()'
SELECT
[Measures].[UserID]
ON COLUMNS
FROM

Filtering by logged in user

MSBI Academy

2012-06-08T06:45:00.001-07:00

An excellent resource for Excel, SQL Server & Sharepoint BI training

MSBI Academy - Home

Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode)

2012-06-06T10:56:00.001-07:00

To configure Reporting Services against an existing Sharepoint 2010 farm, you need to go to the root site and enable the proper features. Then configure libraries in subsites to expose the proper content types (Report Builder Model, Report, Data Source).

It can get a bit tricky. Here are some links.

http://blog.cloudshare.com/2012/05/22/step-by-step-guide-to-installing-sharepoint-with-sql-2012-powerpivot-powerview-and-reporting-services/

Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode)

SQL Error 909 when restoring ContosoRetailDW.bak using MS SQL Server Mgt. Studio

2012-06-06T09:54:00.001-07:00

If you’re trying to restore the Contoso database samples on a BI or Standard edition of SQL 2012, you’ll get an error about Enterprise features being used. Here is a script that can be run on a trial Enterprise or Developer edition of SQL 2012 to remove that dependency.

ALTER INDEX ALL ON DimChannel REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON DimEntity REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON DimProduct REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON DimStore REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON FactExchangeRate REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON FactInventory REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON FactITMachine REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON FactOnlineSales REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON FactSales REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON FactSalesQuota REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON FactStrategyPlan REBUILD WITH (DATA_COMPRESSION = None);

SQL Error 909 when restoring ContosoRetailDW.bak using MS SQL Server Mgt. Studio

Share cache across SSIS packages

2012-05-31T04:21:00.001-07:00

Shared caching is a new feature of SSIS 2012 that could improve performance when using the same large lookup table across multiple packages. It could be replicated by saving to a raw file in SSIS 2005/8, however this should be faster.

Prior to SSIS 2012 it was not possible to re-use or share the same cache across packages. What that meant is if you created a cache you will be able to consume it only in the very package where the cache was instantiated. Therefore, a developer could take only two routes from here – either make as much heavy ETL processing as possible inside the same package where the cache resides, or populate yet another cache object in every other package where it is needed. The latter was
especially harmful leading to unnecessary heavy extra database calls and an extended development time. This limitation has been overcome in the SSIS 2012 release.