SQL Job failure – The SSIS logging provider has failed to open the log

This error occurs because the account it is running under does not have access to open the log file. Either give this account permission or remove the log file from the packages i.e. right click on control flow then choose Logging.

Read Full Post »

Analysis Services deployment error – No mapping between account names and security IDs was done

This error has got nothing to do with user permission or account to connect to cube. It relates to invalid members defined in the role. For example, if the VM has not joined a domain but there are members with domain usernames, these users need to be removed in order to fix this error.

David Francis explains this in details in his blog below.

http://consultingblogs.emc.com/davidfrancis/archive/2007/10/01/SSAS-2005-_2D00_-No-mapping-between-account-names-and-security-IDs-was-done-error-on-deployment.aspx

Read Full Post »

How to incorporate deletion in a dimension as deletion is not performed in the Slowly Changed Dimension (SCD) task?

From my experience, SCD always performs very slow if there are more than 9000 rows in the dimension to process. I have therefore had to customise it to use Conditional Split and Lookup tasks instead. For deletion, I have had to use EXCEPT() in the Execute SQL Task. I found out about this Kimball SCD later and it has simplied and sped up the warehouse development. It is an excellent tool and I would recommend this to any BI developers.

It is similar to SCD but it performs much better! The inputs that feed into the Kimball SCS are the source and the existing dimension. The outputs are: new, deleted, unchanged, updated SCD1, new SCD2, expired SCD2, invalid input, and auditing. Invalid input detects row duplication and other errors.

An example:

Thanks so much to Todd McDermid!

http://kimballscd.codeplex.com/

Read Full Post »

How to make pivot column headers dynamic?

We have business requirements to build an organisation chart in the warehouse as a dimension. There is a parent and child relationship in each level in the hierarchy. For example: Company -> Group -> Division ->Business Stream -> Region -> Business Unit Operation -> Location Facility -> Project Department -> Workgroup. Workgroup is the lowest level and must not be NULL. This information is stored in the database in one table column, hence, we have to use PIVOT() function so that each level is pivoted as a column header.

Each companay has its own organisation chart therefore this column header must be dynamic.

To make a list of organisation hiearchy as comma separated value, use FOR XML PATH at the end of the SELECT statement.

The following blog explains it all – http://blog.programmingsolution.net/sql-server-2008/comma-separated-list-csv-of-rows-of-a-column-using-for-xml-path/

This solution incorporates some variables hence the need to use a stored procedure. To use a stored procedure in the OLE DB Source SSIS component, set the data access mode to SQL Command then type the following:

— Causes the exec statement to return the column information only; no rows of data are returned. This is because this SP returns dynamic column information for workgroup hierarchy. Each client has its own organisation chart.

SET FMTONLY OFF

EXEC Warehouse.sp_IS_dm_Workgroup.

The following blog explains how to set up SP in the OLEDB Source in details.

http://www.ssistalk.com/2007/10/10/ssis-stored-procedures-and-the-ole-db-source/

Read Full Post »

How to display Reporting Services report in a new browser window (via URL)?

Rob Fisch explained the solution in step by step in

http://www.mssqltips.com/tip.asp?tip=1283.

The answer is to use javascript:

=“javascript:void(window.open(‘http://servername/reportserver?%2fpathto%2freport&rs:Command=Render&ProductCode=“+Fields!ProductCode.Value+“‘))“

Read Full Post »