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.

Extremely slow Excel MDX

Using Excel as a client is most of the time exceedingly slow. For example writing a simple query of the type:

SELECT [Measures].[Some Measure] ON 0,
[Product].[Product-Version].[Product] ON 1
FROM [Cubename]

in Management studio is in Excel transformed to:

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Product].[Product-Version].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [Cubename] WHERE ([Measures].[Some Measure])

which takes several times longer to execute. As one starts drilling down it becomes increasingly worse with excel producing MDX that takes 100:s of times longer to execute then if I handwrite the mdx. This is with a very simple cube where Some Measure is not a calculated member. I can't even begin to imagine how slow it would be with a more complex cube. Is there anything to be done about this, any guidelines to follow to make it easer for Excel to generate "normal" mdx?

I had similar problem with Excel and OWC when accessing OLAP Cubes. Unfortunately, unless you optimize your cube, there is nothing can be done with how Excel generate mdx to retrieve data. Excel and OWC is closed code, and recently Microsoft announced that they will be stopping new releases for OWC. Howerver, Excel PivotTable has new version, take a look at Office 2007 in Beta version. It generates more efficient MDXs compared to Office 2003/2000.

Downside, it will take another 2-3 years for Office 2007 to be as popular as Office 2003, so, distribution of your solution in Office 2007 might be an issue if u decide to switch to Office 2007 in larger scale enterprise.

|||Thanks. Is there anything special you have in mind when you say "unless you optimize your cube"? Or do you mean the "ordinary" optimizations one does to make the server work decently fast? I will try to have a look at the 2007 beta though.|||

As mentioned earlier, you can take a look at Office 2007 sending bit different MDX queries. You also take a look at the ProClarity recently aquired by Microsoft see if you get better performance using it.

Also make sure you install latest service pack - SP1. There has been some performance improvements in it. You will see event more performance improvements in upcoming service pack 2. Watch for announcements of Community Technology Preview (CTP) to get your hands on upcoming SP2.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Extracting Month Name from Date

Is there a function for getting the Month name from a date string? For
example, '01/20/2004' would return 'January'I think it's MonthName()
MonthName(getdate()) would return October
"Blake Gremillion" <Blake Gremillion@.discussions.microsoft.com> wrote in
message news:C3E99BBD-C664-4E50-B849-D19B72B12B97@.microsoft.com...
> Is there a function for getting the Month name from a date string? For
> example, '01/20/2004' would return 'January'
>|||DATENAME(MONTH, GATDATE())
"Kelly" <kelly.hauser@.amerock.com> wrote in message
news:ectKTi#qEHA.2764@.TK2MSFTNGP11.phx.gbl...
> I think it's MonthName()
> MonthName(getdate()) would return October
>
> "Blake Gremillion" <Blake Gremillion@.discussions.microsoft.com> wrote in
> message news:C3E99BBD-C664-4E50-B849-D19B72B12B97@.microsoft.com...
> > Is there a function for getting the Month name from a date string? For
> > example, '01/20/2004' would return 'January'
> >
> >
>

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
> >>
>