Facxt Table Example

I need an example that shows how to uses multiple lookups to populate a fact table. The flow goes lilke this...

1. Read a staging table source that has source keys

2. for each source key, perform a lookup on the dimension table and return the surrogate key

3. Insert rows into the fact table with the surrogate keys

This is a standard approach that I've done many times in other ETL tools. However, I can't find any examples on how to get it to work. I have tried stringing the lookups together sequentially and using a multicast to peform the lookups in parallel. Neither approach would work. I could not find any examples on the web or in the SQL2005 samples.

Thanks,

Chris Busch

Blueprint Database

cbusch@.blueprintdatabase.com

Take a look at the samples you can download here:

http://www.msftdwtoolkit.com/ToolsandUtilities/ToolandUtilities.htm

This book is what I'd categorize as a "must have" if you're going to be spending much time with the MS BI stack.

Fact Table/Dimension and Multiple Level Dimension

Hi Guys,

I have two questions on Analysis Service for SQL SERVER 2005:-

a) Is it possible for me to use two tables from my database without Primary Key. As this use to work on my Analysis Service on SQL SERVER 2000.

b) I had a multiple Level Dimension in SQL SERVER 2000 with Key Column as one column in my table and name column as another in my table. I am not able to do the same in SQL SERVER 2005.

Urgent help is requried.

Regards,

Kaushal

a)You do not need primary keys in the source data base but I think you need to set logical primary keys in the data source view.

b)Key and name columns are still separated in Analysis Services 2005.

Regards

Thomas Ivarsson

|||

Hi Thomas,

Thanks for Reply.

a) Yes i do understand that, but then what happens if i want to use the columns used in Primary key as levels in a Dimension?

b) I am still not understanding how to take kare of Key and Name Columns?

Regards,

Kaushal

Fact table granularity

I have a fact table: FactVoyage at a "Voyage" level of granularity. Each "Voyage" involves visits to multiple "Voyage Ports" so the FactVoyagePort table will be at Port level of granularity. Voyage to VoyagePort = 1 to many.

I'm trying to use the Dimensional Model for datawarehouse, so my understanding is that each FactTable should connect to many Dimension tables but not to other Fact tables as this would effectively become a relational model. However many of the dimensions we want to include in FactVoyagePort table are the same as those in FactVoyage, so I'm tempted to join FactVoyagePort to FactVoyage using VoyageID column in both tables, and depend on the dimensions in FactVoyage instead of repeating them in FactVoyagePort . This means in order to create the cube for VoyagePorts I will need to include both Fact tables in the DSV.

If my understanding of Dimensional model is correct I might regret this later on, but if I fully denormalize here I'm concerned about errors arising from generating the same data twice at the ETL level.

Any comments ?

Thanks

Richard

Not sure where the concern about generating data twice comes in. I would recommend against joining the facts. However, in the ETL, you might use one fact to retrieve the dimensions for the other fact, since you will have already performed any necessary lookups.|||

Thanks! I'll try using one fact to populate the other fact at ETL stage as you suggested.

Richard

Extracting multiple values as comma seperated

Hi all,

This is my table :

WorkstationNo UserID

101 a1

102 a2

103 a3

101 a2

and there are many other fields too. The output I need is something like this

WorsktationNo OccupiedBy

101 a1,a2

102 a2

103 a3

In the similar fashion I would also require to retrieve the values based on the UserID something like this

UserID Workstations

a1 101

a2 101,102

a3 103

Could someone tell me how to write the query for this.

Here ya go

Code Snippet

create table #t (WorkstationNo int, UserID varchar(10) )

insert into #t

select 101, 'a1'

union all select 102, 'a2'

union all select 103, 'a3'

union all select 101, 'a2'

select WorkstationNo,

substring( ( SELECT ', ' + UserID AS [text()]

FROM #t t2

WHERE t1.WorkstationNo = t2.WorkstationNo

FOR XML path(''), elements ), 2, 1000) as OccupiedBy

from #t t1

group by WorkstationNo

select UserID,

substring( ( SELECT ', ' + convert( varchar(10), WorkstationNo ) AS [text()]

FROM #t t2

WHERE t1.UserID = t2.UserID

FOR XML path(''), elements ), 2, 1000) as OccupiedBy

from #t t1

group by UserID

|||

Thanks DaleJ..

worked like a charm. I guess i will take time to understand the code...

