Lookup current id in Kimball Type II dimension for fact rows

Hi all,

Maybe someone here can help me out: I have a Kimball type II dimension, where i track changes in a hierarchy. Each row has a RowStartDate and RowEndDate property to indicate from when to when a certain row should be used.

Now i want to load facts to that table. So each fact will have a certain date associated with it that i can use to lookup the right Id (a certain SourceId can have mulitiple integer Ids when there are historic changes) and then load the facts.

Is there a building block I can use for that? I could do this with SQL scripts but the client would prefer to have as much as possible done in SSIS. The Lookup transformation will only let me specify an equal (inner join where A=B) join, but i need equal for one column (SourceId) and then >= and <= (RowStart and RowEnd) to find the right row version.

Any ideas? Thanks in advance,

John

That question have been asked before. There are several works arround to that problem, here is one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=406485&SiteID=1

You may want to try a search with 'SSIS range lookup'

|||

That's it, excellent! I was googling on the wrong words.

Thanks,

John

Looking for thoughts on my upgrade strategy

Hello,

We've run upgrade advisor and fixed everything and we're ready to upgrade from sql 2k to sql 2k5. We did find a problem though. Our current sql2k has an instance name of "dbserver". The problem is that we wanted to put CRM 3.0 data on this instance as well, but I guess CRM 3.0 will only install based upon the default instance only. So here is my thoughts and was wondering if someone see any problems on this process.

1. Install another sql2k instance, using the default instance name this time, on the current w2k3 box which holds our current sql2k instance named "dbserver".

2. Once default instance is up and running, transfer over logins, operators.

3. Stop current live instance "dbserver"

4. Use new default sql2k instance and attach all databases from the stopped "dbserver" instance other than master, tempdb, model, msdb.

5. Run the upgrade on default instance, which now has all current databases associated to it, for sql2k5.

6. Uninstall "dbserver" instance.

Anyone see any problems on suggestions? Thanks in advance.

You approach will work but there is a quicker option you could consider. You could install a default instance of SQL Server 2005 and migrate your databases and logins directly over to that new instance instead of going through the intermediate SQL 2000 installation step.

Michelle

Looking for SQL2005 host

I have been having problems with my current host.. and would like to see
about finding one that provides SQL2005 databases
Can anyone suggest some good and reasonably priced hosts that provide
sql2005 databases?
Or maybe a site that gives breakdown by host and features..
--
ASP, SQL2005, DW8 VBScriptExpensive but most certainly worth it IMHO:
http://www.orcsweb.com/hosting/sharedplan.aspx
Good value:
http://serverintellect.com/hosting/tier1.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:Od7RGCBEIHA.4772@.TK2MSFTNGP02.phx.gbl...
>I have been having problems with my current host.. and would like to see
>about finding one that provides SQL2005 databases
> Can anyone suggest some good and reasonably priced hosts that provide
> sql2005 databases?
> Or maybe a site that gives breakdown by host and features..
> --
> ASP, SQL2005, DW8 VBScript
>|||I have found the following..
http://www.webhost4life.com/hosting_nosetup.asp
http://www.websecurestores.com/
http://www.re-invent.com/webhosting.aspx
http://www.alentus.com/hosting/sqlserver.asp
Does anyone have any feedback on either of these
--
ASP, SQL2005, DW8 VBScript
"Daniel" <dan_c@.h.com> wrote in message
news:Od7RGCBEIHA.4772@.TK2MSFTNGP02.phx.gbl...
>I have been having problems with my current host.. and would like to see
>about finding one that provides SQL2005 databases
> Can anyone suggest some good and reasonably priced hosts that provide
> sql2005 databases?
> Or maybe a site that gives breakdown by host and features..
> --
> ASP, SQL2005, DW8 VBScript
>|||You may ask http://www.pronethosting.net/windowshosting.php if they are
going to switch to SQL 2005
MH

Looking for some general feedback on working with SQL, SSIS and SAP

This is less of a specific question and more of a request for for some advice as to possibilities and directions. Here's the current situation. My company is using SAP for its purchasing, inventory, etc. This system is pretty much opaque to me - it's managed by another group within the company, and changes to it go through a complicated approval process. At the same time, the majority of our users, internal and external, are looking at this same data through a more accesible and more user friendly collection of web applications - done in classic ASP, up through ASP.NET 1.1 and 2 - and stored in an assortment of MS-SQL 2000 databases. Data is exchanged between SQL and SAP via DTS packages, some nightly, some run more frequently.

There's some issues here - data is never quite synchronized between the two sides, sometimes the same data must be updated twice, leading to possible data integrity issues, etc. Given that, we're going to be moving to SQL 2005 within the next year or so. From everything I've understood, within that context, there are vastly better ways of dealing with out situation than the way we're currently doing it.

So what I'm looking for is just a general impression of what can be done, with SSIS and SAP. Any approaches that might prove more fruitful, an y pitfalls to watch out for, that sort of thing.

How are you pulling data from SAP to SQL using DTS?

With SQL 2005, we have a preview version of the SAP .NET Data Provider for use within SSIS. You can check it out http://msdn2.microsoft.com/en-us/library/ms141761(SQL.90).aspx

SSIS will also be supported by the upcoming Biztalk R2 Adapter pack - which has adapters for SAP, Oracle & Siebel.

Go to https://connect.microsoft.com/ and look for this adapter pack - you can try out Beta2 around end-July.

|||

Currently we're not exactly pulling data. We're exporting data from SAP to a collection of flat files. Then we have a number of DTS packages that run - most nightly, one every 4 hours - and import the data into our database. Which seems damn clumsy.

Thanks for pointing me towards the .NET Data Provider, which I had a vague notion of, and to the Biztalk beta, which is something I hadn't heard of. I'll have plenty of things to research.