DB Antics

Hey Doc, can we attach only the data file in SQL Server?

2009-06-26T01:40:00.001+05:30

“Hey Doc, I have salvaged the MDF file of an important database. Can I attach it to SQL Server in order to use it?” was the question from an old colleague.

Yes, you can…

“And oh! I don’t have the log file. And I renamed the MDF file by mistake too.”

No problem…

This is how you do it…

When you bring up the Attach Databases dialog and select the salvaged and renamed file, you would see the following situation on it (click on image for larger view).

The second grid lists the original database files followed by the Not Found message. This is obviously since you do not have them in the original location. At this stage you cannot move ahead by clicking on OK. You will have to remove both the entries using the Remove button below that grid.

Then you have to add the new data file; click on the Add Catalog button, then browse and select the new MDF file (the one which you had salvaged and renamed). You can then optionally change the name of the new (to be attached) database from the Attach As textbox.

Click on OK and Voila!, you’ve got your old database back.

Share this post :

Obtaining the Current Time

2009-06-15T14:08:00.004+05:30

As we all know, SQL Server 2008 comes with a time data type. Obviously, as the name itself denotes, this data type is capable of storing the time sans the date component. So, how would one populate a time field or variable with the current system time? One would obviously think of the getdate() function and some fancy operations to extract the time component out of it. At least, that is what I thought. But, the answer is much more simpler:

         DECLARE @CurrentTime time        SET @CurrentTime = GETDATE()        SELECT @CurrentTime

Share this post :

Populating an Intermediate Fact Table

2009-06-08T20:36:00.002+05:30

This is but one scenario where and how we use an intermediate fact table. Intermediate fact tables are used in cases where you have a many-to-many relationship between two dimensions (also known as Many-to-many dimensions). To introduce a simple example:

Imagine a bank. Account holders. Bank accounts. A bank account can be held by multiple account holders (joint account), whereas an account holder may hold multiple accounts. Transactions are usually recorded based on accounts. However, when business users need to analyze the business data they would like to analyze from the Account holder point of view as well, hence the data model may have to be put up to look something like this:

Check out these links to know more about many-to-many dimensions; The Many to Many Relationship, Many-to-Many dimensions in Analysis Services or try binging it.

My scenario is this. The source system has Groups and Users. Both of them have been identified as dimensions (DimGroup and DimUser). ETLs already created and tested. Since a user can exist in multiple groups and also obviously a group can have multiple users in them, makes this an ideal many-to-many scenario. Something simple and noteworthy in this scenario is that historical tracking is not necessary. Which means I need not store the history of a user belonging to a particular group 3 months ago and now does not.

This is how the data population was done (image contains detailed steps):

Reasoning: The data to be stored in the target is made up of only two columns that are composite (GroupID and UserID), hence using the slowly changing dimension would not do. The records that come in from the source after getting looked up for the appropriate keys in their respective dimension tables (i.e. DimGroup and DimUser) either shall be inserted if not matched with the destination (i.e. DimGroupDimUser) or be sent on their way (matching records). These matching records are right-outer-joined against records from the destination (i.e. DimGroupDimUser) in order to be deleted (where matching records’ UserID == NULL and matching records’ GroupID == NULL). There are no updates that can happen here. If an update happens at the source; a record will be deleted and another inserted at the destination (This is more or less why the Slowly Changing Dimension cannot be used in this scenario).

Of course, I feel that this could be done in other (maybe even better ways). I would love to hear your comments on this.

Share this post :

What on earth is an Array Formula?

2009-06-05T13:02:00.001+05:30

This has nothing to do with SQL Server, save a teeny weeny part. Me gets this requirement from above: Do this report thingy with Excel using macros. It shall be a temporary solution. And it shall be parallel to the permanent reporting solution that you are already developing, but with a higher priority (That’s like building a super saloon car while also building a rickety lorry with a high priority to do somewhat the same thing as the car, ‘cept that the rickety lorry should be shipped first. – That’s how one colleague put it). Ye shall extract the data from Good ole SQL Server to the Excel workbook, and Mr. Senior Whiner shall do the macros in such a way that it'll create some nice looking reports on more Excel sheets. Anyways, with a lot of whining and I-dunnos and I-cannots the macro ball was passed by Mr. Senior Whiner to scape-goat builder. R n' Ds by meself and scape-goat builder led to the discovery of array formulae.

