Hitachi Consulting BI Blog

MDX cell set into SSIS data stream [Reed + Pablo]

I've seen two techniques described for getting the results of an MDX query into an SSIS data stream. 1 - Use the OLE DB Source Adapter and ignore the whiny warning. This seems to work when you Preview, but crashes SSIS (even SP2) when you run the job. 2 - Use a linked server and an OpenQuery command. This works but has a lot of overhead. My colleague Pablo Guzman and I came up with a pretty slick way to do it. - Create a new ADO.Net Connection and from the .Net Providers\SQLClient Data Providers list choose the OLE DB Provider for AS9.0. - Create a DataReader Source adapter connected to the new AS connection manager. - Put the MDX into the SQLCommand property--be sure to map the external and output columns. This obviously flattens the cell set, but it doesn't limit what you put on the axes--probably a good idea to have the columns end up static. The DataReader Source adapter turns all the columns into DT_NTEXT, which you then have to convert to DT_WSTR to be able to convert to anything else, but that's standard SSIS data type manipulation. Seems a lot slicker than creating a linked server. Cheers, Reed

The 'Currency' number format style is Evil [Reed]

I always used to use Currency formatting for currency values, but it's totally wrong in a database environment. Currency displays using the current locale's currency setting. But the values in your database have a pre-known, specific currency. If the values in your database are in Euros, then you don't want somebody from California to see them showing up as dollars. The origin of the Currency style was to simplify life for people using tools like Excel to input numbers. But when used with a database, they can cause rediculous restatements. Once I thought about it, Currency format is so rediculous that I can't believe I used to use it all the time. Hence the blog entry. Even when doing currency conversions--you want to be in control of which currency format the values take. You NEVER want to be at the mercy of the current user's control panel whims. Cheers. Happy 4th of July to all us Americans. [Reed]