SSIS SCD Wizard Performance Issue « Data Warehousing and Business Intelligence

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

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

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)

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

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