I have been working with Excel for a long time, not on an extensive scale though, but quite a power user. Therefore, array formulae just came out as a big surprise. I never knew something like that ever existed.

So what's an Array Formula, you ask?

It's a formula which can be used in place multiple ‘normal’ formulae. And it works on arrays of values. And in order to enter the formula, you need to first type it in and then press Ctrl + Shift + Enter on the keyboard. Otherwise you'll end up with the #VALUE! error. Once you enter the formula successfully, you will notice that the whole formula is contained within a pair of curly braces which disappear when you click on the formula bar.

In the screenshot you could see that in order to get the total value for each region I use an array formula as follows:

=SUM((C2:C17)*(D2:D17)*(A2:A17="East"))

This is just a simple example. There's a whole dimension of complex formulae that can be written in a simpler and powerful form using array formulae. Just google 'Excel Array Formula', or perhaps you could even bing it (if you dare)

Share this post :

Minor Irritation in Datadude 2008

2009-05-27T17:41:00.002+05:30

There occurs an irritating situation in Datadude 2008 (I hear that it is from GDR1 onwards) during Schema Comparison. I am not sure about Datadude 2005 though. It happens when the source is a project and the target is a database, and you try to write updates to the target. What happens is: nothing happens! The schema comparison screen continues to look exactly as it looked like; showing the differences and what action has to be performed, which is indeed irritating, until of course you open the Error List window.

What happens when you click on the Write Updates button is that; if there is some change which cannot be applied to the database destination such as an action which would add a non-null column without a default to a table with data, or if there is simply a syntax error in the source project such as ADD [NewColumn] varchar10; changes are not applied to the target database. The thing is, the Error List gets updated alright, but it does not pop-up, even when it is hidden. This just gives us the “illusion” that nothing happens. Also, in case there is a syntax error such as in the latter scenario; the Export to Editor button gets grayed out.

This may be a minor issue, but it sure irritates when you didn't know it before hand. Solution: whenever you experience that updates are not written to the target database or if the Export to Editor button is grayed out, all you have to do is open the Error List...

Share this post :

SQLCMD and its usage in the Management Studio Query Editor

2009-05-25T19:25:00.002+05:30

I have been getting used to using Datadude (a.k.a. Visual Studio Team System 2008 Database Edition) in the recent days (I jumped directly onto the Datadude 2008 bandwagon).

Coming to the topic… When generating schema scripts, let's say to create a new database or alter an existing one, datadude uses some SQLCMD codes. It uses these, for example to set the database name in a parameterized fashion. Like this:

:setvar DatabaseName "MyDatabase"
:setvar DefaultDataPath "D:\Databases\"

CREATE DATABASE [$(DatabaseName)]
ON
(
NAME = [$(DatabaseName)],
FILENAME = '$(DefaultDataPath)My_Database.mdf',
SIZE = 51200 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10240 KB
)
LOG ON
(
NAME = [$(DatabaseName)_log],
FILENAME = '$(DefaultDataPath)My_Database.ldf',
SIZE = 102400 KB,
MAXSIZE = 2097152 MB,
FILEGROWTH = 1024 KB
)

When you need to parameterize stuff such as database file paths or database names in your create database script or something you may be in for a fix when using the usual T-SQL parameters, and may need to write a little more complex T-SQL for the task, such as dynamic SQL which I feel is somewhat unnatural. Using SQLCMD, therefore makes it quite simple and clean. What hurts me though, is that SQLCMD has been around since SQL Server 2005, and I have only just adopted it.

When using SQLCMD in the Query Editor of Management Studio, it would not work under the default settings. You would as a result need to enable SQLCMD mode from the Query menu.

The primary usage of SQLCMD though, is for command prompt based queries. Yet, it also comes in quite handy in times like these mentioned earlier.

