I recently used the copy data base wizard to copy an existing database from SQL7.0 to SQL2000. The copy was in general a success except for the fact that the copied database gives the error "could not open database "MainDB" version 5.25. Upgrade to the latest version. I performaed a detach on the database and then a reattach to force an upgrade but the reattach process fails with the error
Error 3624:
Converting database "MainDB" from version 525 to current version 539.
Database "mainDB" running the upgrade step from verion 525 to vesrion 526.
Location:Upgraddb.cpp:2230
Expression:0
SPID:53
ProcessID:1088
Description:Database upgrade failed due to MovePage
I earlier successfully moved another database between the same two servers without any problems.
Any ideas?
Thanks
JohnTry taking a backup on SQL7 and then restore on the SQL2000 server instead of copying.
--jfp
Showing posts with label fact. Show all posts
Showing posts with label fact. Show all posts
Wednesday, March 21, 2012
Wednesday, March 7, 2012
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-less fact table
hai
can you get give me some ideas for constructing a fact table for dataware
housing.And also about the different types of measures in the fact table
like full additive measures,semi-additive measures and fact-less fact table
vidhyaYour fact table should have measures that you would like to report and
analyze. If you wanted to track sales you might have (simplified):
CustomerID
ProductID
StoreID
SalesAmount
Sales Amount is your measure and it can be totaled up so therefore it is
additive.
Semi-Additive measure can be values such as account balances or inventories
(something that is tracked over time). If you have $100 in your account toda
y
and $50 in your account tomorrow, you don't want to state that the total is
$150.
Here is some info on Semi-Additive (it was written under AS 2000 but the
principles still apply):
http://msdn.microsoft.com/library/d...br />
add2.asp
Fact-less Facts are basically used to identify the occurance of something
that may not have a measure associated. Essentially the existence of the fac
t
row counts as 1.
Here is a good Kimball reference for that:
http://www.dbmsmag.com/9609d05.html
-=Steve
"vidhya" wrote:
> hai
> can you get give me some ideas for constructing a fact table for datawar
e
> housing.And also about the different types of measures in the fact table
> like full additive measures,semi-additive measures and fact-less fact tabl
e
>
> vidhya
>
>
can you get give me some ideas for constructing a fact table for dataware
housing.And also about the different types of measures in the fact table
like full additive measures,semi-additive measures and fact-less fact table
vidhyaYour fact table should have measures that you would like to report and
analyze. If you wanted to track sales you might have (simplified):
CustomerID
ProductID
StoreID
SalesAmount
Sales Amount is your measure and it can be totaled up so therefore it is
additive.
Semi-Additive measure can be values such as account balances or inventories
(something that is tracked over time). If you have $100 in your account toda
y
and $50 in your account tomorrow, you don't want to state that the total is
$150.
Here is some info on Semi-Additive (it was written under AS 2000 but the
principles still apply):
http://msdn.microsoft.com/library/d...br />
add2.asp
Fact-less Facts are basically used to identify the occurance of something
that may not have a measure associated. Essentially the existence of the fac
t
row counts as 1.
Here is a good Kimball reference for that:
http://www.dbmsmag.com/9609d05.html
-=Steve
"vidhya" wrote:
> hai
> can you get give me some ideas for constructing a fact table for datawar
e
> housing.And also about the different types of measures in the fact table
> like full additive measures,semi-additive measures and fact-less fact tabl
e
>
> vidhya
>
>
Factless Fact Table
I have a table that stores all instances of when a company restates their finances. There are a number of other columns that have boolean data types in which I can use to filter. Since the records in this table are based on an event, there really isn't "fact" or numerical data.
When I build my cube in AS2005 using VS2005 and select this table as the fact table, I can't seem to get the other attributes (boolean fields) to appear as filters. Do I have to create a dimension using this table too?
For example, one report would be to display all restatements that had a negative impact (as opposed to a positive impact).
Do I have to create a dimension using this table too?
Yes (well, I guess so)
In my case I have to use it too, and I use it in a "WHERE" clause.
Kinda like:
WHERE [Blah].[True]
or something like that :-)
Fact Tables and Clustered Indexes
A design question:
Say I have a medium-sized fact table w/ a handful of dimension key
columns (including a date) and a handful of measures.
I necessarily need a primary key on the composite dimension key
columns, and I don't know ahead of time which of my dimension key
column(s) will be the best candidate for a clustered index. I do plan
on putting non-clustered indexes on all my dimension key columns, and
the related dimension tables' key columns.
For the sake of argument, let's say we're not partitioning the fact
table.
Assume that new facts occur in time, the fact table grows with time,
and (nearly) all changes to the fact table occur as INSERTs.
Now, all things being equal, is there a benefit of adding a clustered
index to the fact table? Two options:
- Add an IDENTITY column, make it the primary key, and add the
clustered index to it.
- Add the clustered index on the date column, since it has a natural
order.
Basically, I'm after two answers in this scenario:
- Is there a benefit to having a clustered index on a table when the
application doesn't 'really' call for one?
- If so, is it better to add an IDENTITY column (adding size to the
table) or to pick an naturally ordered dimension key? A random key?
The fact's composite key?
Thanks much.
Steven D. Clark
stevec@.clarkdev.com
"Steven Clark" <stevec@.clarkdev.com> wrote in message
news:d7740507.0407100710.2b1644b5@.posting.google.c om...
> Basically, I'm after two answers in this scenario:
> - Is there a benefit to having a clustered index on a table when the
> application doesn't 'really' call for one?
In my opinion, there is never a reason NOT to have one; it's a freebie,
basically, unlike clustered indexes. It doesn't take up any extra disc
space, and you might as well order the data on the disc somehow, rather than
letting the server take care of it... So I always make sure that every table
has one.
> - If so, is it better to add an IDENTITY column (adding size to the
> table) or to pick an naturally ordered dimension key? A random key?
> The fact's composite key?
Some tips for clustered indexes:
- They assist with range queries and grouping. So try to use them for
columns that will be used for those kinds of operations (>, <, BETWEEN, etc,
or make it composite in the same order that you'll be grouping. If you do
composite, order the columns by selectivity, least selective first. This
will create a wider tree, which will result in somewhat quicker grouping.)
- Clustering on a random key is a very bad idea, because it will cause a
lot of page splits, leading to fragmentation. This will slow your data
loads. It will also give you no query benefits at all. So you'll actually
lose on this option.
- Clustering on an IDENTITY key or a DATETIME column that's
automatically set to the date the row is inserted will actually speed up
inserts as it will create a hotspot at the end of the table. So you'll
never have page splits when inserting new data. This can definitely help
speed your data load! Clustering on an IDENTITY will usually not help too
much with queries as, in my experience, most grouping and range operations
don't consider surrogates. Depending on your app, clustering on a DATETIME
as I described can help a lot, as a lot of queries will request data between
two dates, greater than a date, etc.
- Finally, clustering on the composite of your dimensions may be helpful
if you're grouping on them or requesting ranges. However, in the latter
case, remember that a composite index will only be used for searching if the
first column is part of the search criteria, so try to choose one of your
dimensions that will always be searched on (if that exists in your
warehouse).
I hope that answered your questions? Post back if you need some
clarification or further assistance.
|||> It doesn't take up any extra disc space,
That's not true. The clustered index uses the datapages as the leaves, so
that is disk space you use already anyway, but the nodes of the index still
take up extra space on disk. That doesn't mean btw that it is not a good
idea to have a clustered index on every table. In almost all cases the
performance improvement that a clustered index provides more than offsets
the extra disk space used.
Jacco Schalkwijk
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23rDxElvZEHA.2388@.TK2MSFTNGP11.phx.gbl...
> "Steven Clark" <stevec@.clarkdev.com> wrote in message
> news:d7740507.0407100710.2b1644b5@.posting.google.c om...
> In my opinion, there is never a reason NOT to have one; it's a
freebie,
> basically, unlike clustered indexes. It doesn't take up any extra disc
> space, and you might as well order the data on the disc somehow, rather
than
> letting the server take care of it... So I always make sure that every
table
> has one.
>
> Some tips for clustered indexes:
> - They assist with range queries and grouping. So try to use them for
> columns that will be used for those kinds of operations (>, <, BETWEEN,
etc,
> or make it composite in the same order that you'll be grouping. If you do
> composite, order the columns by selectivity, least selective first. This
> will create a wider tree, which will result in somewhat quicker grouping.)
> - Clustering on a random key is a very bad idea, because it will cause
a
> lot of page splits, leading to fragmentation. This will slow your data
> loads. It will also give you no query benefits at all. So you'll
actually
> lose on this option.
> - Clustering on an IDENTITY key or a DATETIME column that's
> automatically set to the date the row is inserted will actually speed up
> inserts as it will create a hotspot at the end of the table. So you'll
> never have page splits when inserting new data. This can definitely help
> speed your data load! Clustering on an IDENTITY will usually not help too
> much with queries as, in my experience, most grouping and range operations
> don't consider surrogates. Depending on your app, clustering on a
DATETIME
> as I described can help a lot, as a lot of queries will request data
between
> two dates, greater than a date, etc.
> - Finally, clustering on the composite of your dimensions may be
helpful
> if you're grouping on them or requesting ranges. However, in the latter
> case, remember that a composite index will only be used for searching if
the
> first column is part of the search criteria, so try to choose one of your
> dimensions that will always be searched on (if that exists in your
> warehouse).
> I hope that answered your questions? Post back if you need some
> clarification or further assistance.
>
>
|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23cIz3GDaEHA.2544@.TK2MSFTNGP10.phx.gbl...
> That's not true. The clustered index uses the datapages as the leaves, so
> that is disk space you use already anyway, but the nodes of the index
still
> take up extra space on disk. That doesn't mean btw that it is not a good
> idea to have a clustered index on every table. In almost all cases the
> performance improvement that a clustered index provides more than offsets
> the extra disk space used.
Thanks for the clarification on that... I also didn't think about fill
factor, which could also create the impression of more disc space being
used.
Say I have a medium-sized fact table w/ a handful of dimension key
columns (including a date) and a handful of measures.
I necessarily need a primary key on the composite dimension key
columns, and I don't know ahead of time which of my dimension key
column(s) will be the best candidate for a clustered index. I do plan
on putting non-clustered indexes on all my dimension key columns, and
the related dimension tables' key columns.
For the sake of argument, let's say we're not partitioning the fact
table.
Assume that new facts occur in time, the fact table grows with time,
and (nearly) all changes to the fact table occur as INSERTs.
Now, all things being equal, is there a benefit of adding a clustered
index to the fact table? Two options:
- Add an IDENTITY column, make it the primary key, and add the
clustered index to it.
- Add the clustered index on the date column, since it has a natural
order.
Basically, I'm after two answers in this scenario:
- Is there a benefit to having a clustered index on a table when the
application doesn't 'really' call for one?
- If so, is it better to add an IDENTITY column (adding size to the
table) or to pick an naturally ordered dimension key? A random key?
The fact's composite key?
Thanks much.
Steven D. Clark
stevec@.clarkdev.com
"Steven Clark" <stevec@.clarkdev.com> wrote in message
news:d7740507.0407100710.2b1644b5@.posting.google.c om...
> Basically, I'm after two answers in this scenario:
> - Is there a benefit to having a clustered index on a table when the
> application doesn't 'really' call for one?
In my opinion, there is never a reason NOT to have one; it's a freebie,
basically, unlike clustered indexes. It doesn't take up any extra disc
space, and you might as well order the data on the disc somehow, rather than
letting the server take care of it... So I always make sure that every table
has one.
> - If so, is it better to add an IDENTITY column (adding size to the
> table) or to pick an naturally ordered dimension key? A random key?
> The fact's composite key?
Some tips for clustered indexes:
- They assist with range queries and grouping. So try to use them for
columns that will be used for those kinds of operations (>, <, BETWEEN, etc,
or make it composite in the same order that you'll be grouping. If you do
composite, order the columns by selectivity, least selective first. This
will create a wider tree, which will result in somewhat quicker grouping.)
- Clustering on a random key is a very bad idea, because it will cause a
lot of page splits, leading to fragmentation. This will slow your data
loads. It will also give you no query benefits at all. So you'll actually
lose on this option.
- Clustering on an IDENTITY key or a DATETIME column that's
automatically set to the date the row is inserted will actually speed up
inserts as it will create a hotspot at the end of the table. So you'll
never have page splits when inserting new data. This can definitely help
speed your data load! Clustering on an IDENTITY will usually not help too
much with queries as, in my experience, most grouping and range operations
don't consider surrogates. Depending on your app, clustering on a DATETIME
as I described can help a lot, as a lot of queries will request data between
two dates, greater than a date, etc.
- Finally, clustering on the composite of your dimensions may be helpful
if you're grouping on them or requesting ranges. However, in the latter
case, remember that a composite index will only be used for searching if the
first column is part of the search criteria, so try to choose one of your
dimensions that will always be searched on (if that exists in your
warehouse).
I hope that answered your questions? Post back if you need some
clarification or further assistance.
|||> It doesn't take up any extra disc space,
That's not true. The clustered index uses the datapages as the leaves, so
that is disk space you use already anyway, but the nodes of the index still
take up extra space on disk. That doesn't mean btw that it is not a good
idea to have a clustered index on every table. In almost all cases the
performance improvement that a clustered index provides more than offsets
the extra disk space used.
Jacco Schalkwijk
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23rDxElvZEHA.2388@.TK2MSFTNGP11.phx.gbl...
> "Steven Clark" <stevec@.clarkdev.com> wrote in message
> news:d7740507.0407100710.2b1644b5@.posting.google.c om...
> In my opinion, there is never a reason NOT to have one; it's a
freebie,
> basically, unlike clustered indexes. It doesn't take up any extra disc
> space, and you might as well order the data on the disc somehow, rather
than
> letting the server take care of it... So I always make sure that every
table
> has one.
>
> Some tips for clustered indexes:
> - They assist with range queries and grouping. So try to use them for
> columns that will be used for those kinds of operations (>, <, BETWEEN,
etc,
> or make it composite in the same order that you'll be grouping. If you do
> composite, order the columns by selectivity, least selective first. This
> will create a wider tree, which will result in somewhat quicker grouping.)
> - Clustering on a random key is a very bad idea, because it will cause
a
> lot of page splits, leading to fragmentation. This will slow your data
> loads. It will also give you no query benefits at all. So you'll
actually
> lose on this option.
> - Clustering on an IDENTITY key or a DATETIME column that's
> automatically set to the date the row is inserted will actually speed up
> inserts as it will create a hotspot at the end of the table. So you'll
> never have page splits when inserting new data. This can definitely help
> speed your data load! Clustering on an IDENTITY will usually not help too
> much with queries as, in my experience, most grouping and range operations
> don't consider surrogates. Depending on your app, clustering on a
DATETIME
> as I described can help a lot, as a lot of queries will request data
between
> two dates, greater than a date, etc.
> - Finally, clustering on the composite of your dimensions may be
helpful
> if you're grouping on them or requesting ranges. However, in the latter
> case, remember that a composite index will only be used for searching if
the
> first column is part of the search criteria, so try to choose one of your
> dimensions that will always be searched on (if that exists in your
> warehouse).
> I hope that answered your questions? Post back if you need some
> clarification or further assistance.
>
>
|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23cIz3GDaEHA.2544@.TK2MSFTNGP10.phx.gbl...
> That's not true. The clustered index uses the datapages as the leaves, so
> that is disk space you use already anyway, but the nodes of the index
still
> take up extra space on disk. That doesn't mean btw that it is not a good
> idea to have a clustered index on every table. In almost all cases the
> performance improvement that a clustered index provides more than offsets
> the extra disk space used.
Thanks for the clarification on that... I also didn't think about fill
factor, which could also create the impression of more disc space being
used.
Fact Tables and Clustered Indexes
A design question:
Say I have a medium-sized fact table w/ a handful of dimension key
columns (including a date) and a handful of measures.
I necessarily need a primary key on the composite dimension key
columns, and I don't know ahead of time which of my dimension key
column(s) will be the best candidate for a clustered index. I do plan
on putting non-clustered indexes on all my dimension key columns, and
the related dimension tables' key columns.
For the sake of argument, let's say we're not partitioning the fact
table.
Assume that new facts occur in time, the fact table grows with time,
and (nearly) all changes to the fact table occur as INSERTs.
Now, all things being equal, is there a benefit of adding a clustered
index to the fact table? Two options:
- Add an IDENTITY column, make it the primary key, and add the
clustered index to it.
- Add the clustered index on the date column, since it has a natural
order.
Basically, I'm after two answers in this scenario:
- Is there a benefit to having a clustered index on a table when the
application doesn't 'really' call for one?
- If so, is it better to add an IDENTITY column (adding size to the
table) or to pick an naturally ordered dimension key? A random key?
The fact's composite key?
Thanks much.
Steven D. Clark
stevec@.clarkdev.com"Steven Clark" <stevec@.clarkdev.com> wrote in message
news:d7740507.0407100710.2b1644b5@.posting.google.com...
> Basically, I'm after two answers in this scenario:
> - Is there a benefit to having a clustered index on a table when the
> application doesn't 'really' call for one?
In my opinion, there is never a reason NOT to have one; it's a freebie,
basically, unlike clustered indexes. It doesn't take up any extra disc
space, and you might as well order the data on the disc somehow, rather than
letting the server take care of it... So I always make sure that every table
has one.
> - If so, is it better to add an IDENTITY column (adding size to the
> table) or to pick an naturally ordered dimension key? A random key?
> The fact's composite key?
Some tips for clustered indexes:
- They assist with range queries and grouping. So try to use them for
columns that will be used for those kinds of operations (>, <, BETWEEN, etc,
or make it composite in the same order that you'll be grouping. If you do
composite, order the columns by selectivity, least selective first. This
will create a wider tree, which will result in somewhat quicker grouping.)
- Clustering on a random key is a very bad idea, because it will cause a
lot of page splits, leading to fragmentation. This will slow your data
loads. It will also give you no query benefits at all. So you'll actually
lose on this option.
- Clustering on an IDENTITY key or a DATETIME column that's
automatically set to the date the row is inserted will actually speed up
inserts as it will create a hotspot at the end of the table. So you'll
never have page splits when inserting new data. This can definitely help
speed your data load! Clustering on an IDENTITY will usually not help too
much with queries as, in my experience, most grouping and range operations
don't consider surrogates. Depending on your app, clustering on a DATETIME
as I described can help a lot, as a lot of queries will request data between
two dates, greater than a date, etc.
- Finally, clustering on the composite of your dimensions may be helpful
if you're grouping on them or requesting ranges. However, in the latter
case, remember that a composite index will only be used for searching if the
first column is part of the search criteria, so try to choose one of your
dimensions that will always be searched on (if that exists in your
warehouse).
I hope that answered your questions? Post back if you need some
clarification or further assistance.|||> It doesn't take up any extra disc space,
That's not true. The clustered index uses the datapages as the leaves, so
that is disk space you use already anyway, but the nodes of the index still
take up extra space on disk. That doesn't mean btw that it is not a good
idea to have a clustered index on every table. In almost all cases the
performance improvement that a clustered index provides more than offsets
the extra disk space used.
Jacco Schalkwijk
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23rDxElvZEHA.2388@.TK2MSFTNGP11.phx.gbl...
> "Steven Clark" <stevec@.clarkdev.com> wrote in message
> news:d7740507.0407100710.2b1644b5@.posting.google.com...
> In my opinion, there is never a reason NOT to have one; it's a
freebie,
> basically, unlike clustered indexes. It doesn't take up any extra disc
> space, and you might as well order the data on the disc somehow, rather
than
> letting the server take care of it... So I always make sure that every
table
> has one.
>
> Some tips for clustered indexes:
> - They assist with range queries and grouping. So try to use them for
> columns that will be used for those kinds of operations (>, <, BETWEEN,
etc,
> or make it composite in the same order that you'll be grouping. If you do
> composite, order the columns by selectivity, least selective first. This
> will create a wider tree, which will result in somewhat quicker grouping.)
> - Clustering on a random key is a very bad idea, because it will cause
a
> lot of page splits, leading to fragmentation. This will slow your data
> loads. It will also give you no query benefits at all. So you'll
actually
> lose on this option.
> - Clustering on an IDENTITY key or a DATETIME column that's
> automatically set to the date the row is inserted will actually speed up
> inserts as it will create a hotspot at the end of the table. So you'll
> never have page splits when inserting new data. This can definitely help
> speed your data load! Clustering on an IDENTITY will usually not help too
> much with queries as, in my experience, most grouping and range operations
> don't consider surrogates. Depending on your app, clustering on a
DATETIME
> as I described can help a lot, as a lot of queries will request data
between
> two dates, greater than a date, etc.
> - Finally, clustering on the composite of your dimensions may be
helpful
> if you're grouping on them or requesting ranges. However, in the latter
> case, remember that a composite index will only be used for searching if
the
> first column is part of the search criteria, so try to choose one of your
> dimensions that will always be searched on (if that exists in your
> warehouse).
> I hope that answered your questions? Post back if you need some
> clarification or further assistance.
>
>|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23cIz3GDaEHA.2544@.TK2MSFTNGP10.phx.gbl...
> That's not true. The clustered index uses the datapages as the leaves, so
> that is disk space you use already anyway, but the nodes of the index
still
> take up extra space on disk. That doesn't mean btw that it is not a good
> idea to have a clustered index on every table. In almost all cases the
> performance improvement that a clustered index provides more than offsets
> the extra disk space used.
Thanks for the clarification on that... I also didn't think about fill
factor, which could also create the impression of more disc space being
used.
Say I have a medium-sized fact table w/ a handful of dimension key
columns (including a date) and a handful of measures.
I necessarily need a primary key on the composite dimension key
columns, and I don't know ahead of time which of my dimension key
column(s) will be the best candidate for a clustered index. I do plan
on putting non-clustered indexes on all my dimension key columns, and
the related dimension tables' key columns.
For the sake of argument, let's say we're not partitioning the fact
table.
Assume that new facts occur in time, the fact table grows with time,
and (nearly) all changes to the fact table occur as INSERTs.
Now, all things being equal, is there a benefit of adding a clustered
index to the fact table? Two options:
- Add an IDENTITY column, make it the primary key, and add the
clustered index to it.
- Add the clustered index on the date column, since it has a natural
order.
Basically, I'm after two answers in this scenario:
- Is there a benefit to having a clustered index on a table when the
application doesn't 'really' call for one?
- If so, is it better to add an IDENTITY column (adding size to the
table) or to pick an naturally ordered dimension key? A random key?
The fact's composite key?
Thanks much.
Steven D. Clark
stevec@.clarkdev.com"Steven Clark" <stevec@.clarkdev.com> wrote in message
news:d7740507.0407100710.2b1644b5@.posting.google.com...
> Basically, I'm after two answers in this scenario:
> - Is there a benefit to having a clustered index on a table when the
> application doesn't 'really' call for one?
In my opinion, there is never a reason NOT to have one; it's a freebie,
basically, unlike clustered indexes. It doesn't take up any extra disc
space, and you might as well order the data on the disc somehow, rather than
letting the server take care of it... So I always make sure that every table
has one.
> - If so, is it better to add an IDENTITY column (adding size to the
> table) or to pick an naturally ordered dimension key? A random key?
> The fact's composite key?
Some tips for clustered indexes:
- They assist with range queries and grouping. So try to use them for
columns that will be used for those kinds of operations (>, <, BETWEEN, etc,
or make it composite in the same order that you'll be grouping. If you do
composite, order the columns by selectivity, least selective first. This
will create a wider tree, which will result in somewhat quicker grouping.)
- Clustering on a random key is a very bad idea, because it will cause a
lot of page splits, leading to fragmentation. This will slow your data
loads. It will also give you no query benefits at all. So you'll actually
lose on this option.
- Clustering on an IDENTITY key or a DATETIME column that's
automatically set to the date the row is inserted will actually speed up
inserts as it will create a hotspot at the end of the table. So you'll
never have page splits when inserting new data. This can definitely help
speed your data load! Clustering on an IDENTITY will usually not help too
much with queries as, in my experience, most grouping and range operations
don't consider surrogates. Depending on your app, clustering on a DATETIME
as I described can help a lot, as a lot of queries will request data between
two dates, greater than a date, etc.
- Finally, clustering on the composite of your dimensions may be helpful
if you're grouping on them or requesting ranges. However, in the latter
case, remember that a composite index will only be used for searching if the
first column is part of the search criteria, so try to choose one of your
dimensions that will always be searched on (if that exists in your
warehouse).
I hope that answered your questions? Post back if you need some
clarification or further assistance.|||> It doesn't take up any extra disc space,
That's not true. The clustered index uses the datapages as the leaves, so
that is disk space you use already anyway, but the nodes of the index still
take up extra space on disk. That doesn't mean btw that it is not a good
idea to have a clustered index on every table. In almost all cases the
performance improvement that a clustered index provides more than offsets
the extra disk space used.
Jacco Schalkwijk
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23rDxElvZEHA.2388@.TK2MSFTNGP11.phx.gbl...
> "Steven Clark" <stevec@.clarkdev.com> wrote in message
> news:d7740507.0407100710.2b1644b5@.posting.google.com...
> In my opinion, there is never a reason NOT to have one; it's a
freebie,
> basically, unlike clustered indexes. It doesn't take up any extra disc
> space, and you might as well order the data on the disc somehow, rather
than
> letting the server take care of it... So I always make sure that every
table
> has one.
>
> Some tips for clustered indexes:
> - They assist with range queries and grouping. So try to use them for
> columns that will be used for those kinds of operations (>, <, BETWEEN,
etc,
> or make it composite in the same order that you'll be grouping. If you do
> composite, order the columns by selectivity, least selective first. This
> will create a wider tree, which will result in somewhat quicker grouping.)
> - Clustering on a random key is a very bad idea, because it will cause
a
> lot of page splits, leading to fragmentation. This will slow your data
> loads. It will also give you no query benefits at all. So you'll
actually
> lose on this option.
> - Clustering on an IDENTITY key or a DATETIME column that's
> automatically set to the date the row is inserted will actually speed up
> inserts as it will create a hotspot at the end of the table. So you'll
> never have page splits when inserting new data. This can definitely help
> speed your data load! Clustering on an IDENTITY will usually not help too
> much with queries as, in my experience, most grouping and range operations
> don't consider surrogates. Depending on your app, clustering on a
DATETIME
> as I described can help a lot, as a lot of queries will request data
between
> two dates, greater than a date, etc.
> - Finally, clustering on the composite of your dimensions may be
helpful
> if you're grouping on them or requesting ranges. However, in the latter
> case, remember that a composite index will only be used for searching if
the
> first column is part of the search criteria, so try to choose one of your
> dimensions that will always be searched on (if that exists in your
> warehouse).
> I hope that answered your questions? Post back if you need some
> clarification or further assistance.
>
>|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23cIz3GDaEHA.2544@.TK2MSFTNGP10.phx.gbl...
> That's not true. The clustered index uses the datapages as the leaves, so
> that is disk space you use already anyway, but the nodes of the index
still
> take up extra space on disk. That doesn't mean btw that it is not a good
> idea to have a clustered index on every table. In almost all cases the
> performance improvement that a clustered index provides more than offsets
> the extra disk space used.
Thanks for the clarification on that... I also didn't think about fill
factor, which could also create the impression of more disc space being
used.
Fact Tables
What are fact tables? Y is it neccessary for Cubes...On 10.08.2006 12:05, somuthomas@.gmail.com wrote:
> What are fact tables? Y is it neccessary for Cubes...
http://en.wikipedia.org/wiki/Fact_table|||Remember the Rubic's Cube puzzle toy? Picture that as your OLAP cube. Each
colored square represents a cell. One row in your fact table becomes a cell
in your cube for a given measure.
It is the lowest level of detail and thus gives the ability to roll-up
values (aggregate) at various dimensional levels.
RDA Corp
Business Intelligence Evangelist Leader
www.rdacorp.com
"somuthomas@.gmail.com" wrote:
> What are fact tables? Y is it neccessary for Cubes...
>
> What are fact tables? Y is it neccessary for Cubes...
http://en.wikipedia.org/wiki/Fact_table|||Remember the Rubic's Cube puzzle toy? Picture that as your OLAP cube. Each
colored square represents a cell. One row in your fact table becomes a cell
in your cube for a given measure.
It is the lowest level of detail and thus gives the ability to roll-up
values (aggregate) at various dimensional levels.
RDA Corp
Business Intelligence Evangelist Leader
www.rdacorp.com
"somuthomas@.gmail.com" wrote:
> What are fact tables? Y is it neccessary for Cubes...
>
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 with Dimensions
Hello:
I'm a newbie to Datawarehousing and using of the concepts of Dimensional
modeling. I read about Kimball's methodology too.
I started using Analysis Services, which is really great. I created tables
with the primary index values.
My scenario is like this.
I should be able to drill down to any of these reports and the details
also. I would like evaluate the sale of tools in different regions during
different times, with different customers, with different tools description
and with different types of purchases(in any fashion), the way users need.
I created 5 different tables with
Tool Description, Region, Time, Purchase Type, Customer.
All tables are linked by Tool number which is unique.
Tool Description has fields such as
Tools Description, Tool Type, Tool Group, Tool Number(The index has
multiple columns to make it unique).
Region - Region, State, Zip Code, Tool_No(Unique Record)
Time - Year,Quarter,Month,Week,Day,Tool_No
Purchase_Type - Purchase_Type(Lease/Sale), Sale_Type, Transaction_Type,
Tool_NO(Index values)
Customer(Customer age, Gender,Tool_No)
I created all these tables and keyed in values manually for my own testing
purposes. I would be populating the values using Stored PRocedures(probably
by DTS) as time goes.
But to create a FACT table with multiple dimensions, the following
questions arose.
Can I create a fact table with the following columns or do I need to add
Tool_No also in the FACT table.
Tool_Description,Year,Purchase_Type,Customer,Total _Sales.
I guess that I need to populate this fact table using T-SQL before
creating a cube .Am I correct? Please do let me know.
In the FACT TAble, do I need to go to the level of granularity of the
Tool_No also.
Once I create a fact table with T-SQL queries, can I go ahead and create a
cube with all 4 - 5 dimensions, the way I would really like to drill it down
to the reports.
Any help is greatly appreciated.
Thanks,
Bobby, i could take a look at your current SQL Server database and OLAp
structures. Send them to me, andI'll see what can I do
Alejandro Leguizamo
SQL Server MVP
Colombia
"Bobbyx2405" <Bobbyx2405@.discussions.microsoft.com> wrote in message
news:6899853C-03EA-490C-9EE1-657E276A85F3@.microsoft.com...
> Hello:
> I'm a newbie to Datawarehousing and using of the concepts of
> Dimensional
> modeling. I read about Kimball's methodology too.
> I started using Analysis Services, which is really great. I created
> tables
> with the primary index values.
> My scenario is like this.
> I should be able to drill down to any of these reports and the details
> also. I would like evaluate the sale of tools in different regions during
> different times, with different customers, with different tools
> description
> and with different types of purchases(in any fashion), the way users need.
> I created 5 different tables with
> Tool Description, Region, Time, Purchase Type, Customer.
> All tables are linked by Tool number which is unique.
> Tool Description has fields such as
> Tools Description, Tool Type, Tool Group, Tool Number(The index has
> multiple columns to make it unique).
> Region - Region, State, Zip Code, Tool_No(Unique Record)
> Time - Year,Quarter,Month,Week,Day,Tool_No
> Purchase_Type - Purchase_Type(Lease/Sale), Sale_Type, Transaction_Type,
> Tool_NO(Index values)
> Customer(Customer age, Gender,Tool_No)
> I created all these tables and keyed in values manually for my own
> testing
> purposes. I would be populating the values using Stored
> PRocedures(probably
> by DTS) as time goes.
> But to create a FACT table with multiple dimensions, the following
> questions arose.
> Can I create a fact table with the following columns or do I need to add
> Tool_No also in the FACT table.
> Tool_Description,Year,Purchase_Type,Customer,Total _Sales.
> I guess that I need to populate this fact table using T-SQL before
> creating a cube .Am I correct? Please do let me know.
> In the FACT TAble, do I need to go to the level of granularity of the
> Tool_No also.
> Once I create a fact table with T-SQL queries, can I go ahead and create
> a
> cube with all 4 - 5 dimensions, the way I would really like to drill it
> down
> to the reports.
>
> Any help is greatly appreciated.
> Thanks,
>
>
I'm a newbie to Datawarehousing and using of the concepts of Dimensional
modeling. I read about Kimball's methodology too.
I started using Analysis Services, which is really great. I created tables
with the primary index values.
My scenario is like this.
I should be able to drill down to any of these reports and the details
also. I would like evaluate the sale of tools in different regions during
different times, with different customers, with different tools description
and with different types of purchases(in any fashion), the way users need.
I created 5 different tables with
Tool Description, Region, Time, Purchase Type, Customer.
All tables are linked by Tool number which is unique.
Tool Description has fields such as
Tools Description, Tool Type, Tool Group, Tool Number(The index has
multiple columns to make it unique).
Region - Region, State, Zip Code, Tool_No(Unique Record)
Time - Year,Quarter,Month,Week,Day,Tool_No
Purchase_Type - Purchase_Type(Lease/Sale), Sale_Type, Transaction_Type,
Tool_NO(Index values)
Customer(Customer age, Gender,Tool_No)
I created all these tables and keyed in values manually for my own testing
purposes. I would be populating the values using Stored PRocedures(probably
by DTS) as time goes.
But to create a FACT table with multiple dimensions, the following
questions arose.
Can I create a fact table with the following columns or do I need to add
Tool_No also in the FACT table.
Tool_Description,Year,Purchase_Type,Customer,Total _Sales.
I guess that I need to populate this fact table using T-SQL before
creating a cube .Am I correct? Please do let me know.
In the FACT TAble, do I need to go to the level of granularity of the
Tool_No also.
Once I create a fact table with T-SQL queries, can I go ahead and create a
cube with all 4 - 5 dimensions, the way I would really like to drill it down
to the reports.
Any help is greatly appreciated.
Thanks,
Bobby, i could take a look at your current SQL Server database and OLAp
structures. Send them to me, andI'll see what can I do
Alejandro Leguizamo
SQL Server MVP
Colombia
"Bobbyx2405" <Bobbyx2405@.discussions.microsoft.com> wrote in message
news:6899853C-03EA-490C-9EE1-657E276A85F3@.microsoft.com...
> Hello:
> I'm a newbie to Datawarehousing and using of the concepts of
> Dimensional
> modeling. I read about Kimball's methodology too.
> I started using Analysis Services, which is really great. I created
> tables
> with the primary index values.
> My scenario is like this.
> I should be able to drill down to any of these reports and the details
> also. I would like evaluate the sale of tools in different regions during
> different times, with different customers, with different tools
> description
> and with different types of purchases(in any fashion), the way users need.
> I created 5 different tables with
> Tool Description, Region, Time, Purchase Type, Customer.
> All tables are linked by Tool number which is unique.
> Tool Description has fields such as
> Tools Description, Tool Type, Tool Group, Tool Number(The index has
> multiple columns to make it unique).
> Region - Region, State, Zip Code, Tool_No(Unique Record)
> Time - Year,Quarter,Month,Week,Day,Tool_No
> Purchase_Type - Purchase_Type(Lease/Sale), Sale_Type, Transaction_Type,
> Tool_NO(Index values)
> Customer(Customer age, Gender,Tool_No)
> I created all these tables and keyed in values manually for my own
> testing
> purposes. I would be populating the values using Stored
> PRocedures(probably
> by DTS) as time goes.
> But to create a FACT table with multiple dimensions, the following
> questions arose.
> Can I create a fact table with the following columns or do I need to add
> Tool_No also in the FACT table.
> Tool_Description,Year,Purchase_Type,Customer,Total _Sales.
> I guess that I need to populate this fact table using T-SQL before
> creating a cube .Am I correct? Please do let me know.
> In the FACT TAble, do I need to go to the level of granularity of the
> Tool_No also.
> Once I create a fact table with T-SQL queries, can I go ahead and create
> a
> cube with all 4 - 5 dimensions, the way I would really like to drill it
> down
> to the reports.
>
> Any help is greatly appreciated.
> Thanks,
>
>
Fact Table with Dimensions
Hello:
I'm a newbie to Datawarehousing and using of the concepts of Dimensional
modeling. I read about Kimball's methodology too.
I started using Analysis Services, which is really great. I created tables
with the primary index values.
My scenario is like this.
I should be able to drill down to any of these reports and the details
also. I would like evaluate the sale of tools in different regions during
different times, with different customers, with different tools description
and with different types of purchases(in any fashion), the way users need.
I created 5 different tables with
Tool Description, Region, Time, Purchase Type, Customer.
All tables are linked by Tool number which is unique.
Tool Description has fields such as
Tools Description, Tool Type, Tool Group, Tool Number(The index has
multiple columns to make it unique).
Region - Region, State, Zip Code, Tool_No(Unique Record)
Time - Year,Quarter,Month,Week,Day,Tool_No
Purchase_Type - Purchase_Type(Lease/Sale), Sale_Type, Transaction_Type,
Tool_NO(Index values)
Customer(Customer age, Gender,Tool_No)
I created all these tables and keyed in values manually for my own testing
purposes. I would be populating the values using Stored PRocedures(probably
by DTS) as time goes.
But to create a FACT table with multiple dimensions, the following
questions arose.
Can I create a fact table with the following columns or do I need to add
Tool_No also in the FACT table.
Tool_Description,Year,Purchase_Type,Cust
omer,Total_Sales.
I guess that I need to populate this fact table using T-SQL before
creating a cube .Am I correct? Please do let me know.
In the FACT TAble, do I need to go to the level of granularity of the
Tool_No also.
Once I create a fact table with T-SQL queries, can I go ahead and create a
cube with all 4 - 5 dimensions, the way I would really like to drill it down
to the reports.
Any help is greatly appreciated.
Thanks,Bobby, i could take a look at your current SQL Server database and OLAp
structures. Send them to me, andI'll see what can I do
Alejandro Leguizamo
SQL Server MVP
Colombia
"Bobbyx2405" <Bobbyx2405@.discussions.microsoft.com> wrote in message
news:6899853C-03EA-490C-9EE1-657E276A85F3@.microsoft.com...
> Hello:
> I'm a newbie to Datawarehousing and using of the concepts of
> Dimensional
> modeling. I read about Kimball's methodology too.
> I started using Analysis Services, which is really great. I created
> tables
> with the primary index values.
> My scenario is like this.
> I should be able to drill down to any of these reports and the details
> also. I would like evaluate the sale of tools in different regions during
> different times, with different customers, with different tools
> description
> and with different types of purchases(in any fashion), the way users need.
> I created 5 different tables with
> Tool Description, Region, Time, Purchase Type, Customer.
> All tables are linked by Tool number which is unique.
> Tool Description has fields such as
> Tools Description, Tool Type, Tool Group, Tool Number(The index has
> multiple columns to make it unique).
> Region - Region, State, Zip Code, Tool_No(Unique Record)
> Time - Year,Quarter,Month,Week,Day,Tool_No
> Purchase_Type - Purchase_Type(Lease/Sale), Sale_Type, Transaction_Type,
> Tool_NO(Index values)
> Customer(Customer age, Gender,Tool_No)
> I created all these tables and keyed in values manually for my own
> testing
> purposes. I would be populating the values using Stored
> PRocedures(probably
> by DTS) as time goes.
> But to create a FACT table with multiple dimensions, the following
> questions arose.
> Can I create a fact table with the following columns or do I need to add
> Tool_No also in the FACT table.
> Tool_Description,Year,Purchase_Type,Cust
omer,Total_Sales.
> I guess that I need to populate this fact table using T-SQL before
> creating a cube .Am I correct? Please do let me know.
> In the FACT TAble, do I need to go to the level of granularity of the
> Tool_No also.
> Once I create a fact table with T-SQL queries, can I go ahead and create
> a
> cube with all 4 - 5 dimensions, the way I would really like to drill it
> down
> to the reports.
>
> Any help is greatly appreciated.
> Thanks,
>
>
I'm a newbie to Datawarehousing and using of the concepts of Dimensional
modeling. I read about Kimball's methodology too.
I started using Analysis Services, which is really great. I created tables
with the primary index values.
My scenario is like this.
I should be able to drill down to any of these reports and the details
also. I would like evaluate the sale of tools in different regions during
different times, with different customers, with different tools description
and with different types of purchases(in any fashion), the way users need.
I created 5 different tables with
Tool Description, Region, Time, Purchase Type, Customer.
All tables are linked by Tool number which is unique.
Tool Description has fields such as
Tools Description, Tool Type, Tool Group, Tool Number(The index has
multiple columns to make it unique).
Region - Region, State, Zip Code, Tool_No(Unique Record)
Time - Year,Quarter,Month,Week,Day,Tool_No
Purchase_Type - Purchase_Type(Lease/Sale), Sale_Type, Transaction_Type,
Tool_NO(Index values)
Customer(Customer age, Gender,Tool_No)
I created all these tables and keyed in values manually for my own testing
purposes. I would be populating the values using Stored PRocedures(probably
by DTS) as time goes.
But to create a FACT table with multiple dimensions, the following
questions arose.
Can I create a fact table with the following columns or do I need to add
Tool_No also in the FACT table.
Tool_Description,Year,Purchase_Type,Cust
omer,Total_Sales.
I guess that I need to populate this fact table using T-SQL before
creating a cube .Am I correct? Please do let me know.
In the FACT TAble, do I need to go to the level of granularity of the
Tool_No also.
Once I create a fact table with T-SQL queries, can I go ahead and create a
cube with all 4 - 5 dimensions, the way I would really like to drill it down
to the reports.
Any help is greatly appreciated.
Thanks,Bobby, i could take a look at your current SQL Server database and OLAp
structures. Send them to me, andI'll see what can I do
Alejandro Leguizamo
SQL Server MVP
Colombia
"Bobbyx2405" <Bobbyx2405@.discussions.microsoft.com> wrote in message
news:6899853C-03EA-490C-9EE1-657E276A85F3@.microsoft.com...
> Hello:
> I'm a newbie to Datawarehousing and using of the concepts of
> Dimensional
> modeling. I read about Kimball's methodology too.
> I started using Analysis Services, which is really great. I created
> tables
> with the primary index values.
> My scenario is like this.
> I should be able to drill down to any of these reports and the details
> also. I would like evaluate the sale of tools in different regions during
> different times, with different customers, with different tools
> description
> and with different types of purchases(in any fashion), the way users need.
> I created 5 different tables with
> Tool Description, Region, Time, Purchase Type, Customer.
> All tables are linked by Tool number which is unique.
> Tool Description has fields such as
> Tools Description, Tool Type, Tool Group, Tool Number(The index has
> multiple columns to make it unique).
> Region - Region, State, Zip Code, Tool_No(Unique Record)
> Time - Year,Quarter,Month,Week,Day,Tool_No
> Purchase_Type - Purchase_Type(Lease/Sale), Sale_Type, Transaction_Type,
> Tool_NO(Index values)
> Customer(Customer age, Gender,Tool_No)
> I created all these tables and keyed in values manually for my own
> testing
> purposes. I would be populating the values using Stored
> PRocedures(probably
> by DTS) as time goes.
> But to create a FACT table with multiple dimensions, the following
> questions arose.
> Can I create a fact table with the following columns or do I need to add
> Tool_No also in the FACT table.
> Tool_Description,Year,Purchase_Type,Cust
omer,Total_Sales.
> I guess that I need to populate this fact table using T-SQL before
> creating a cube .Am I correct? Please do let me know.
> In the FACT TAble, do I need to go to the level of granularity of the
> Tool_No also.
> Once I create a fact table with T-SQL queries, can I go ahead and create
> a
> cube with all 4 - 5 dimensions, the way I would really like to drill it
> down
> to the reports.
>
> Any help is greatly appreciated.
> Thanks,
>
>
Fact Table SQL Query - adding a prefix to a value
Hi there, I have a question regarding a query to extract measures from a fact table. The fact table from the source system contains delivery notes with turnover in one dataset. The primary key is the delivery note number plus a consecutive number. The same table contains also the cancellations of the delivery note with the same turnover and the same primary key as the delivery note, only differed by another consecutive number. The cancellation is represented by another column which contains either a "0" or a "1" (cancellation no/yes).
The problem is that I'd like to change the turnover value for cancellations with a prefix instead of using another column in the fact table. Cause then I'd be able to group those numbers...
Is there a SQL function that allows me to
Example datasets:
Code Snippet
ConsNo DelNNo Canc Turnover Amount
001 200 0 1000 500
002 200 1 1000 500
I'd like to achieve this output by a query:
Code Snippet
ConsNo DelNNo Turnover Amount
001 200 1000 500
002 200 -1000 -500
Any ideas? I've heard of a function called "decode" but I think it doesn't work in SQL Server 2005...
Just create a view that depending on the Canc column multiplies the Turnover and Amount by 1 or -1.|||But how to use IF-statement and mathematical functions within a SELECT-statement? That would be T-SQL right?|||
In SQL you can do something like:
select ConsNo,
DelNNo,
Turnover = case
when Canc = 0
then Turnover
else Turnover * -1
end,
Amount = case
when Canc = 0
then Amount
else Amount * -1
end
from <table name>
There is also the possibility to use isnull and nullif to simulate the previous cases.
|||
Tiago Rente wrote:
In SQL you can do something like:
select ConsNo,
DelNNo,
Turnover = case
when Canc = 0
then Turnover
else Turnover * -1
end,
Amount = case
when Canc = 0
then Amount
else Amount * -1
end
from <table name>
There is also the possibility to use isnull and nullif to simulate the previous cases.
It works, but when I add the GROUP BY function I always get an error message that "Canc" and "Turnover" aren't groupable!
Surprisingly I didn't even select the "Canc"-attribute, and I don't use Turnover within the GROUP-function.
|||Unfortunately with this solution you have to copy the case statement to the group by section. In Sybase IQ you could use the name of the column in the group by without repeting the code.
In SQL Server the other option is to create a User Define Function that receives 2 columns (Canc, Turnover) or (Canc, Amount) and returns the correct value. This way you still need to copy the call to the UDF in the group by, but is less error prune. However, this will cost you in performance, since the UDF will be executed for each row in the table (as if you had open a cursor 
Or you can create a view and then do the group by to the result of the view, this way you do not need to repeat the case or UDF in the group by since you already have a column name to do the group by.
|||
Unfortunately with this solution you have to copy the case statement to the group by section. In Sybase IQ you could use the name of the column in the group by without repeting the code.
In SQL Server the other option is to create a User Define Function that receives 2 columns (Canc, Turnover) or (Canc, Amount) and returns the correct value. This way you still need to copy the call to the UDF in the group by, but is less error prune. However, this will cost you in performance, since the UDF will be executed for each row in the table (as if you had open a cursor ).
Or you can create a view and then do the group by to the result of the view, this way you do not need to repeat the case or UDF in the group by since you already have a column name to do the group by.
I tried to use the case-statement in the GROUP BY function but the result is the same. Weird...
|||Summing the case statements should do the trick.
Code Snippet
select ConsNo,
DelNNo,
Turnover = SUM(case
when Canc = 0
then Turnover
else Turnover * -1
end),
Amount = SUM(case
when Canc = 0
then Amount
else Amount * -1
end)
from <table name>
GROUP BY
ConsNo
, DelNo
Fact Table Optimal Design
I have a working model, but looking to optimize it. Particulary the fact table. Here is how fact table looks:
ProductKey ChargeAKey ChargeBKey ChargeCKey ChargeA$ ChargeB$ ChargeC$ TotalABC$
1 1 0 0 2$ 0 0 2$
1 2 0 0 3$ 0 0 3$
1 0 1 0 0 4$ 0 4$
1 0 0 1 0 0 2$ 2$
1 0 0 2 0 0 3$ 3$
1 0 0 3 0 0 3$ 3$
... now some explanation.. granuality of fact table is Product + ChargeType. each charge type (A, B, C) has it's own dimension table with its own unique list of attributes and they all come from different sources. The simplest loading would append each Product + ChargeX to the table. I was thinking of option b - settingup fact table as Product + ChargeType + ChargeKey + Chargeamount, but this design in my opinion is less friendly then the original because user would have to know what charges have to be filtered down first by type to get to specific key and its attributes... what I don't like about option a is that it takes a lot of space and there are fields populated with only meningless 0 vs having everything compressed into few records. e.g. above exmple could be designed to have only 3 records vs. 6 which translates in huge savings when we are talking millions of records.Would anyone have anyexperience with similiar challenge? I would be able to solve this with complex ETL procedures, but there has to be something much simplier. I am still not sure if this is design or ETL solution... any feedback greatly appreciated.
My general recommendation on dimensional design is to stay focused on your business users. What is the business process, activity, or event this table represents? How would your users describe the facts associated with this process/activity/event?
It appears you have incorporated a dimension (charge type) with three values into your model. If each charge type represents a different event/process/activity, then three fact tables are in order. If this is a single event that could be broken down by charge type (of which there are currently three identified), then a single fact table is required with a charge type dimension. (What happens if a fourth charge type is introduced?)
B.
|||Thank You. That's right I always keep end user in mind because it is for him that data is being presented... and you always think the performance and simplicity. You gave me the answer: "What happens if a fourth charge type is intorduced?".. I will be in a lot of work, if not trouble, to integrate everything into one fact table.. Thanks.
Fact Table Many-to-Many Relationship
Hi
I've just started using SSAS2005 in a health application.
My main Fact table is a Patient Details table with Patient ID as primary key. The cube built around it uses age and gender as dimensions, with median age and Male-Female ratio as some of the measures.
Now in the same Data Source View I've added a Treatment Fact table with multiple treatment rows per Patient ID, each row containing treatment details and the name of the hospital which provided the treatment.
How do I create a dimension or link between these two tables so that in the same cube I can summarize, by hospital, both the number of treatments (and other treatment-related measures and dimensions) as well as the characteristics (age/gender) of patients treated in each hospital? Sounds like a many-to-many relationship but I don't know how to implement it in this case.
Thanks in advance.
You could set up a Patient measure group with a PatientCount measure and a Treatment measure group with a TreatmentCount measure. Age and Gender dimensions are directly related to the Patient measure group, and Hospital to the Treatment measure group. If you then create a PatientDetails fact dimension for the Patient measure group, which is also directly related to the Treatment measure group, the Hospital dimension can have a Many-to-Many relation with the Patient measure group, via the PatientDetails intermediate dimension. You can refer to the Many-to-Many relation of SalesReason to Internet Sales in Adventure Works, for a similar example.|||
Thanks Deepak, that worked. Much appreciated.
FAct TABLE LOOKUPs data
Hi,
Please help me out in loading the fact tables
I had used lookup on DIM table to get my SUK and if I use union transformation to get the out put from each lookup and then loading the data with some condition the data in my fact is not loading in a proper format.
The union transformation is splitting the out put in to different records
Please do inform me about which transformation should be used to get the data from lookup tables.
Or please do inform me the approach to load the fact table in SSIS.
I’m basically INFORMATICA resource and I’m implementing in terms of INFORMATICA
First of all, In SSIS you don't link ports(columns) separately; you link component input and outputs. The Dataflow should look like:
Source Component -->LKP1(get Dim1 SUK) -->LKP2(get Dim2 SUK) -->LKn(getDimn SUK) ...-->Destination Component
Notice you may want to configure the Lookup error output to either redirect or ignore errors as this component treats the no matches as errors.
In the first page of this forum there is a webcast that shows a similar approach:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=534505&SiteID=1
Please post back if something is not clear.
|||Thank you for that information.
please do clarify me on the approach i had taken
the dataflow in my mapping is :
Source Component >LKP1 > LKP2 > Union Component > SCD Component > Destination Component.
i know the union component is a problem in my mapping but instead of union what component do i use. i'm trying to find out this solution.
or if possible give me your approach for fact tale load with lookup components
Thank you
|||Instead of a UNION, try connecting the output from Lookup 1 to Lookup 2 directly. If you do that, you don't need a union.|||
Thank you for that information.
please do clarify me on the approach i had taken
the dataflow in my mapping is :
Source Component >LKP1 > LKP2 > Union Component > SCD Component > Destination Component.
i know the union component is a problem in my mapping but instead of union what component do i use. i'm trying to find out this solution.
or if possible give me your approach for fact tale load with lookup components
Thank you
AS John says, you don't need the UNION all component at all because you have single pipeline; that is the same concept that in Informatica I belive
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
fact table design question
SSAS 2005 - I have the following 3 tables:
T1: dimA_id, dimB_id, dimC_id, prod_id, dollar_amt_1
T2: dimA_id, dimB_id, dimC_id, cat_id, dollar_amt_2
T3: cat_id, prod_id.
cat_id and prod_id has a parent to child relationship.
In this case, I have to build two fact tables, right? There is no way to combine T1 and T2 into one fact table because the dollar_amt 1 and 2 are at a different level of prod_id and cat_id. I just wanted to make sure that I am doing the right thing. It seems that there are repeated data (dimA_id, dimB_id, dimC_id) in both tables.
Thanks.
But if cat_id/prod_id are modelled as a Parent-Child dimension (ie. using a single key for members at all levels, with a parent key), then data could be loaded at both levels from a single fact table.|||Good point. I will give it a try. Would this be a better appoach than the two fact-table one?|||Should be more straightforward, at least - but large parent-child dimensions can cause performance problems. If this dimension is small, it may not be a concern.|||The cat table has more than 1/2 million records and the prod table is 2-3 times bigger than the cat table. Is this considered large?|||According to the AS 2005 Performance Guide, that is large, but you could try it and check:
Parent-child hierarchies
Parent-child hierarchies are hierarchies with a variable number of levels, as determined by a recursive relationship between a child attribute and a parent attribute. Parent-child hierarchies are typically used to represent a financial chart of accounts or an organizational chart. In parent-child hierarchies, aggregations are created only for the key attribute and the top attribute, i.e., the All attribute unless it is disabled. As such, refrain from using parent-child hierarchies that contain large numbers of members at intermediate levels of the hierarchy. Additionally, you should limit the number of parent-child hierarchies in your cube.
If you are in a design scenario with a large parent-child hierarchy (greater than 250,000 members), you may want to consider altering the source schema to re-organize part or all of the hierarchy into a user hierarchy with a fixed number of levels.
fact table design question
I have created a factSales table with dimDate, dimCustomer, dimProduct, dimSalesPerson tables. The dimensions are all joined with surrogate integer identity PK fields which serve as the composite key in the fact table.
It is possible for the same customer to place multiple orders for the same product from the same sales person on the same date. When this happens it seems to me that only the last order will be stored in the fact table. I want to have a row for each order. How would one design a fact table to accomplish this. The only truly unique piece of data from the OLTP is the sales order number.
factSales:
PK DateKey, int
PK CustomerKey, int
PK ProductKey, int
PK SalesPersonKey, int
Amount
UnitCost
Weight
ShippingCost
dimDate
PK DateKey, int, identity
SalesDate
dimCustomer
PK CustomerKey, int, identity
CustomerName
Address
dimProduct
PK ProductKey, int, identity
Product#
ProductName
DimSalesPerson
PK SalesPersonKey, int, idnetity
SalesPersonName
Dept
StartDate
EndDate
Hi,
You'll need to stick another key on the fact to make each row unique. Even if it is something simple like a count
e.g.
FK DateKey, int
FK CustomerKey, int
FK ProductKey, int
FK SalesPersonKey, int
FK OrderNumberOfTheDay, int
measures ...
If possible a time stamp might be another way, I am assuming though that the different orders happen at different times of the day. But basically another key in your ETL process will fix that problem.
Hope that helps,
Matt
|||Thanks Matt. I was going to just add the sales order # as it is unique but I didn't want to violate and conventions that might cause problems down the line.
Thanks again!
|||Hi John
Matt is right in that you should simply bring through the Sales Order Number then your DSV design will take care of the aggregation
Alternatively, unless you need to analyse the data by the Sales Order Number, (which some might argue you should use your OLTP for) you could aggregate the facts in your fact table ETL load process. This can increase performance both in processing the cube and queries if you have a lot of data
If you want to use any drillthrough functionality then keep the Sales Order Number in.
HTH
Tim
|||Thanks Tim.
I had also considered that possibility. My actual OLTP/OLAP is more complex than I showed (I tried to keep it simple for this example). I would have to join several dozen records in order to aggregate the data and I may have a sales order record which does not yet have a ship date which will be the key date slicer. I can load my fact table with a pointer to a 0 date key or just skip SO records without a ship date (my next task to figure out); it would be very complex to aggregate them. Plus I was already storing the SO# in my fact table on the likely probability the users will want to drill back to the original table.
Fact Table Design Question
We're putting together our data warehouse, and I had a questions regarding
design of fact tables for our situation. We have invoices and payments to
those invoices...would I include all information in one fact table, or would
I separate them into two tables? If I do two tables, can I include two fact
tables in an OLAP cube?
Thanks in advance.
You can use a view...
it's even better to use a partitioned view...
Message posted via http://www.sqlmonster.com
|||Hi,
It’s really depending how often you will be analyzing invoices and payments
together.
Sometimes - you can built two cubes and join them into virtual cube.
Very often - you should include all information in your fact table design.
Tomasz B.
"T." wrote:
> We're putting together our data warehouse, and I had a questions regarding
> design of fact tables for our situation. We have invoices and payments to
> those invoices...would I include all information in one fact table, or would
> I separate them into two tables? If I do two tables, can I include two fact
> tables in an OLAP cube?
> Thanks in advance.
>
>
design of fact tables for our situation. We have invoices and payments to
those invoices...would I include all information in one fact table, or would
I separate them into two tables? If I do two tables, can I include two fact
tables in an OLAP cube?
Thanks in advance.
You can use a view...
it's even better to use a partitioned view...
Message posted via http://www.sqlmonster.com
|||Hi,
It’s really depending how often you will be analyzing invoices and payments
together.
Sometimes - you can built two cubes and join them into virtual cube.
Very often - you should include all information in your fact table design.
Tomasz B.
"T." wrote:
> We're putting together our data warehouse, and I had a questions regarding
> design of fact tables for our situation. We have invoices and payments to
> those invoices...would I include all information in one fact table, or would
> I separate them into two tables? If I do two tables, can I include two fact
> tables in an OLAP cube?
> Thanks in advance.
>
>
Fact Table Design Question
We're putting together our data warehouse, and I had a questions regarding
design of fact tables for our situation. We have invoices and payments to
those invoices...would I include all information in one fact table, or would
I separate them into two tables? If I do two tables, can I include two fact
tables in an OLAP cube?
Thanks in advance.You can use a view...
it's even better to use a partitioned view...
Message posted via http://www.droptable.com|||Hi,
It’s really depending how often you will be analyzing invoices and payment
s
together.
Sometimes - you can built two cubes and join them into virtual cube.
Very often - you should include all information in your fact table design.
Tomasz B.
"T." wrote:
> We're putting together our data warehouse, and I had a questions regarding
> design of fact tables for our situation. We have invoices and payments to
> those invoices...would I include all information in one fact table, or wou
ld
> I separate them into two tables? If I do two tables, can I include two fa
ct
> tables in an OLAP cube?
> Thanks in advance.
>
>
design of fact tables for our situation. We have invoices and payments to
those invoices...would I include all information in one fact table, or would
I separate them into two tables? If I do two tables, can I include two fact
tables in an OLAP cube?
Thanks in advance.You can use a view...
it's even better to use a partitioned view...
Message posted via http://www.droptable.com|||Hi,
It’s really depending how often you will be analyzing invoices and payment
s
together.
Sometimes - you can built two cubes and join them into virtual cube.
Very often - you should include all information in your fact table design.
Tomasz B.
"T." wrote:
> We're putting together our data warehouse, and I had a questions regarding
> design of fact tables for our situation. We have invoices and payments to
> those invoices...would I include all information in one fact table, or wou
ld
> I separate them into two tables? If I do two tables, can I include two fa
ct
> tables in an OLAP cube?
> Thanks in advance.
>
>
Fact table design problem
Hello All,
I have a row for each employee, each month in my fact table.
I need to include bonus information. The problem is, for each month a person may have received more than one bonus amount and type in a month. In some cases they have gotten the same bonus twice in the same month. For example:
EmployeeID DateKey Bonus Bonus Type
1 20060105 $5,000 Team
1 20060107 $500 GoodJob
1 20060110 $250 Incentive
How can I have multiple bonuses and their amounts in the fact table if I only have a single row for each employee?
Thank you for the help, this has been driving me crazy.
-Gumbatman
p.s. I've tried stuff with many-to-many dimensions and also Factless Fact tables to get this, but it is just not working for me. I read Mark Russo's very good article about many-to-many dimensions but I am not 100% that it applies to what I am doing.
Hello! I am not sure that I understand the problem fully but have you tried to add a bonus type dimension.
With a bonus type key, an employee key and a date key you should be able to do the analysis you are describing.
All these relations would be one-to-many from the dimension tables.
HTH
Thomas Ivarsson
|||Thomas,
Thank you for responding.
Are you saying keep the Bonus amounts (the facts) in the main Fact table or have a secondary fact table?
If it is in the main fact table, how do I connect the Bonus dimension to the fact table when there are multiple bonuses inside a month (which is the grain of that fact table)?
I tried using a Bonus fact table that connects to the main fact table via a DateKey and EmployeeKey. Then I connect the Bonus Dimension to the Bonus fact table. In order to get the Bonus Dimension to be "seen" by the main fact table, I am using a many-to-many connection.
The problem I am having there is I have a Geography Dimension connected only to the main fact table, not the Bonus fact table. I can't seem to get the Geography Dimension to work properly. What I mean by that is that the Bonus numbers show up for everyone, regardless of Geography.
Please let me know if I am not explaining this clearly. I really appreciate the help (and I really need the help). I've gotten to the point were I am ready to hire a consultant on this issue alone.
-Gumbatman
|||Perhaps you are talking about something not previously known in your first post.
I do not think that you will need two fact tables for this problem. I am thinking about one fact table with one measure like Amount.
The dimension tables will be time, employee and bonustype. If an employee receive the same type of bonus twice on a single day you can aggregate these recordss into one, with Integration Services.
HTH
Thomas Ivarsson
|||Sorry if I didn't mention stuff clearly in my first post. There is a lot to this issue that I may have forgotten to mention.
However, I am curious about your IS suggestion. I can certainly aggregate the amount into the single row. But how do I show that a person received multiple bonuses in that month?
For example, the Bonus Dimension looks like this:
BonusID BonusType BonusAmount
1 Thank You 50
2 Applause 100
3 Encore 250
On a single row, the employe received two Thank Yous and an Applause - a total of 200. Should my Dimension table look like it does? And this prompts another question, can I do a calculation from a Dimension table?
Thank you again for your help.
-Gumbatman
|||Hello. I would move the BonusAmount to the fact table and keep BonusId and BonusType in the dimension table.
You do all calculations in the fact table.
Your single fact table will look like this
EmployeeId
BonusId
BonusDate
BonusAmount
HTH
Thomas Ivarsson
|||Thomas,
Is the Fact table you are suggesting here, a secondary one to the "main" fact table? If so, what is the best way to link the two fact tables, since the data needs to be summarized by month?
If you are suggesting putting the bonus data in the main table, do I just repeat some of the other Dimensions? For example, here is what I am thinking would be my main fact table with bonus information for a single employee in one month:
Since it is additive by month, I have to zero-out the Salary for the additional rows.(At least that is my guess.)
Thank you.
-Gumbatman
|||No! Only one fact table.
I see that you have a salary measure as well and that it will repeat for all bonus records and that the granularity is actually on a month level?
If this is the case I suggest that you make each bonus type a separate measure and aggregate them to the same level as the salary(month?)
So if the same bonus appears several times during a month it will be aggregated into one single bonus type measure.
HTH
Regards
Thomas Ivarsson
|||OK. I think I got it now.
But if I add each bonus as a separate measure in the fact table, what happens if a new bonus type appears?
Thank you.
-Gumbatman
|||It will be a new measure.
HTH
Thomas Ivarsson
|||Sorry, I should have been more specific...
What is the best way to handle a new measure coming into the Fact table, through Integration Services? I guess I check and then append new columns when a new bonus measure is created?
Also, if I do it by measures, what happens to the Bonus Dimension? I will need to slice and dice by the Bonus types. I am unclear about how I can still show who got a what bonus type, especially if there was more than one in that monthly row.
Thank you again (and for your patience).
-Gumbatman
Subscribe to:
Posts (Atom)