extracting from a string

I am trying to extract data within the first set of parenthesis in a string
that often times have multiple sets of data ..for example
(22)(223)(45) I just want the (22) or
(234)(12)...here just need the (234)
I see lots of text help on lengths but none that address a specified
character beginning and ending...
Thanks for any assistanceTry:
declare @.str varchar(100)
set @.str = '(22)(223)(45)'
select
substring (@.str, charindex ('(', @.str), charindex (')', @.str))
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
I am trying to extract data within the first set of parenthesis in a string
that often times have multiple sets of data ..for example
(22)(223)(45) I just want the (22) or
(234)(12)...here just need the (234)
I see lots of text help on lengths but none that address a specified
character beginning and ending...
Thanks for any assistance|||Tom, here is my statement with you syntax...all I am getting is (22)
declare @.str varchar(100)
set @.str = '(22)(223)(45)'
select dbo.products_ourproducts_products.folderlist,
substring (@.str, charindex ('(', @.str), charindex (')', @.str))
from dbo.products_ourproducts_products
"Tom Moreau" wrote:
> Try:
> declare @.str varchar(100)
> set @.str = '(22)(223)(45)'
> select
> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
> I am trying to extract data within the first set of parenthesis in a string
> that often times have multiple sets of data ..for example
> (22)(223)(45) I just want the (22) or
> (234)(12)...here just need the (234)
> I see lots of text help on lengths but none that address a specified
> character beginning and ending...
> Thanks for any assistance
>
>|||Tom provided an example of the operation using a @.string variable. The
idea was that you would apply that expression to YOUR data. So, if
dbo.products_ourproducts_products.folderlist is the column from which
you want to extract the first item, it would be:
SELECT substring (X.folderlist,
charindex ('(', X.folderlist),
charindex (')', X.folderlist))
FROM dbo.products_ourproducts_products as X
All I did was replace @.str with your column reference. (And assign an
alias to make things more readable.)
Roy Harvey
Beacon Falls, CT
On Wed, 30 Apr 2008 13:59:05 -0700, Gerry M
<GerryM@.discussions.microsoft.com> wrote:
>Tom, here is my statement with you syntax...all I am getting is (22)
>declare @.str varchar(100)
> set @.str = '(22)(223)(45)'
> select dbo.products_ourproducts_products.folderlist,
> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
> from dbo.products_ourproducts_products
>
>"Tom Moreau" wrote:
>> Try:
>> declare @.str varchar(100)
>> set @.str = '(22)(223)(45)'
>> select
>> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
>> I am trying to extract data within the first set of parenthesis in a string
>> that often times have multiple sets of data ..for example
>> (22)(223)(45) I just want the (22) or
>> (234)(12)...here just need the (234)
>> I see lots of text help on lengths but none that address a specified
>> character beginning and ending...
>> Thanks for any assistance
>>|||Tom, I might not be explaing very well, I have thousands of records and in
this field (folderlist)I have many different values...I only want the first
value in the field enclosed in parenthesis...some examples of the data are as
follows
(22)(223)(45) I want the (22)
(272)(24423) I want the (222)
(6) I want the (6)
(3422)(223)(45) I want the (3422)
Thanks
"Tom Moreau" wrote:
> Try:
> declare @.str varchar(100)
> set @.str = '(22)(223)(45)'
> select
> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
> I am trying to extract data within the first set of parenthesis in a string
> that often times have multiple sets of data ..for example
> (22)(223)(45) I just want the (22) or
> (234)(12)...here just need the (234)
> I see lots of text help on lengths but none that address a specified
> character beginning and ending...
> Thanks for any assistance
>
>|||Thank you, that worked fine.
"Roy Harvey (SQL Server MVP)" wrote:
> Tom provided an example of the operation using a @.string variable. The
> idea was that you would apply that expression to YOUR data. So, if
> dbo.products_ourproducts_products.folderlist is the column from which
> you want to extract the first item, it would be:
> SELECT substring (X.folderlist,
> charindex ('(', X.folderlist),
> charindex (')', X.folderlist))
> FROM dbo.products_ourproducts_products as X
> All I did was replace @.str with your column reference. (And assign an
> alias to make things more readable.)
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Apr 2008 13:59:05 -0700, Gerry M
> <GerryM@.discussions.microsoft.com> wrote:
> >Tom, here is my statement with you syntax...all I am getting is (22)
> >
> >declare @.str varchar(100)
> > set @.str = '(22)(223)(45)'
> >
> > select dbo.products_ourproducts_products.folderlist,
> > substring (@.str, charindex ('(', @.str), charindex (')', @.str))
> >
> > from dbo.products_ourproducts_products
> >
> >
> >"Tom Moreau" wrote:
> >
> >> Try:
> >>
> >> declare @.str varchar(100)
> >> set @.str = '(22)(223)(45)'
> >>
> >> select
> >> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
> >>
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> >> SQL Server MVP
> >> Toronto, ON Canada
> >> https://mvp.support.microsoft.com/profile/Tom.Moreau
> >>
> >>
> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
> >> I am trying to extract data within the first set of parenthesis in a string
> >> that often times have multiple sets of data ..for example
> >>
> >> (22)(223)(45) I just want the (22) or
> >> (234)(12)...here just need the (234)
> >>
> >> I see lots of text help on lengths but none that address a specified
> >> character beginning and ending...
> >>
> >> Thanks for any assistance
> >>
> >>
> >>
>|||I have the value I need from the string, can I now remove the just
parenthesis that enclose it?
"Gerry M" wrote:
> Thank you, that worked fine.
>
> "Roy Harvey (SQL Server MVP)" wrote:
> > Tom provided an example of the operation using a @.string variable. The
> > idea was that you would apply that expression to YOUR data. So, if
> > dbo.products_ourproducts_products.folderlist is the column from which
> > you want to extract the first item, it would be:
> >
> > SELECT substring (X.folderlist,
> > charindex ('(', X.folderlist),
> > charindex (')', X.folderlist))
> > FROM dbo.products_ourproducts_products as X
> >
> > All I did was replace @.str with your column reference. (And assign an
> > alias to make things more readable.)
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> > On Wed, 30 Apr 2008 13:59:05 -0700, Gerry M
> > <GerryM@.discussions.microsoft.com> wrote:
> >
> > >Tom, here is my statement with you syntax...all I am getting is (22)
> > >
> > >declare @.str varchar(100)
> > > set @.str = '(22)(223)(45)'
> > >
> > > select dbo.products_ourproducts_products.folderlist,
> > > substring (@.str, charindex ('(', @.str), charindex (')', @.str))
> > >
> > > from dbo.products_ourproducts_products
> > >
> > >
> > >"Tom Moreau" wrote:
> > >
> > >> Try:
> > >>
> > >> declare @.str varchar(100)
> > >> set @.str = '(22)(223)(45)'
> > >>
> > >> select
> > >> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
> > >>
> > >>
> > >> --
> > >> Tom
> > >>
> > >> ----
> > >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > >> SQL Server MVP
> > >> Toronto, ON Canada
> > >> https://mvp.support.microsoft.com/profile/Tom.Moreau
> > >>
> > >>
> > >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> > >> news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
> > >> I am trying to extract data within the first set of parenthesis in a string
> > >> that often times have multiple sets of data ..for example
> > >>
> > >> (22)(223)(45) I just want the (22) or
> > >> (234)(12)...here just need the (234)
> > >>
> > >> I see lots of text help on lengths but none that address a specified
> > >> character beginning and ending...
> > >>
> > >> Thanks for any assistance
> > >>
> > >>
> > >>
> >|||On Thu, 1 May 2008 06:11:01 -0700, Gerry M
<GerryM@.discussions.microsoft.com> wrote:
>I have the value I need from the string, can I now remove the just
>parenthesis that enclose it?
Here is a version of what Tom originally posted, modified to leave off
the parentheses.
declare @.str varchar(100)
set @.str = '(22)(223)(45)'
select
substring(@.str, 2, charindex (')', @.str) -2)
I trust that you can apply this new expression in place of the old
version.
Roy Harvey
Beacon Falls, CT|||Roy, thanks for your help...I am getting a "Invalid length parameter passed
to the substring function." error with the following syntax..
update dbo.products_ourproducts_products set extratext02=substring(folderlist,2,charindex (')', folderlist) -2)
FROM dbo.products_ourproducts_products
Thanks again...
"Roy Harvey (SQL Server MVP)" wrote:
> On Thu, 1 May 2008 06:11:01 -0700, Gerry M
> <GerryM@.discussions.microsoft.com> wrote:
> >I have the value I need from the string, can I now remove the just
> >parenthesis that enclose it?
> Here is a version of what Tom originally posted, modified to leave off
> the parentheses.
> declare @.str varchar(100)
> set @.str = '(22)(223)(45)'
> select
> substring(@.str, 2, charindex (')', @.str) -2)
> I trust that you can apply this new expression in place of the old
> version.
> Roy Harvey
> Beacon Falls, CT
>|||What do you get when you run the following:
SELECT charindex (')', folderlist),
folderlist
FROM dbo.products_ourproducts_products
WHERE charindex (')', folderlist) < 3
Roy Harvey
Beacon Falls, CT
On Thu, 1 May 2008 10:31:00 -0700, Gerry M
<GerryM@.discussions.microsoft.com> wrote:
>Roy, thanks for your help...I am getting a "Invalid length parameter passed
>to the substring function." error with the following syntax..
>update dbo.products_ourproducts_products set extratext02=>substring(folderlist,2,charindex (')', folderlist) -2)
>FROM dbo.products_ourproducts_products
>Thanks again...
>"Roy Harvey (SQL Server MVP)" wrote:
>> On Thu, 1 May 2008 06:11:01 -0700, Gerry M
>> <GerryM@.discussions.microsoft.com> wrote:
>> >I have the value I need from the string, can I now remove the just
>> >parenthesis that enclose it?
>> Here is a version of what Tom originally posted, modified to leave off
>> the parentheses.
>> declare @.str varchar(100)
>> set @.str = '(22)(223)(45)'
>> select
>> substring(@.str, 2, charindex (')', @.str) -2)
>> I trust that you can apply this new expression in place of the old
>> version.
>> Roy Harvey
>> Beacon Falls, CT|||I get the following 7 records (of a selection of 2100)
(No column name) folderlist
0
0
0
0
0
0
0
"Roy Harvey (SQL Server MVP)" wrote:
> What do you get when you run the following:
> SELECT charindex (')', folderlist),
> folderlist
> FROM dbo.products_ourproducts_products
> WHERE charindex (')', folderlist) < 3
> Roy Harvey
> Beacon Falls, CT
> On Thu, 1 May 2008 10:31:00 -0700, Gerry M
> <GerryM@.discussions.microsoft.com> wrote:
> >Roy, thanks for your help...I am getting a "Invalid length parameter passed
> >to the substring function." error with the following syntax..
> >
> >update dbo.products_ourproducts_products set extratext02=> >substring(folderlist,2,charindex (')', folderlist) -2)
> >FROM dbo.products_ourproducts_products
> >
> >Thanks again...
> >
> >"Roy Harvey (SQL Server MVP)" wrote:
> >
> >> On Thu, 1 May 2008 06:11:01 -0700, Gerry M
> >> <GerryM@.discussions.microsoft.com> wrote:
> >>
> >> >I have the value I need from the string, can I now remove the just
> >> >parenthesis that enclose it?
> >>
> >> Here is a version of what Tom originally posted, modified to leave off
> >> the parentheses.
> >>
> >> declare @.str varchar(100)
> >> set @.str = '(22)(223)(45)'
> >>
> >> select
> >> substring(@.str, 2, charindex (')', @.str) -2)
> >>
> >> I trust that you can apply this new expression in place of the old
> >> version.
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
>