Restoring a database to a lower edition – SQL Server 2008

2009-05-13T15:32:00.002+05:30

SQL Server 2008 introduced a few Enterprise Edition-only features which changes the structure of a database when implemented. These features are:

Partitioning
Change data capture
Transparent data encryption
Data compression

Due to this, whenever you do a database restore or attach on an SQL Server 2008 instance with a lower edition, SQL Server would not allow you.

So what can you do when you have to move a database to a lower edition? Remove the offending features of course, there's nothing else that you could do. But, how would you know which of the offending enterprise edition-only features are enabled on your database? Use the new dynamic management view sys.dm_db_persisted_sku_features. It's a simple view which returns the feature you have enabled on your database along with its id. Hence, if the view shows any records, all you got to do now is disable the feature(s) before backing up.

Note: Of course partitioning was present in SQL Server 2005 as well, and even there you had to remove partitioning if you had to restore a database on a lower edition.

Slipstreaming SQL Server 2008 SP1

2009-04-09T12:51:00.012+05:30

Service pack 1 for SQL Server 2008 was released just a couple of days ago (07-Apr-09), eight months after the RTM. As exciting as it is, I did not want to put up just an 'SP1 Released' post, since it may seem a little too ambiguous and also since I had already done it on the 'Universe'...

One feature of this service pack is that it allows for administrators to slipstream it into the main product, hence when new SQL Server 2008 installations are required, they could just install it along with SP1 in one go. This obviously saves time and money. Put that together with the fact that many people haven't gone ahead with SQL Server 2008 yet; you'll have a lot of happy administrators when their organizations finally decide to go ahead with implementing SQL Server 2008.

I just tried creating my own slipstreamed SQL Server 2008 with SP1 drop (following these steps), coupling in the latest version of Books Online as well. The only issue was that the final size of the entire folder was more than 5GB, which requires me to compress it if I need to burn it onto a DVD. Isn’t it nice, when things just (almost) work...?

To get Certified, or Not to get Certified

2009-04-03T08:34:00.012+05:30

Being technically certified is something that all of us (or at least most of us) would love to be. But, getting there is certainly a little bit more practical than wishing it were so. Surprisingly getting oneself technically certified is not a elephantine task as most of us think. It obviously requires some effort alright, but most of us (me included in the not so distant past) always like to put it away for next month. I have successfully put away my certification exams for next month, only to realize that it it had actually gone beyond an year. And not just once, but twice. And the best part of it was, that I started giving myself excuses; It's only been a couple of months since the last exam, I'll do it next month. Darn! Too much work at office, I'll do it next month. I have this and that to do this month, I'll do it next month.

The second time it happened, it really hurt. Who was I fooling? And what was I trying to prove pushing back the exams?

See, the first time I did my certification (SQL Server 2005 MCTS) after an year of pushing back was when I got a free voucher from Merill. And it was to expire in a little more than a week. And I had to use it. Use it, i did and got through. Then the pushing back came again, and it was worse this time - 19 months. Then I received a couple of free vouchers for SQL Server 2008 beta MCTSs and some random googling found me more voucher codes published by considerate people for SQL Server 2008 MCITPs, and the thing was all of these were only valid for a month. But heck, I didn't want to waste even one of them. Used them and got through: Certifications are no more going to be pushed back again.

See, there's one thing that we Sri Lankans love, and that is getting stuff free or for a discount. I could even be a little bolder and extend this statement to include Asians as well. So, if something valuable (yes 50 USDs is quite valuable to a lot of folk) comes along free or with a discount it is really worth it.

What I am really trying to say is, guys, there are offers that keep coming up from time to time, where you could get discounts or free vouchers to do your certifications. Grab these opportunities and get it done with, and keep going. Don't set deadlines too far off. Don't give yourselves excuses for doing it next month. You never know how your certification will help you push yourself that extra bit in your career. Even if you have to pay for your certification in full, get it done with...

