SSAS: Vague Processing Errors About Connection Strings Are Not My Friend

Oh Visual Studio, you crazy cat, you! I've just spent the past hour or so going stark raving mad. courtesy of a DBA who's less than 'expert' and a teeny little Visual Studio property I neglected to set properly.


When deploying my Analysis Services database, the processing kept failing with a vague error along the lines of "connection string is invalid" and "login timeout". Now, I know there's nothing wrong with the connection string cos it works fine in VS, right? Wrong!


When deploying a cube to the Integration environment, my DBA told me that I couldn't just deploy it (outrageous suggestion), no I had to give them the project, they would deploy it, then I had to reimport it from the new environment into Visual Studio. WTF? Ok, I'll play. I'm new here after all so who am I to question their processes?


So when I later needed to redeploy some changes I'd since made, the DBA told me I had to do the same again. At this juncture, I put up a big red stop sign and start asking some questions. Turns out they do that so the Impersonation works correctly. Why wasn't it working as expected is the real question they neglected to ask and solve. Instead of fixing the problem, the treated a short-term workaround as the solution, and stopped there, purely because they didn't know any better.


Having rarely used impersonation before I didn't have an immediate answer. I've always just use the service account as my credentials because I'm lazy and frankly I can't really see why, on a DEV box mind you, you'd bother? Whatever, I don't care so much, if that's how they want to do it I'm easy.


So the problem is that after redeploying the project, it wouldn't reprocess. As I mentioned earlier, I was getting these vague errors about logins and connections. Turns out the solution was really freakin' simple.

See that little "Remove Passwords" property? Yeah that's the sucker. That cheeky tyke was set to TRUE. Set it to false and whaddya know? Everything works a treat.

Aaaah... Friday 4pm... and time for a brewskie.

TSQL: Programmatically Create DDL scripts for Foreign Keys

I'll admit it, I'm lazy... I hate typing exercises. I don't need the practice and I'm sure the person paying my somewhat exhorbitant fee doesn't want to fork out for something so mundane either. That means I'm always on the hunt for a shortcut or two, I mean, process improvement.

These two snippets will generate the DDL to create all the foreign keys in the database.
The first will check if it exists first, the second drops it first. Use whichever suits.

