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
>
>
Showing posts with label types. Show all posts
Showing posts with label types. Show all posts
Wednesday, March 7, 2012
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 table design
I am creating the data model for an insurance related datawarehouse, I have 3 types of Claims that we want to report on:
health, dental and drug. Some of the data the user needs will be in all 3 but others will be in only one or two of the fact tables.
My question is should these 3 types of claims be divided into 3 fact tables or should I combine them leaving the fields that only apply to one of the types null.
Thanks in advance for the help
If I understand correctly you basically are talking about which users can have access to view which type of claims. Is this correct?
If this is your question then I think you can solve via using roles and permissions on the cube. I myself am in the learning process - as my name implies ;), but based on my so far understanding, this should do the trick.
Please let me know if this solves your problem? Kindly post your reply to the newsgroup.
By copy of this mail to the experts, I would like to ask a related question: Is it a good or bad design practice to have multiple fact tables?
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Actually what I am looking for is more of a best practice, is it better to have one fact table that has columns that are null or have multiple fact tables.
|||If you include all measures related to all three types of claims, then your fact table can get huge depending on how much claims activity you process, amount of history, and record width.
If you put them in different fact tables, you can still join them by your degenerate key(primary key -ex. claims number), but at least you won't be wasting space.
-- shache wrote: --
I am creating the data model for an insurance related datawarehouse, I have 3 types of Claims that we want to report on:
health, dental and drug. Some of the data the user needs will be in all 3 but others will be in only one or two of the fact tables.
My question is should these 3 types of claims be divided into 3 fact tables or should I combine them leaving the fields that only apply to one of the types null.
Thanks in advance for the help
|||Hi,
A silly question from me:
Please explain what you mean by "(primary key -ex. claims number)"...the
reason for asking is that I'm new to this... is this the same thing as a
"surrogate key" (another term that I read in an article.
Regards.
> If you include all measures related to all three types of claims, then
> your fact table can get huge depending on how much claims activity you
> process, amount of history, and record width.
> If you put them in different fact tables, you can still join them by
> your degenerate key(primary key -ex. claims number), but at least you
> won't be wasting space.
|||in the fact table, there is no primary key because you have "degenerated' the primary key from the source system. It has ceased to become the primary key in the fact table even though the field still exists. So, in general, a claim number was the primary
key in your claims source system, but now it is just an ordinary field.
In the dimension table, the primary key is no longer valid anymore because a surrogate key is created. The surrogate key is a meangless number. So a customerid was a primary key in your claims system, but now it is not a PK anymore in your dimension table
. You have just a number(surrogate key) to describe your product and that is your new PK.
I'll understand if it is still confusing.
primary key is not the same as surrogate key. primary key is the same as what is called the degenerate key in the fact table. The surrogate key is actually the primary key in the dimension table.
-- Learner wrote: --
Hi,
A silly question from me:
Please explain what you mean by "(primary key -ex. claims number)"...the
reason for asking is that I'm new to this... is this the same thing as a
"surrogate key" (another term that I read in an article.
Regards.
> If you include all measures related to all three types of claims, then
> your fact table can get huge depending on how much claims activity you
> process, amount of history, and record width.
> If you put them in different fact tables, you can still join them by
> your degenerate key(primary key -ex. claims number), but at least you
> won't be wasting space.
health, dental and drug. Some of the data the user needs will be in all 3 but others will be in only one or two of the fact tables.
My question is should these 3 types of claims be divided into 3 fact tables or should I combine them leaving the fields that only apply to one of the types null.
Thanks in advance for the help
If I understand correctly you basically are talking about which users can have access to view which type of claims. Is this correct?
If this is your question then I think you can solve via using roles and permissions on the cube. I myself am in the learning process - as my name implies ;), but based on my so far understanding, this should do the trick.
Please let me know if this solves your problem? Kindly post your reply to the newsgroup.
By copy of this mail to the experts, I would like to ask a related question: Is it a good or bad design practice to have multiple fact tables?
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Actually what I am looking for is more of a best practice, is it better to have one fact table that has columns that are null or have multiple fact tables.
|||If you include all measures related to all three types of claims, then your fact table can get huge depending on how much claims activity you process, amount of history, and record width.
If you put them in different fact tables, you can still join them by your degenerate key(primary key -ex. claims number), but at least you won't be wasting space.
-- shache wrote: --
I am creating the data model for an insurance related datawarehouse, I have 3 types of Claims that we want to report on:
health, dental and drug. Some of the data the user needs will be in all 3 but others will be in only one or two of the fact tables.
My question is should these 3 types of claims be divided into 3 fact tables or should I combine them leaving the fields that only apply to one of the types null.
Thanks in advance for the help
|||Hi,
A silly question from me:
Please explain what you mean by "(primary key -ex. claims number)"...the
reason for asking is that I'm new to this... is this the same thing as a
"surrogate key" (another term that I read in an article.
Regards.
> If you include all measures related to all three types of claims, then
> your fact table can get huge depending on how much claims activity you
> process, amount of history, and record width.
> If you put them in different fact tables, you can still join them by
> your degenerate key(primary key -ex. claims number), but at least you
> won't be wasting space.
|||in the fact table, there is no primary key because you have "degenerated' the primary key from the source system. It has ceased to become the primary key in the fact table even though the field still exists. So, in general, a claim number was the primary
key in your claims source system, but now it is just an ordinary field.
In the dimension table, the primary key is no longer valid anymore because a surrogate key is created. The surrogate key is a meangless number. So a customerid was a primary key in your claims system, but now it is not a PK anymore in your dimension table
. You have just a number(surrogate key) to describe your product and that is your new PK.
I'll understand if it is still confusing.
primary key is not the same as surrogate key. primary key is the same as what is called the degenerate key in the fact table. The surrogate key is actually the primary key in the dimension table.
-- Learner wrote: --
Hi,
A silly question from me:
Please explain what you mean by "(primary key -ex. claims number)"...the
reason for asking is that I'm new to this... is this the same thing as a
"surrogate key" (another term that I read in an article.
Regards.
> If you include all measures related to all three types of claims, then
> your fact table can get huge depending on how much claims activity you
> process, amount of history, and record width.
> If you put them in different fact tables, you can still join them by
> your degenerate key(primary key -ex. claims number), but at least you
> won't be wasting space.
Fact table design
I am creating the data model for an insurance related datawarehouse, I have
3 types of Claims that we want to report on:
health, dental and drug. Some of the data the user needs will be in all 3 b
ut others will be in only one or two of the fact tables.
My question is should these 3 types of claims be divided into 3 fact tables
or should I combine them leaving the fields that only apply to one of the ty
pes null.
Thanks in advance for the helpIf I understand correctly you basically are talking about which users can ha
ve access to view which type of claims. Is this correct?
If this is your question then I think you can solve via using roles and perm
issions on the cube. I myself am in the learning process - as my name implie
s ;), but based on my so far understanding, this should do the trick.
Please let me know if this solves your problem? Kindly post your reply to th
e newsgroup.
By copy of this mail to the experts, I would like to ask a related question:
Is it a good or bad design practice to have multiple fact tables?
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||Actually what I am looking for is more of a best practice, is it better to h
ave one fact table that has columns that are null or have multiple fact tabl
es.|||If you include all measures related to all three types of claims, then your
fact table can get huge depending on how much claims activity you process, a
mount of history, and record width.
If you put them in different fact tables, you can still join them by your de
generate key(primary key -ex. claims number), but at least you won't be wast
ing space.
-- shache wrote: --
I am creating the data model for an insurance related datawarehouse, I have
3 types of Claims that we want to report on:
health, dental and drug. Some of the data the user needs will be in all 3 b
ut others will be in only one or two of the fact tables.
My question is should these 3 types of claims be divided into 3 fact tables
or should I combine them leaving the fields that only apply to one of the ty
pes null.
Thanks in advance for the help|||Hi,
A silly question from me:
Please explain what you mean by "(primary key -ex. claims number)"...the
reason for asking is that I'm new to this... is this the same thing as a
"surrogate key" (another term that I read in an article.
Regards.
> If you include all measures related to all three types of claims, then
> your fact table can get huge depending on how much claims activity you
> process, amount of history, and record width.
> If you put them in different fact tables, you can still join them by
> your degenerate key(primary key -ex. claims number), but at least you
> won't be wasting space.|||in the fact table, there is no primary key because you have "degenerated' th
e primary key from the source system. It has ceased to become the primary ke
y in the fact table even though the field still exists. So, in general, a cl
aim number was the primary
key in your claims source system, but now it is just an ordinary field.
In the dimension table, the primary key is no longer valid anymore because a
surrogate key is created. The surrogate key is a meangless number. So a cus
tomerid was a primary key in your claims system, but now it is not a PK anym
ore in your dimension table
. You have just a number(surrogate key) to describe your product and that is
your new PK.
I'll understand if it is still confusing.
primary key is not the same as surrogate key. primary key is the same as wha
t is called the degenerate key in the fact table. The surrogate key is actua
lly the primary key in the dimension table.
-- Learner wrote: --
Hi,
A silly question from me:
Please explain what you mean by "(primary key -ex. claims number)"...the
reason for asking is that I'm new to this... is this the same thing as a
"surrogate key" (another term that I read in an article.
Regards.
> If you include all measures related to all three types of claims, then
> your fact table can get huge depending on how much claims activity you
> process, amount of history, and record width.
> If you put them in different fact tables, you can still join them by
> your degenerate key(primary key -ex. claims number), but at least you
> won't be wasting space.|||There are a number of different characteristics between medical, dental and
RX drugs that facilitate analysis meaningful for management, diagnosis and p
rocedure codes having the largest variation. I would treat all three as sepa
rate facts and use views an
d virtual cubes for those situations where combining the three makes sense.
3 types of Claims that we want to report on:
health, dental and drug. Some of the data the user needs will be in all 3 b
ut others will be in only one or two of the fact tables.
My question is should these 3 types of claims be divided into 3 fact tables
or should I combine them leaving the fields that only apply to one of the ty
pes null.
Thanks in advance for the helpIf I understand correctly you basically are talking about which users can ha
ve access to view which type of claims. Is this correct?
If this is your question then I think you can solve via using roles and perm
issions on the cube. I myself am in the learning process - as my name implie
s ;), but based on my so far understanding, this should do the trick.
Please let me know if this solves your problem? Kindly post your reply to th
e newsgroup.
By copy of this mail to the experts, I would like to ask a related question:
Is it a good or bad design practice to have multiple fact tables?
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||Actually what I am looking for is more of a best practice, is it better to h
ave one fact table that has columns that are null or have multiple fact tabl
es.|||If you include all measures related to all three types of claims, then your
fact table can get huge depending on how much claims activity you process, a
mount of history, and record width.
If you put them in different fact tables, you can still join them by your de
generate key(primary key -ex. claims number), but at least you won't be wast
ing space.
-- shache wrote: --
I am creating the data model for an insurance related datawarehouse, I have
3 types of Claims that we want to report on:
health, dental and drug. Some of the data the user needs will be in all 3 b
ut others will be in only one or two of the fact tables.
My question is should these 3 types of claims be divided into 3 fact tables
or should I combine them leaving the fields that only apply to one of the ty
pes null.
Thanks in advance for the help|||Hi,
A silly question from me:
Please explain what you mean by "(primary key -ex. claims number)"...the
reason for asking is that I'm new to this... is this the same thing as a
"surrogate key" (another term that I read in an article.
Regards.
> If you include all measures related to all three types of claims, then
> your fact table can get huge depending on how much claims activity you
> process, amount of history, and record width.
> If you put them in different fact tables, you can still join them by
> your degenerate key(primary key -ex. claims number), but at least you
> won't be wasting space.|||in the fact table, there is no primary key because you have "degenerated' th
e primary key from the source system. It has ceased to become the primary ke
y in the fact table even though the field still exists. So, in general, a cl
aim number was the primary
key in your claims source system, but now it is just an ordinary field.
In the dimension table, the primary key is no longer valid anymore because a
surrogate key is created. The surrogate key is a meangless number. So a cus
tomerid was a primary key in your claims system, but now it is not a PK anym
ore in your dimension table
. You have just a number(surrogate key) to describe your product and that is
your new PK.
I'll understand if it is still confusing.
primary key is not the same as surrogate key. primary key is the same as wha
t is called the degenerate key in the fact table. The surrogate key is actua
lly the primary key in the dimension table.
-- Learner wrote: --
Hi,
A silly question from me:
Please explain what you mean by "(primary key -ex. claims number)"...the
reason for asking is that I'm new to this... is this the same thing as a
"surrogate key" (another term that I read in an article.
Regards.
> If you include all measures related to all three types of claims, then
> your fact table can get huge depending on how much claims activity you
> process, amount of history, and record width.
> If you put them in different fact tables, you can still join them by
> your degenerate key(primary key -ex. claims number), but at least you
> won't be wasting space.|||There are a number of different characteristics between medical, dental and
RX drugs that facilitate analysis meaningful for management, diagnosis and p
rocedure codes having the largest variation. I would treat all three as sepa
rate facts and use views an
d virtual cubes for those situations where combining the three makes sense.
Friday, February 17, 2012
Extracting data types
How is it possible to
take out all data types ?
Which procedure( function, view)
should I use ?Try:
select [name]
from systypes
AMB
"Alur" wrote:
> How is it possible to
> take out all data types ?
> Which procedure( function, view)
> should I use ?
>|||Also see sp_datatype_info in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Alur" <Alur@.discussions.microsoft.com> wrote in message
news:D4FEECA6-E828-4FE8-8DF6-BCD8BDD94E13@.microsoft.com...
How is it possible to
take out all data types ?
Which procedure( function, view)
should I use ?|||Thank you.|||Thank you.
take out all data types ?
Which procedure( function, view)
should I use ?Try:
select [name]
from systypes
AMB
"Alur" wrote:
> How is it possible to
> take out all data types ?
> Which procedure( function, view)
> should I use ?
>|||Also see sp_datatype_info in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Alur" <Alur@.discussions.microsoft.com> wrote in message
news:D4FEECA6-E828-4FE8-8DF6-BCD8BDD94E13@.microsoft.com...
How is it possible to
take out all data types ?
Which procedure( function, view)
should I use ?|||Thank you.|||Thank you.
Subscribe to:
Posts (Atom)