It also happens that I have 10% discount vouchers with free second shot available until April 30, 2009. These vouchers can be used to do any MCTS, MCITP or MCPD exams before May 31, 2009. If anyone is interested mail me at gogulaa[at]gmail.com, and I shall be happy to send you your voucher. This offer is only valid for exams taken in Sri Lanka. If you are from another country, you could try contacting an MVP from there and maybe he/she will be able to help you out.

~~Disclaimer: No, the above article is not an advertisment to lure you into requesting vouchers. :)~~

The History of Business Intelligence

2009-03-26T09:32:00.017+05:30

Given here is a video of a presentation done by Nic Smith, BI Solutions Manager at Microsoft. He talks about how Business Intelligence came into being, along with its failures. I think it's a really cool presentation which I believe should be viewed by anyone in BI.

Apart from the excellent and simple narrative, it also gives a little insight on the history of pop culture mixed with subtle humor, which really kept me smiling throughout the presentation, especially the "iced out" part. Man that was funniest! The thing is; it's a very simple presentation, but you could see the effort that had gone into it, a manifestation of the quote by Spanish playwright Poncela: "When something can be read without effort, great effort has gone into its writing". Hats off to Nic. Enjoy...

Please put your comments on the original post: http://blogs.msdn.com/bi/archive/2009/03/22/history-of-business-intelligence.aspx.

Of Excel, Errors, SSIS and x64

2009-03-03T23:51:00.005+05:30

Here’s something to always keep in mind when Working with Office Excel (or for that matter Office Access) in Integration Services on a 64-bit operating system: You are bound to get errors.

This is because Office Excel and Access files are connected to, from SSIS using Jet drivers, and Jet drivers come only in 32-bit form. A lot of us have taken to using 64-bit OSes such as Vista Ultimate 64-bit or Windows Server 2008 64-bit, what with the prices of memory being so affordable and all that. But running a perfect Integration Services Package (which you had tested and run several times) copied from a 32-bit machine can cause that all-too-familiar sinking feeling in your stomach. Or even building an Integration Services package from scratch like you have always done (in a 32-bit environment) may give you an AcquireConnection call failure (i.e. your package is unable to connect to your Excel file although everything seems alright).

The solution to this is:

If you are running the package in the debug mode (i.e. from BIDS), set the Run64BitRuntime property of the solution to False. This property is set to true by default in x64 environments, hence giving you the error (which is believe me, very frustrating for a first-timer on x64).

If you are running the package directly (from the file system) through the DTExec or DTExecUI utilities you will have to make sure that you run the package using the 32-bit version of the respective utility: The 32-bit version by default can be found in the <drive>:\ Program Files(x86)\Microsoft SQL Server\100\DTS\Binn folder, whereas the 64-bit version can be found be default in the <drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn folder. This, of course is for SQL Server 2008, whereas for SQL Server 2005 you would have to substitute 100 with 90.

So, do make a note to check your 64bit “settings”, the next time you are working with Integration Services and Excel on a 64-bit environment. You may cross paths with errors such as The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009 or The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.

Date of Latest Backup/Restore

2009-02-26T00:40:00.022+05:30

My colleague who is a developer usually comes up with requests for little bits and pieces of database related information. This time, it was a request for the latest date that a particular database had been restored from a backup. T’was nothing but 10 minutes of BOL and coding, but lot of people out there might find it quite useful on some occasions. So here we go, the first in the series of Not-So-Hip-Snippets-Yet-Useful-At-Times...

A list of database on your instance are stored in the sys.databases table in the master database, while the backup and restore information is stored in the msdb database on several tables:

For backups: dbo.backupset, dbo.backupmediaset, dbo.backupmediafamily
For restores: dbo.restorehistory, dbo.restorefile, dbo.restorefilegroup

The database ID is not maintained in the listed tables, hence we would need to join the the sys.databases table with the required tables using the database names. This is the code to get a list of the latest backups performed against your databases:

SELECT
d.[name] AS 'Database Name',
ISNULL(CONVERT(varchar, MAX(b.[backup_finish_date]), 109), 'No backups') AS 'Last Backed up Date/Time',
ISNULL(b.[user_name], 'N/A') AS 'User', ISNULL(b.[type], '-') AS 'Backup Type'
FROM
sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
GROUP BY
d.[name], b.[user_name], b.[type]

