OLAP PivotTable Extension

Recently I was working on a PivotTable Report with data from a SSAS Cube (Excel version 2010).
I needed a Profitability Trend calculation for a client, but this calculation was not available in the Cube. Because in Excel 2010 the functionality to create calculated measures is not exposed in the User Interface, I searched on the internet and found OLAP PivotTable Extension, a free add-in for Excel.

Click here to check out the features of OLAP PivotTable Extension.

SSMS - Saving changes is not permitted

When you design a table in a SQL Server database and then try to make a change to a table structure that requires the table to be recreated, SSMS (SQL Server Management Studio) will not allow you to save the changes.

The error you will get is:
“Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.”

Disable “Prevent saving changes that require the table re-creation” 
In SSMS go to Tools | Options | Designers | Tables and Designers as shown in the below snippet. Under Table Options you need to uncheck “Prevent saving changes that require the table re-creation” option and click OK to save changes. Now SSMS will work like expected.

Important
Microsoft recommends that you do not work around this problem by turning off the Prevent saving changes that require table re-creation option. For more information about the risks of turning off this option, see the "More information" section in the kb article from Microsoft on : http://support.microsoft.com/kb/956176

SSMS Tools Pack (free)

Recently I wanted to export a configuration table with data from Microsoft SQL Server 2008 R2, through generating "INSERT INTO" scripts. Because it was a big (and frustating) search in SQL Server Management Studio (SSMS) to get this done, I went looking for a tool that could simplify this.

After some googling, I found SSMS Tools Pack. SSMS Tools Pack is an add-in for Microsoft SQL Server Management Studio (SSMS) 2005, 2008, 2008 R2, Denali CTP3 and their respective Express versions. It is a very useful tool, and free to use.

Download
Go to http://www.ssmstoolspack.com/Download

Features
Click here to view all the features of SSMS Tool Pack.

BI solution delivers insight on CXL P&L attributes

BI Architects develops Business Intelligence (BI) solutions for its customers ranging from commodity trading and enterprise risk to project management.

In this video blog I want to present a BI solution we have developed for one of our customers, that uses Triple Point’s Commodity XL (CXL), giving essential insight in the in the causes of the position and mark to market movements, including physical operations (P&L attributes).

The presentation depicted below describes our customer's needs, CXL and the solution itself.



For more information on our CXL BI solution for gaining insight on P&L attributes, please contact our sales manager Erik Rongen-Kleintjens via E.Rongen-Kleintjens@bi-architects.nl .

For more information on Triple Point’s Commodity XL (CXL) software, go to tpt.com .

SQL Server 2012 Virtual Launch Event

On March 7th, 2012 the SQL Server 2012 Virtual Launch Event take place. This online event will allow you to immerse yourself in the exciting New World of Data with SQL Server 2012.

You will have access to over 30+ sessions to learn about the new capabilities of SQL Server 2012 at your own pace, on your own schedule. This event brings together a who’s who of industry experts and executives to tell the SQL Server 2012 story. You will also “meet” with partners, experts, and peers in a virtual “Expo Hall” to discuss SQL Server 2012 one-on-one.

For the full agenda of this event or to register, click here.

SSIS datatypes VS SQL Server datatypes

Often it can be extremely confusing when you encounter SSIS data types.  At first glance they seem to be nothing like SQL Server data types.  That's why I wanted to write this blogpost,  below a conversion chart of SSIS data types to SQL Server data types.  This information is readily available on MSDN but it always seems difficult to find.  

SSIS Data Type	SSIS Expression	SQL Server
single-byte signed integer	(DT_I1)
two-byte signed integer	(DT_I2)	smallint
four-byte signed integer	(DT_I4)	int
eight-byte signed integer	(DT_I8)	bigint
single-byte unsigned integer	(DT_UI1)	tinyint
two-byte unsigned integer	(DT_UI2)
four-byte unsigned integer	(DT_UI4)
eight-byte unsigned integer	(DT_UI8)
float	(DT_R4)	real
double-precision float	(DT_R8)	float
string	(DT_STR, «length», «code_page»)	char, varchar
Unicode text stream	(DT_WSTR, «length»)	nchar, nvarchar, sql_variant, xml
date	(DT_DATE)	date
Boolean	(DT_BOOL)	bit
numeric	(DT_NUMERIC, «precision», «scale»)	decimal, numeric
decimal	(DT_DECIMAL, «scale»)	decimal
currency	(DT_CY)	smallmoney, money
unique identifier	(DT_GUID)	uniqueidentifier
byte stream	(DT_BYTES, «length»)	binary, varbinary, timestamp
database date	(DT_DBDATE)	date
database time	(DT_DBTIME)
database time with precision	(DT_DBTIME2, «scale»)	time(p)
database timestamp	(DT_DBTIMESTAMP)	datetime, smalldatetime
database timestamp with precision	(DT_DBTIMESTAMP2, «scale»)	datetime2
database timestamp with timezone	(DT_DBTIMESTAMPOFFSET, «scale»)	datetimeoffset(p)
file timestamp	(DT_FILETIME)
image	(DT_IMAGE)	image
text stream	(DT_TEXT, «code_page»)	text
Unicode string	(DT_NTEXT)	ntext

Hope this helps!

ShellRunas – Launch Programs with Different Account Credentials

ShellRunas is free utility that integrates into the Windows context menu to launch programs as a different user. It provides similar functionality to that of the command line based RunAs utility. The command-line Runas utility is handy for launching programs under different accounts, but it’s not convenient if you’re a heavy Explorer user. ShellRunas provides functionality similar to that of Runas to launch programs as a different user via a convenient shell context-menu entry.

Screenshot

At BI Architects we are using ShellRunas a lot for logging into SSMS (SQL Server Management Studio) with different credentials. If you want more information about ShellRunas or if you want to download the utility, click here