SELECT 'IF NOT EXISTS (SELECT * FROM sys.FOREIGN_KEYS WHERE name = ''' + fk.Name + ''' AND Parent_Object_Id = object_id(''['+ s.name + '].[' + t.name + ']'')) BEGIN '
+ char(13) + char(9) + 'ALTER TABLE ' + s.name + '.' + t.name + ' WITH CHECK '
+ char(13) + char(9) + 'ADD CONSTRAINT ['+fk.Name +'] '
+ char(13) + char(9) + 'FOREIGN KEY(['+c.name+']) REFERENCES [' + fks.name + '].['+fkt.name+'] (['+fkc.name+']) '
+ char(13) + 'END'
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkcm on fkcm.constraint_object_id = fk.object_id
inner join sys.tables t on t.object_id = fkcm.Parent_Object_Id
inner join sys.tables fkt on fkt.object_id = fkcm.Referenced_Object_Id
inner join sys.columns c on c.object_id = t.object_id and c.column_id = fkcm.parent_column_id
inner join sys.columns fkc on fkc.object_id = fkt.object_id and fkc.column_id = fkcm.referenced_column_id
inner join sys.schemas s on s.schema_id = t.schema_id
inner join sys.schemas fks on fks.schema_id = fkt.schema_id
ORDER BY 1

And the second one (ie does a drop first)...

SELECT DISTINCT 'IF EXISTS (SELECT * FROM sys.FOREIGN_KEYS WHERE name = ''' + fk.Name + ''' AND Parent_Object_Id = object_id(''['+ s.name + '].[' + t.name + ']'')) BEGIN '
+ char(13) + char(9) + 'ALTER TABLE ' + s.name + '.' + t.Name + ' DROP CONSTRAINT ' + fk.Name
+ char(13) + 'END'
+ char(13) + 'ALTER TABLE ' + s.name + '.' + t.name
+ char(13) + char(9) + 'WITH CHECK ADD CONSTRAINT ['+fk.Name +'] '
+ char(13) + char(9) + 'FOREIGN KEY(['+c.name+']) REFERENCES [' + fks.name + '].['+fkt.name+'] (['+fkc.name+']) '
+ char(13)
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkcm on fkcm.constraint_object_id = fk.object_id
inner join sys.tables t on t.object_id = fkcm.Parent_Object_Id
inner join sys.tables fkt on fkt.object_id = fkcm.Referenced_Object_Id
inner join sys.columns c on c.object_id = t.object_id and c.column_id = fkcm.parent_column_id
inner join sys.columns fkc on fkc.object_id = fkt.object_id and fkc.column_id = fkcm.referenced_column_id
inner join sys.schemas s on s.schema_id = t.schema_id
inner join sys.schemas fks on fks.schema_id = fkt.schema_id
ORDER BY 1

Integration Services Gotcha #6631984: The Table Variable Data Flow Affair

I encountered a teeny little SSIS glitch today that completely did my head in for a good 30 minutes. Ok, I'll admit it, more like an hour.

In my data flow, I had an OLEDB Source which declared and populated a table variable then returned some data, which included a join on my table variable.

The source query worked perfectly, returning plenty of data when tested through SSMS. As soon as I ran the data flow, it would be successful but zero data.

That freakin' table variable was messin' with me. Took it out, replaced it with a subquery and whammo! Data.

Oh SSIS, you pernickety old man, you!

SQL 2008: Using MERGE to do a Type 2 Change

I'm going to do a brief overview of the SQL 2008 MERGE statement. Primarily for my own purposes - being to document the usage for type 2 changes so I don't forget it.

First let's begin with the basic type 1 usage.


All fairly self-explanatory. We have a source and a target. We join the two tables, identify new records, changed records and deleted records. And deal with them appropriately.

So let's move onto the type 2 changes. Here we need to:

• Insert New Records
• Disable changed records
• Add a new record for the changed records
  

That's 2 inserts and an update. The MERGE won't allow that so we have to nest our merge inside an insert. Luckily this works because MERGE allows output!

Ignore the outer INSERT for the moment. We have a MERGE statement which is very similar to our type 1 MERGE. Compare the Source and Target, INSERT new records as required. The difference being that when the matched record contains a change, instead of updating the changed field, we set the CurrentFlag and the EndDate so as to disable the record.

Of course that means we still don't have a record which contains our updated values. So, now lets turn our attention to the OUTPUT clause.

This will be familiar to anyone who used it in SQL 2005 in much the same context - to identify rows which have been modified/added/deleted. Really the origin of MERGE seems to be strongly rooted in the SQL 2005 OUTPUT clause. On the upside, unlike the SQL 2005 version and UPDATE is an UPDATE and not a DELETE and an INSERT. Small but handy! Anyway, where were we? Let's return the columns we need to create a new record and a special $Action column - this tells us what actually happened ie UPDATE, INSERT or DELETE. We need this because the OUTPUT will return everything that occured in the MERGE.


Now we have a set of rows that have been actioned by the MERGE. Still, we haven't actually done anything with them.

To do that, we enclose the entire MERGE statement, including it's OUTPUT clause of course, into brackets so we can make it the FROM clause for an INSERT. Our OUTPUT clause is return the columns we need for the INSERT remember? So now we just add our INSERT INTO at the front, add an alias to the closed bracket of the FROM clause AND, drumroll... whack a WHERE clause on the end. This where clause just filters the OUTPUT from the MERGE so we only insert new records for those that were updated, ignoring those that may have been inserted etc in the MERGE.

Easy!


-------------------------------------------------------------------------------------------------

For the purposes of copy/paste-ability, here is a text version of the TSQL. Images have been used above for improved readability.

--A simple type 1 change
MERGE INTO DimCustomer c
USING StageCustomer stg ON c.CustomerId = stg.CustomerId
WHEN NOT MATCHED BY TARGET THEN --ie new
INSERT (CustomerId,CustomerName,IsActive) VALUES (
stg.CustomerId
,stg.CustomerName
,stg.IsActive
)
WHEN MATCHED AND c.CustomerName <> stg.CustomerName THEN --ie changed
UPDATE SET c.CustomerName = stg.CustomerName
WHEN NOT MATCHED BY SOURCE THEN --ie deleted
DELETE


--a type 2 change
INSERT INTO dbo.DimCustomer
SELECT CustomerId, CustomerName, IsActive, IsCurrent, Eff_Date, End_Date
FROM
( MERGE DimCustomer c
USING StageCustomer stg ON c.CustomerId = stg.CustomerId
WHEN NOT MATCHED THEN INSERT VALUES (
stg.CustomerId
,stg.CustomerName
,stg.IsActive
,1 --> IsCurrent
,GETDATE() --> Eff_Date
,NULL --> End_Date
)
WHEN MATCHED AND c.IsCurrent = 1 AND (c.CustomerName <> stg.CustomerName) THEN UPDATE SET
c.IsCurrent = 0
,c.End_date = GETDATE()
OUTPUT $Action as RowAction
,stg.CustomerId
,stg.CustomerName
,1 AS IsCurrent
,GETDATE() as Eff_Date
,NULL as End_Date
) m
WHERE m.RowAction = 'UPDATE'

SharePoint Magazine - Part 4 available now

My next article for SharePoint Magazine is available now.

Dissecting Dashboards (Part 4 of 6)

Sharepoint Saturday

Register now for Sharepoint Saturday in Sydney on August 8th.

http://www.sharepointsaturday.org/sydney/default.aspx

Can Dynamics CPM pick up where PerformancePoint left off?

Can Dynamics CPM pick up where PerformancePoint left off? And how does all of this fit in with SSRS Report Builder and Project Gemini, a key part of Microsoft's self-service BI drive? When you say things like "Microsoft Forecaster is a budgeting and planning application that allows companies to build budgets based on specific objectives", one has to wonder!

And "Enterprise Reporting is designed for sophisticated group reporting and consolidation needs, and includes advanced multi-dimensional consolidations, eliminations and multicurrency capabilities. Enterprise Reporting’s planning environment is easily customizable to fit diverse budgeting and forecasting needs across all industries and organizations. Enterprise Reporting provides strong analytical modules to make it extremely easy to create new ad-hoc reports on the fly"

Seriously? That's quite a bundle of tasks. Enquiring minds want to know more!