While this is the code to get a list of the latest restore operations performed against your databases:

SELECT
d.[name],
ISNULL(CONVERT(varchar, MAX(r.[restore_date]), 109), 'No restores') AS 'Last Restored up Date/Time',
ISNULL(r.[user_name], 'N/A') AS 'User',
ISNULL(r.[restore_type], '-') AS  'Backup Type'
FROM
sys.databases d
LEFT OUTER JOIN msdb.dbo.restorehistory r ON d.[name] = r.[destination_database_name]
GROUP BY
d.[name], r.[user_name], r.[restore_type]

You could stuff more details into this code, from the tables listed above to suit your requirements.

Office Excel 2007 and SSIS

2008-04-11T15:41:00.007+05:30

Most of us know how to make use of an Office Excel file in an SSIS package. As long as you use an Excel file with the .xls extension (i.e. Office 3.0, 4.0, 5.0 or Office 97 - 2003 format) you'd be fine. But, when it comes to Office Excel 2007 you'd want to do it differently. The Connection manager for Excel files option that you would usually choose would not work.

Since Office Excel 2007 and its .xlsx extension came long after SQL Server 2005 and since this new format is entirely different from the previous formats we had a little problem.

This was sorted out with Microsoft releasing the infamous Service Pack 2 (infamous 'cos it had some other issues). Anyway this Service Pack gave us a new driver which could be used for Office Excel 2007 (a.k.a. Excel 12.0) files.

I have briefed below the steps used for creating a connection to an Excel 12.0 file.

In a new or existing package,

Add a New Connection and choose the appropriate connection manager type (ADO.NET or OLEDB) by right-clicking on the Connection Managers section.
This will pop up the appropriate Configuration Connection Manager box.
Click on New... and under the Provider drop-down, select Microsoft Office 12.0 Access Database Engine OLE DB Provider (yes, it is made to be used with Excel files as well).

Click on the All tab of the tab strip located on the left, and type the following against the Extended Properties property: "Excel 12.0".

Go back to the Connections tab and type in the full file path of the Excel 2007 file.

And you're done! Remember; when you use the connection manager within a Data Flow task, use an OLEDB Source or Destination instead of an Excel Source or Destination.

The hierarchyid data type

2008-03-24T11:58:00.005+05:30

I have published my article titled SQL Server 2008 - The hierarchyid data type, on SQLServerUniverse.com.

Just to let you know, the hierarchyid data type is a CLR data type introduced in SQL Server 2008, and is used for storing hierarchical data. Unlike the parent/child approach which maintains a relationship among the records, this approach maintains the level and the position within the level to keep track of the records' hierarchy.

So, go ahead read, please my article and let me know on your thoughts.

The Case of the Uninstallable CTP

2008-03-11T13:25:00.002+05:30

SQL Server 2008 CTP - February 2008 comes with Windows PowerShell. PowerShell is installed as a prerequisite. So one fine day when I decided to install the CTP on my Vista Ultimate SP1 box, since I had just rebuilt the PC from scratch, I was faced with an error while running the setup. The thing which puzzled me was that I had been able to successfully setup the same CTP on Vista Ultimate SP1 RC before (i.e. before rebuilding the PC). The error that pops up is this:

This led me to do some sleuthing around as any one of us would do: Located the PowerShell folder within the CTP disc. PowerShell comes in the form of an .msu (Microsoft System Update) file for Windows Vista. Double-clicking on it to manually install PowerShell resulted in this error:

The real problem can be identified only here. There was some service which was not started up, in order to execute the PowerShell msu file. So which service is responsible for running Windows updates? Windows Update, of course. And true enough, Windows Update was disabled. Enabling it and starting it allowed the SQL Server installation to go through uninterrupted.

So, always remember to make sure that the Windows Update service is started up before setting up SQL Server 2008. Not quite sure what would happen on Windows Server or Windows XP though, since they don't use msu files...

Under a Spell

2007-06-07T10:32:00.001+05:30

What's up with QA at Microsoft these days? I was trying to setup a Back Up Database task in an SSIS package when I caught something from the corner of my eye. Check out the name of the highlighted property:

Should we send the Wizard back to Hogwarts?

2007-06-01T15:16:00.000+05:30

The SQL Server Import and Export Wizard in SQL Server 2005 is a handy tool to quickly transfer data from one data source to another, though on most occasions I use it to transfer data between two SQL Server instances. During these data transfer sessions, I always could not connect to a particular SQL Server instance using SQL authentication, though I could connect to it throught SSMS without any worries. The only difference between this particular SQL Server and the others was that, this one had the password as P@ssW0r;D. Whereas the others had simple passwords; either blip or bleep. So where would the problem lie?

Well you see, the Import and Export wizard in its internals uses SSIS, and within that it uses an OLEDB connection. An as usual an OLEDB connection uses a connection string, and when the password is given in the connection string... all hell breaks lose (if I were to exaggerate a little). Why you ask? Because the connection string would now have an extra ';' in its midst:
Data Source=SERVERX;User ID=sa;Password=P@ssW0r;D;Initial Catalog=DBX

To correct this problem, I simply included double quotes at the beginning and at the end of the password in the password field of the wizard.

This worked like a charm, since the connection string in the internal workings of the wizard would now look something like this:
Data Source=SERVERX;User ID=sa;Password="P@ssW0r;D"Initial Catalog=DBX

Now, this got me thinking: "What if my password contained a semi-colon (;) and a double-quote (") ?"
Answer: I didn't want to find out.