Extracting data from SQL Server to XML documents

Hey guys, here's the situation.
I need to extract data from SQL Server to multiple XML files, on a daily
basis.
What is the best strategy to accomplish this task?
I did try two things already. These method were using stored procedures.
The first one was the use of the System Stored Procedure sp_makewebtask
using template files. This task failed due to a dll error.
My second attempt was using the "bcp" utility. It didn't work because of the
version of SQL Server we are using right now (too old).
Is there any other ways to generate XML from SQL Server? Can we use DTS to
accomplished this task?
Thanks for the help!
If you're using SQL Server 2000, you can use a variety of techniques -
including FOR XML queries, annotated schemas, etc. See SQL Server Books
Online, and install SQLXML 3.0
(http://www.microsoft.com/downloads/d...isplayLang=en.).
For earlier versions, you'd have to write a custom solution - there's no
built-in support for XML.
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
news:%23W6QGyiVFHA.3280@.TK2MSFTNGP09.phx.gbl...
Hey guys, here's the situation.
I need to extract data from SQL Server to multiple XML files, on a daily
basis.
What is the best strategy to accomplish this task?
I did try two things already. These method were using stored procedures.
The first one was the use of the System Stored Procedure sp_makewebtask
using template files. This task failed due to a dll error.
My second attempt was using the "bcp" utility. It didn't work because of the
version of SQL Server we are using right now (too old).
Is there any other ways to generate XML from SQL Server? Can we use DTS to
accomplished this task?
Thanks for the help!
|||thx for the info!
"Graeme Malcolm" <graemem_cm@.hotmail.com> a crit dans le message de
news:u4T2%23UjVFHA.1796@.TK2MSFTNGP15.phx.gbl...
> If you're using SQL Server 2000, you can use a variety of techniques -
> including FOR XML queries, annotated schemas, etc. See SQL Server Books
> Online, and install SQLXML 3.0
>
(http://www.microsoft.com/downloads/d...a154-8e23-47d2
-a033-764259cfb53b&DisplayLang=en.).
> For earlier versions, you'd have to write a custom solution - there's no
> built-in support for XML.
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
> news:%23W6QGyiVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys, here's the situation.
> I need to extract data from SQL Server to multiple XML files, on a daily
> basis.
> What is the best strategy to accomplish this task?
> I did try two things already. These method were using stored procedures.
> The first one was the use of the System Stored Procedure sp_makewebtask
> using template files. This task failed due to a dll error.
> My second attempt was using the "bcp" utility. It didn't work because of
the
> version of SQL Server we are using right now (too old).
> Is there any other ways to generate XML from SQL Server? Can we use DTS to
> accomplished this task?
> Thanks for the help!
>
>

Extracting data from SQL Server to XML documents

Hey guys, here's the situation.
I need to extract data from SQL Server to multiple XML files, on a daily
basis.
What is the best strategy to accomplish this task?
I did try two things already. These method were using stored procedures.
The first one was the use of the System Stored Procedure sp_makewebtask
using template files. This task failed due to a dll error.
My second attempt was using the "bcp" utility. It didn't work because of the
version of SQL Server we are using right now (too old).
Is there any other ways to generate XML from SQL Server? Can we use DTS to
accomplished this task?
Thanks for the help!If you're using SQL Server 2000, you can use a variety of techniques -
including FOR XML queries, annotated schemas, etc. See SQL Server Books
Online, and install SQLXML 3.0
(http://www.microsoft.com/downloads/...DisplayLang=en.).
For earlier versions, you'd have to write a custom solution - there's no
built-in support for XML.
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
news:%23W6QGyiVFHA.3280@.TK2MSFTNGP09.phx.gbl...
Hey guys, here's the situation.
I need to extract data from SQL Server to multiple XML files, on a daily
basis.
What is the best strategy to accomplish this task?
I did try two things already. These method were using stored procedures.
The first one was the use of the System Stored Procedure sp_makewebtask
using template files. This task failed due to a dll error.
My second attempt was using the "bcp" utility. It didn't work because of the
version of SQL Server we are using right now (too old).
Is there any other ways to generate XML from SQL Server? Can we use DTS to
accomplished this task?
Thanks for the help!|||thx for the info!
"Graeme Malcolm" <graemem_cm@.hotmail.com> a crit dans le message de
news:u4T2%23UjVFHA.1796@.TK2MSFTNGP15.phx.gbl...
> If you're using SQL Server 2000, you can use a variety of techniques -
> including FOR XML queries, annotated schemas, etc. See SQL Server Books
> Online, and install SQLXML 3.0
>
(http://www.microsoft.com/downloads/...4a154-8e23-47d2
-a033-764259cfb53b&DisplayLang=en.).
> For earlier versions, you'd have to write a custom solution - there's no
> built-in support for XML.
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
> news:%23W6QGyiVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys, here's the situation.
> I need to extract data from SQL Server to multiple XML files, on a daily
> basis.
> What is the best strategy to accomplish this task?
> I did try two things already. These method were using stored procedures.
> The first one was the use of the System Stored Procedure sp_makewebtask
> using template files. This task failed due to a dll error.
> My second attempt was using the "bcp" utility. It didn't work because of
the
> version of SQL Server we are using right now (too old).
> Is there any other ways to generate XML from SQL Server? Can we use DTS to
> accomplished this task?
> Thanks for the help!
>
>