Come Undone

2007-04-22T19:31:00.001+05:30

Recently I was working on some ETL packages to transfer some flat file information to a SQL Server 2005 staging table. The particular package that I was working required removing some header and trailing records and then transferring the rest of the data. Some of the trailing records can only be identified by a particular junk character that appeared towards the end of the file. This resulted in me checking for this character in a Script Task in the SSIS package and then removing the line, among other things. The code for this header and trailer removing phase was around 40 to 50 lines long. The problem was the script never executed. And when I went back to check the script the entire code in the Script Task was missing! (As if a technical David Copperfield had waved his wand over my code). I rewrote the code twice over, tried saving the entire project and all kinds of silly things, yet it wouldn't keep my code... Commenting line by line wouldn't work either.

Only removing line by line showed me the perpetrator:
If str.Contains("?") Then 'ASCII Code 26 (Some fonts show it using a 'question mark' while others show a 'box')

So, myself goes and copies this character to the command prompt to see what this character actual is (This is because several different junk characters are denoted using "?"). Guess what showed up on the command prompt:
^Z (a.k.a Undo).

Which meant, every time I saved and closed the script, the whole set of code comes undone! A quick word of admonishment to myself for not using proper standards, I quickly rewrite the script once again, but this time using the ASCII value of the character instead.

UPDATE: The above package was created using SP1 of SQL Server 2005. I just tried out the same thing with SP2, the issue seems partly fixed: The code doesn't disappear when I close the Script Editor Window, but is cleared once I close the entire projects and open it up, unless of course the offending character is removed in its entirety...

The 5 Year Difference

2006-11-02T16:00:00.001+05:30

Data Warehousing/Data Marting nearly always required the need for ETL. And most of the time it was the ETLing of data to create dimensions. And the one type of dimension I have worked with most is the type now known as Slowly Changing Dimension (this term was not used much before the advent of SQL Server 2005, but still).

The ETL tool that I have been using, Data Transformation Services of SQL Server 2000 was a decent enough tool for the purpose. I was quite content with it until; along came SQL Server 2005 carrying with it DTS's new form: SSIS (SQL Server Integration Services). SSIS gives you things which could only be dreamt when working with DTS.

Let me now explain this magic through, how I would go about ETLing data from the source to a destination using each of the two technologies. First, DTS and then SSIS.

The source is a SQL Server 2000 table of Products already arranged in a parent-child hierarchy, and looking like this:

The destination for the DTS process will be a SQL Server 2000 table laid out in a structure which would later make up a star schema, like this:

while the destination for the SSIS process will be a SQL Server 2005 table laid out just like the one above, like this:

while the data after the ETL in both cases should look like this:

DTS Steps (SQL Server 2000)

Create a new DTS package under Data Transformation Services in SQL Server Enterprise Manager.
Create two database connections.
- One for the source
- The other for the destination
Add a Data Driven Query Task.
Write a SQL statement to build a nice result set to transfer, under Source tab.
Set the destination
Create a Lookup so that we could check whether the record which is being transfered is a new record or an existing record.
Write an ActiveX script with logic to check if the record exists (using the Lookup created earlier) in the destination and appropriately call the Insert Query or the Update Query. (Takes quite a long time)
Write the Insert Query and the Update Query and set their properties.

SSIS Steps (SQL Server 2005)

Create a new Integration Services project in Business Intelligence Development Studio.
Create two connection managers for the source and destination.
Add a Data Flow Task.
Add a Data Flow Source to the task and set the source to it.
Add a Slowly Changing Dimension object. (The wizard opens up to unveil the magic!)
Write a SQL statement to build a nice result set to transfer.
Continue with the wizard and voila!!! No ActiveX scripts, No lookups to worry about, No Insert Queries nor Update Queries!!! - Your ETL is done! Basically the only thing you wrote was the source query , which again can be built using the Query Builder.

So now, I can build my dimensions quickly and that also without writing any code; just a series of clicks and drags!

To VIEW or not to VIEW

2006-10-26T15:34:00.000+05:30

Imagine this scenario:
An Oracle 9i database with tens of thousands of transactional data, which accumulates during the day. This data was to be ETLed to a Microsoft SQL Server 2000 data mart (in reality, a database), and a dimensional data mart (SQL Server 2000 Analysis Services database) will be then created on top of this relational data mart. All this should happen during the night and should be ready the following morning. That's because; when the day dawns, the decision makers would arrive. And when they do not find the properly updated cubes in the data mart, they would rather slice and dice my... um... donkey!

Now to summarize what we have in the SQL Server 2000 database... We have dimension tables, transaction tables (which are being used for staging purposes) and fact tables (from which the cubes will be created). The dimension tables will be populated first, then the transaction tables and then the fact tables (which would get the data from the transactional data)

Another hitch apart from all the data coming into the cubes before dawn, was that some transactions (i.e. the ones upto the previous 2 months) could change! Yes, you could well imagine my annoyance!

But as I had to follow orders, I came up with a plan to delete transactional data from the current date to upto 2 months before on the transaction and fact tables and then perform the ETL from that point onward. But this would increase the amount of records which I had to ETL every night. But since the process was going ok and the decision makers were able to slice and dice their cubes everyday, I was fine.

The Problem:
The problem started 2 months later, when the number of people who perform transactions started to increase. In the end the entire ETL was crawling on its belly for more than 12 hours!!! There were actually two things affecting this:

The large number of transactions
The under-configured server (I couldn't take the blame alone, could I?)

Coming back to the large number of transactions, I discovered something staggering! The ETL from Oracle to the staging transaction tables was happening quite fast (a little more than 2 hours), but the populating of data onto the fact tables was what was eating it all up! Imagine That! Transferring data from a couple of transactional tables to a fact table (both residing on the same database) taking close to half a day!

Something worth noting is how the transaction table to fact table ETL was designed: Stored Procedures using cursors to lookup around half a dozen dimensions, twice or thrice over.

The Solution
The solution that I implemented was: dropped all the stored procedures, dropped all the fact tables, created views on top of the transaction tables, and made them look exactly like the fact tables. The next step was to fool the cubes into believing the views were actually the fact tables, which was quite simple. Then we changed the server, to match the required configuration. And voila! Man and machine solved the issue. The process now takes 1 1/2 hours tops to finish.

Conclusion:
Which of the solutions actually did the trick? I didn't want to find out. I'd like to believe it was both!