Dimensions from Fact tables

Most of the Fact tables I'm building seem to have a bunch of categorization type columns in addition to the numeric columns that naturally fit into the Fact tables. However it feels "wrong" to create dimensions from a Fact table, so instead I'm getting the ETL to split the tables: for every FactX table I get a DimXInfo table that has the non-numeric/non-key data.

E.g.
FactDeal includes dealID, price, quantity, product key, customer key (and other keys that relate to shared dimensions.
DimDealInfo table includes dealID, ContractNumber, DealType

Then in the SSAS cube DimDealInfo naturally forms a Dimension and FactDeal naturally forms a measure group. And you don't have to create a SSAS Dimension from the Fact table.

Question is does this way of doing it make sense. It makes life easier at cube level but at ETL level you're getting two tables that are one to one relationship which also feels kind of "wrong". Any suggestions ?

Yes, in my case it does make sense. I use it with weblogs, and if I want to parse these into a datacube, I need to make keys for every occurence with the global @.@.IDENTITY variable.
Wich I think is really wrong.|||

Degenerate dimensions are not evil!

Indeed there are some business scenario where they are useful and sometimes the only way to model your data.

Using it is a "simple" choice if you have some alternatives (but if you have alternatives maybe you should not use DD!) or a "must" if you haven't.

In my opinion if it seems your unique solution and you don't have performance or other kind of problem (during ETL and process)...why not?

|||

Hello. This is a link to a good article about a method of what to do with fact or degenerate dimensions. It will be appropriate for SSAS2005:

http://www.intelligententerprise.com/000320/webhouse.jhtml

I think that you are on the right track.

HTH

Thomas Ivarsson

|||

Thanks everyone. I think I'm getting closer to the answer now! Yes it is degenerate dimensions I'm dealing with. In some cases I have followed Ralph Kimball's suggestion of a "Junk Dimension" to group together a few apparently unrelated items and get them out of the fact table. This works for items where there are few distinct values. (I think I should move e.g. DealType to a "Junk Dimension")

I guess the remaining item really is "ContractNumber". There is going to be a separate distinct value for every row in the Fact Table. My reasons for taking it out to a separate Dimension table are 1) Remove all text/character based columns from Fact Table to make it run faster and 2) because I need users to be able to drill right down to individual deal level when they browse a cube and the only way I can see to do that is inlcude it as a dimension.

I'm guessing that my reason 1 is not valid because we're only talking one or maybe a few columns, so probably won't have significant performance hit. But what about reason 2, can you some how drill down to a descriptive field of individual row without having to make that field a dimension ? There was something in Analysis Services 2000 that let you view all the rows that made up a specific value but can't see it in SQL 2005 and besides didn't have a client to handle it, so in the end I added ContractNumber as a dimension so any client can drill down to single row level. (Clients I'm using are : SSRS and Excel 2003).

|||

Hello.

Perhaps you are thinking about drillthrough? You can find them under actions in the cube editor.

From a contract number/id you can probably create artifical levels above the single contract by using the first two/three/four characters as levels. It is better to build these levels in a dimension.

You can create these levels in named calculations by using the TSQL function LEFT().

Regards

Thomas Ivarsson

Dimensions and Fact tables

If you have two fact tables that share a dimension is it better to duplicate
the dimension by adding a copy of it to the warehouse or should you share
the single dimension between the fact tables?based on what you are asking - in my opinion i would opt to share the
dimension. trying to keep a second copy in sync seems like it would be added
overhead with very little benefit.
time dimension would be a good example of this - no sense in having multiple
time dimensions.
there are some good books out there on this stuff - look for books from
ralph kimball.
hth
Sal
"Stephen" <swrothfuss@.hotmail.com> wrote in message
news:%23JNCPSVAEHA.392@.TK2MSFTNGP12.phx.gbl...
> If you have two fact tables that share a dimension is it better to
duplicate
> the dimension by adding a copy of it to the warehouse or should you share
> the single dimension between the fact tables?
>

Dimension values without data in a fact table

I have an ODS system and a Data warehouse system
I have a Sales fact table in the ODS system and I have these fields:
SALES
ID_CUSTOMER (PK),
ID_MODEL (PK),
ID_TIME (PK),
SALES,
QUANT_ART,
COST
Then in some records in the fields ID_Time or ID_Model or ID_Customer I
don’t have values (NULL) because in the transactional systems these record
don’t have values (NULL).
The users want to generate aggregate reports with the Sales table...
The question is:
I have to put a “dummy” value in the dimensions Customer, Model and Time
(for example “0”) and put this value in the fact table if the dimensions
fields have NULL values?
Or I have to leave the NULL values?
What is the best choice? Why?
Personally -- and this really does boil down to personal preference -- I
believe that one of the key things that should happen during an ETL is
elimination of all "questionable" data. That includes unknown data -- can
you really report on something that's unknown? At the least, generate
well-known tokens to replace the NULLs with. If possible, get rid of those
rows on the way in (of course, that really depends on context) -- perhaps
they can appear in the aggregate data, but not in the line-level data?
There are various ways of dealing with the problem, but personally I am very
firm when designing data warehouses and ensure that, one way or another,
there will be absolutely no NULLs in the database.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"CLAUDIO" <CLAUDIO@.discussions.microsoft.com> wrote in message
news:937E414E-AB9A-4E4D-8A58-D8251139550E@.microsoft.com...
> I have an ODS system and a Data warehouse system
> I have a Sales fact table in the ODS system and I have these fields:
> SALES
> ID_CUSTOMER (PK),
> ID_MODEL (PK),
> ID_TIME (PK),
> SALES,
> QUANT_ART,
> COST
>
> Then in some records in the fields ID_Time or ID_Model or ID_Customer I
> don't have values (NULL) because in the transactional systems these record
> don't have values (NULL).
> The users want to generate aggregate reports with the Sales table...
> The question is:
> I have to put a "dummy" value in the dimensions Customer, Model and Time
> (for example "0") and put this value in the fact table if the dimensions
> fields have NULL values?
> Or I have to leave the NULL values?
> What is the best choice? Why?
>
sql

Dimension values without data in a fact table

I have an ODS system and a Data warehouse system
I have a Sales fact table in the ODS system and I have these fields:
SALES
ID_CUSTOMER (PK),
ID_MODEL (PK),
ID_TIME (PK),
SALES,
QUANT_ART,
COST
Then in some records in the fields ID_Time or ID_Model or ID_Customer I
don’t have values (NULL) because in the transactional systems these record
don’t have values (NULL).
The users want to generate aggregate reports with the Sales table...
The question is:
I have to put a “dummy” value in the dimensions Customer, Model and Time
(for example “0”) and put this value in the fact table if the dimensions
fields have NULL values'
Or I have to leave the NULL values?
What is the best choice? Why?Personally -- and this really does boil down to personal preference -- I
believe that one of the key things that should happen during an ETL is
elimination of all "questionable" data. That includes unknown data -- can
you really report on something that's unknown? At the least, generate
well-known tokens to replace the NULLs with. If possible, get rid of those
rows on the way in (of course, that really depends on context) -- perhaps
they can appear in the aggregate data, but not in the line-level data?
There are various ways of dealing with the problem, but personally I am very
firm when designing data warehouses and ensure that, one way or another,
there will be absolutely no NULLs in the database.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"CLAUDIO" <CLAUDIO@.discussions.microsoft.com> wrote in message
news:937E414E-AB9A-4E4D-8A58-D8251139550E@.microsoft.com...
> I have an ODS system and a Data warehouse system
> I have a Sales fact table in the ODS system and I have these fields:
> SALES
> ID_CUSTOMER (PK),
> ID_MODEL (PK),
> ID_TIME (PK),
> SALES,
> QUANT_ART,
> COST
>
> Then in some records in the fields ID_Time or ID_Model or ID_Customer I
> don't have values (NULL) because in the transactional systems these record
> don't have values (NULL).
> The users want to generate aggregate reports with the Sales table...
> The question is:
> I have to put a "dummy" value in the dimensions Customer, Model and Time
> (for example "0") and put this value in the fact table if the dimensions
> fields have NULL values'
> Or I have to leave the NULL values?
> What is the best choice? Why?
>

Dimension theory design question.

If I have a sales fact table with 1000 sales to 20 different customers would I have 20 rows in my customer dimension table or 1000 rows? Each sales row has a customer, but many would be duplicates. Do I add a row to the customer dimension for each sale or each customer?

Thanks.

Hello! You will have 20 customer records in your customer dimension table and 1000 records in your sales fact table.

You will have a one to many relation between the customer dimension table and the fact table.

Select Distinct (TSQL) will help you with duplicates in the customer dimension.

HTH

Thomas Ivarsson

|||

Thanks.

To recap: I have an identity integer field for the surrogate key in the dimCustomer table and a business key field. I have two choices for the business key: I can use the CustomerID or the SalesNum from the OLTP. I gather from your response that I should use the CustomerID as the business key. Then when I load the dimension table, if a new sale goes to an existing customer, a new row will NOT be added to the dimCustomer table. When I subsequently load my fact table, I'll use the CustomerID in my sales row to point to the business key in the dimension table and retrieve the surrogate key which will be loaded into the fact table as the foreign key to dimCustomer and part of my aggregate key in the factSales table.

Did I say that right? (There's a whole lot of keys goin on.)

|||

Correct! You will only add customers when the customer business key is not in that dimension table.

Yes, it is good design to use integers, non business keys, as a primary key(dimension table) and foreign key(fact table)

You load the dimension table first and the fact table after and you update the keys in the way you have described.

The fact table will only have the surrogate keys and the dimension table both the business key and the surrogate key.

Regards

Thomas Ivarsson

dimension table population-out of fact table

I have a large flat file that comes to me. I first import the flat data in to a SQL table for ease of use. Then i put it into a more permanent table with the proper references to dimension tables. I want to build a dimension table out of information from my flat file. I have a dimension table with columns, [Org Client], and [Client#] where [org client] is the name of the client. Both of these columns appear in my flat file but i want to use only the client# in my permanent table. How extract distinct values of client # and [org client] into a dimension table?

My idea was to select distinct values of client# and use some type of foreach loop to go through each client# and use a query to select the TOP(1) values of [org client] where client# = x. Would this work and if so how do I go about setting this up?

I'm really hoping there is a simpler way than this. Thank you all for your time.

A sort transformation in a data flow can get rid of duplicates for you. Then from there you can go into whatever table you want.|||That is very impressive. Thank you very much. Works better than i could've imagined.sql

Dimension Properties

I am using SSAS 2005. I have a Dimension table that is based on the entire data source and then 2 Fact tables that set the data into two groups. When I browse the data for Fact Table 1 in SSAS 2005, I am getting missing dimension values for all the data in Fact table 2. Is there a join property that will tell SSAS to only use data from the Dimension table that is also in the Fact table? I have a primary key on the Dimension table that relates the dimension table to the Fact tables.

S

You will probably need to create a view in relational database or named query in DSV to build a join that only brings data you would like to see in Analysis Services.

You should not rely on Analysis Services to solve data integrity problems.

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

Dimension or Fact?

Hi - I've got a dimensional modelling question that's got me stuck.
I have a "Student" entity and a "Student Transaction" entity.
The Student Transaction table will be a fact table -- that's easy.
And the Student will be a dimension of the Student Transaction table.
HOWEVER, the Student dimension is HUGE and has a lot of sub-dimensions
of its own -- things like State, Country, etc. It's basically what
Kimball calls a rapidly changing monster dimension!
First - isn't this snowflaking, and is that bad? Should the Student
be another fact? But then how do I relate the Student to the Student
Transaction if they're both facts.
Also, the student has attributes like test scores and GPA that seem
like measures -- but a dimension can't have a measure. So how could I
get average test scores and gpa, etc...
The other thing I was thinking of doing was treating the Student as
both a dimension AND a fact! Basically create 2 cubes:
Student Transaction cube, which has Student table as a dimension
Student cube, which actually uses the same Student table, but as a
fact.
This seems to work, but it seems awfully weird to use the same
physical table as both a fact and a dimension... even if it is in
different cubes...
IF anyone has insight, i would appreciate it very much!
thanksIn your case Stundent is a Dimension, average, etc are properties of the dim
ension not measures.
It's a better practice to design the dimension with star structure. You can
make a view to group all information an make the dimension based on that vie
w.
Also you can think several cubes that uses Student dimension and time dimens
ion, like test, etc. And then you can calcultate average and other. A way to
join these cubes is with virtual cubes.
Good Luck|||The "badness" of snowflaking is highly overrated. Conversely, the benefits
of denormalized stars are also overrated.
public @. the domain below
www.tomchester.net
"groove_sf" <dockv@.hot-NOSPAM-mail.com> wrote in message
news:cfpu70dvqh0s8c2p1etalnjdrvos9et1mf@.
4ax.com...
> Hi - I've got a dimensional modelling question that's got me stuck.
> I have a "Student" entity and a "Student Transaction" entity.
> The Student Transaction table will be a fact table -- that's easy.
> And the Student will be a dimension of the Student Transaction table.
> HOWEVER, the Student dimension is HUGE and has a lot of sub-dimensions
> of its own -- things like State, Country, etc. It's basically what
> Kimball calls a rapidly changing monster dimension!
> First - isn't this snowflaking, and is that bad? Should the Student
> be another fact? But then how do I relate the Student to the Student
> Transaction if they're both facts.
> Also, the student has attributes like test scores and GPA that seem
> like measures -- but a dimension can't have a measure. So how could I
> get average test scores and gpa, etc...
> The other thing I was thinking of doing was treating the Student as
> both a dimension AND a fact! Basically create 2 cubes:
> Student Transaction cube, which has Student table as a dimension
> Student cube, which actually uses the same Student table, but as a
> fact.
> This seems to work, but it seems awfully weird to use the same
> physical table as both a fact and a dimension... even if it is in
> different cubes...
> IF anyone has insight, i would appreciate it very much!
> thanks
>|||I disagree that the GPA and test scores should be part of the Student dimens
ion. They are semi-additive facts, and should be considered for a separate
star in your constellation (assuming you are using an architected DW with co
nformed dimensions.)
Think about it this way - if you have student transactions as one "business
process", thus is one star center, then GPA and test scores would merit its
own cetricity in a separate fact table, since GPA and test scores are not tr
ansactions, but are "Grades
", though GPA is going to need to be atomized into GPA factoids so it can be
calculated on the fly if you have other dimensions that will be used to sli
ce and dice. For example, you might want GPA by students by a particular ma
jor, or students in the cla
ss of 2005, etc.
Thus you would have Student as a conforming dimension to both a Transactions
fact table and a Grades fact table, thus the need for at least two stars in
your constellation that share the conformed Student dimension.

Dimension or Fact?

Hi - I've got a dimensional modelling question that's got me stuck.
I have a "Student" entity and a "Student Transaction" entity.
The Student Transaction table will be a fact table -- that's easy.
And the Student will be a dimension of the Student Transaction table.
HOWEVER, the Student dimension is HUGE and has a lot of sub-dimensions
of its own -- things like State, Country, etc. It's basically what
Kimball calls a rapidly changing monster dimension!
First - isn't this snowflaking, and is that bad? Should the Student
be another fact? But then how do I relate the Student to the Student
Transaction if they're both facts.
Also, the student has attributes like test scores and GPA that seem
like measures -- but a dimension can't have a measure. So how could I
get average test scores and gpa, etc...
The other thing I was thinking of doing was treating the Student as
both a dimension AND a fact! Basically create 2 cubes:
Student Transaction cube, which has Student table as a dimension
Student cube, which actually uses the same Student table, but as a
fact.
This seems to work, but it seems awfully weird to use the same
physical table as both a fact and a dimension... even if it is in
different cubes...
IF anyone has insight, i would appreciate it very much!
thanks
In your case Stundent is a Dimension, average, etc are properties of the dimension not measures.
It's a better practice to design the dimension with star structure. You can make a view to group all information an make the dimension based on that view.
Also you can think several cubes that uses Student dimension and time dimension, like test, etc. And then you can calcultate average and other. A way to join these cubes is with virtual cubes.
Good Luck
|||The "badness" of snowflaking is highly overrated. Conversely, the benefits
of denormalized stars are also overrated.
public @. the domain below
www.tomchester.net
"groove_sf" <dockv@.hot-NOSPAM-mail.com> wrote in message
news:cfpu70dvqh0s8c2p1etalnjdrvos9et1mf@.4ax.com...
> Hi - I've got a dimensional modelling question that's got me stuck.
> I have a "Student" entity and a "Student Transaction" entity.
> The Student Transaction table will be a fact table -- that's easy.
> And the Student will be a dimension of the Student Transaction table.
> HOWEVER, the Student dimension is HUGE and has a lot of sub-dimensions
> of its own -- things like State, Country, etc. It's basically what
> Kimball calls a rapidly changing monster dimension!
> First - isn't this snowflaking, and is that bad? Should the Student
> be another fact? But then how do I relate the Student to the Student
> Transaction if they're both facts.
> Also, the student has attributes like test scores and GPA that seem
> like measures -- but a dimension can't have a measure. So how could I
> get average test scores and gpa, etc...
> The other thing I was thinking of doing was treating the Student as
> both a dimension AND a fact! Basically create 2 cubes:
> Student Transaction cube, which has Student table as a dimension
> Student cube, which actually uses the same Student table, but as a
> fact.
> This seems to work, but it seems awfully weird to use the same
> physical table as both a fact and a dimension... even if it is in
> different cubes...
> IF anyone has insight, i would appreciate it very much!
> thanks
>
|||I disagree that the GPA and test scores should be part of the Student dimension. They are semi-additive facts, and should be considered for a separate star in your constellation (assuming you are using an architected DW with conformed dimensions.)
Think about it this way - if you have student transactions as one "business process", thus is one star center, then GPA and test scores would merit its own cetricity in a separate fact table, since GPA and test scores are not transactions, but are "Grades
", though GPA is going to need to be atomized into GPA factoids so it can be calculated on the fly if you have other dimensions that will be used to slice and dice. For example, you might want GPA by students by a particular major, or students in the cla
ss of 2005, etc.
Thus you would have Student as a conforming dimension to both a Transactions fact table and a Grades fact table, thus the need for at least two stars in your constellation that share the conformed Student dimension.
sql

Dimension Displayed

Product Dimension Table

ProdID

Prod A

Prod B

Prod C

Fact Table

Key ProdID Measure

1 ProdA 100

2 ProdB 200

When I process the cube the and drag the measure and Product dim to view the result, the result show as below only ProdA and ProdB only.

The result sure correct and no error.

Just dont know why the ProdC not show as expected at SSAS2000.

I try the setting for the "Show Empty Cells" , It just for showing purpose only, not for permenant at that cube.

Anyone know why ProdC not show at the result as permenantly?

Do I need to do any setting to able ProdC show at the result.

Thanks .

This is not controlled by the cube, it is upto the client tool whether or not it includes empty cells or not. SSAS2000 was the same. By default a lot of browsers do not show empty cells and you have to explicitly turn this option on. There is nothing you can set at the database/cube level to control this.

Dimension Design Question

I have a question about designing a dimension.

I have a fact table with 2 bit columns "IsBalance" & "IsAttribution". Possible values are (0 , 1), (1, 0) and (1,1). Pl note that the fact table cannot be changed.

I use SSAS 2005. I had created 2 dimensions (from named queries) called Balance (Members : 1, 0) and Attribution (Members : 1, 0) to implement the above. Both link to fact table directly.

Obviously this works but it is not the 100% correct business representation. The ideal way is to have a dimension called Type and have following members in it ==> Balance, Attribution, Both. Any suggestion on how to acheive this? Or Is there a better way of doing it?

Thanks,

Arun

It sounds like you just need to create a single dimension from a named query which contains the following rows:

DimKey Balance Attribution Both 0 TRUE FALSE FALSE 1 FALSE TRUE FALSE 2 TRUE TRUE TRUE

You can now build the three attributes you want from this table, plus a key attribute from the DimKey column which you can hide.

If the fact table can't be changed, you've got two options: either create a new named calculation on the fact table in the dsv to derive the DimKey value from the two existing columns or use a composite key for the DimKey attribute rather than the single column described above and join the two columns in this composite key to the two existing columns in your fact table.

HTH,

Chris

|||

Thanks Chris.

Sorry I was not clear in my first post.

Is it possible to have an attribute with following members ==> Balance, Attribution, Both from which the users can select either of them to filter the data across ?

The problem here is that when "Balance" member is selected, it should filter data for combination (Balance = 1, Attribution = 0) and (Balance = 1, Attribution = 1) . Same thing should happen for Attribution too.

|||

Ahh, ok. This sounds like a many-to-many relationship: keep the dimension designed in my previous reply but only build a single attribute on it based on the key, and then hide the whole dimension. Then create a dimension containing the single attribute that you want to see, off the following table:

M2MDimKey M2MDimName 0 Balance 1 Attribution 2 Both

Then create an measure group from a fact table containing the following values:

DimKey M2MDimKey 0 0 1 1 2 2 2 0 2 1

Put in regular relationships between the two new dimensions and this measure group, and last of all you'll be able to give the new dimension based on M2MDimKey/M2MDimName a many-to-many relationship to your main measure group. Now you should get the behaviour you want: selecting the Balance member will give you the aggregated values for all rows where Balance=1, but the Both member will only give you the value for Balance=1 and Attribution=1, and your All Member values will also show correct values.

Chris

|||

Thanks a lot Chris. Tried it out today and works great.

One of the things that I had noted :

I had set the "IsAggregatable" to False (as the "All" member does not make any sense in this case. "Both" member in level 1 will be the quivalent of top level) for this dimension and had set the default value to "Both". When I use the Agg design wizard, it complains that the dimension does not have any agg attribute and hence cannot proceed.

The work around is to set the "IsAggregatable" to True, design and set it back to "False" again. But is this is the right thing to do? (Should I use BIDS helper and delete agg that uses the top level of this dimension)

|||

Hmm, that's strange - I've never had that problem when setting IsAggregatable to false. Had you been setting the AggregationUsage property on the cube dimension or something?

Chris

|||Nope. I haven't changed the defaults on the cube dimension properties. I had set only for the dimension directly.

dimension design

In my telco cube, I want to analyze usage. Eg how much sms's a subscriber has sent. The fact table is completely denormalized and has lots of columns with different types of usage. For example, following columns occur:

MO_VOI_PEAK

MO_VOI_OFFPEAK

MO_VOI_WEEK

MO_VOI_WEEKEND

Now the sum of these fields overlap. Evenings during the week are also offpeak... This is only an example. Different categories occur for different products, like voice, sms, mms and so on. And not all categories apply to all products. Eg, mms does not have the peak and offpeak distinction.

Is it possible to put this into one dimension, without aggregating double?

I have already normalised the fact table, meaning that for each usage type, I have one record. So the 4 types mentioned above, all occur in 4 records, instead of 4 columns.

I have also built up a table to link this usage type to its attributes. You can find an extract below.

UsageType

OrigDesc

ApplCode

PeakCode

RoamingCode

ScopeCode

WeekCode

PersCode

MO_VOI_NAT_ONNET

MO

VOI

NON-ROAM

NAT_ONNET

MO_VOI_NAT_OTHER

MO

VOI

NON-ROAM

NAT_OTHER

MO_VOI_NAT_FIXNET

MO

VOI

NON-ROAM

NAT_FIXNET

MO_VOI_INT

MO

VOI

NON-ROAM

INT

MO_VOI_ROAM

MO

VOI

ROAM

MO_VOI_OTHER_DEST

MO

VOI

NON-ROAM

OTHER_DEST

MO_VOI_FLATRATE

MO

VOI

NON-ROAM

FLATRATE

MO_VOI

MO

VOI

MT_VOI_NAT_ONNET

MT

VOI

NON-ROAM

NAT_ONNET

MT_VOI_NAT_OTHER

MT

VOI

NON-ROAM

NAT_OTHER

MT_VOI_NAT_FIXNET

MT

VOI

NON-ROAM

NAT_FIXNET

MT_VOI_INT

MT

VOI

NON-ROAM

INT

MT_VOI_ROAM

MT

VOI

ROAM

MT_VOI_OTHER_DEST

MT

VOI

NON-ROAM

OTHER_DEST

MT_VOI

MT

VOI

MO_VOI_PEAK

MO

VOI

PEAK

MO_VOI_OFFPEAK

MO

VOI

OFFPEAK

MO_VOI_WEEK

MO

VOI

WEEK

MO_VOI_WEEKEND

MO

VOI

WEEKEND

MT_VOI_PEAK

MT

VOI

PEAK

MT_VOI_OFFPEAK

MT

VOI

OFFPEAK

MT_VOI_WEEK

MT

VOI

WEEK

Is there a way to include all this information into 1 dimension, without aggregating in a wrong way, so without summing up double?

Thanks a lot

Joos

Could you clarify why there are multiple rows for some UsageTypes above, but with the same data in the columns shown? For example, these 6 rows are repeated later:

UsageType

OrigDesc

ApplCode

PeakCode

RoamingCode

ScopeCode

WeekCode

PersCode

MO_VOI_NAT_ONNET

MO

VOI

NON-ROAM

NAT_ONNET

MO_VOI_NAT_OTHER

MO

VOI

NON-ROAM

NAT_OTHER

MO_VOI_NAT_FIXNET

MO

VOI

NON-ROAM

NAT_FIXNET

MO_VOI_INT

MO

VOI

NON-ROAM

INT

MO_VOI_ROAM

MO

VOI

ROAM

MO_VOI_OTHER_DEST

MO

VOI

NON-ROAM

OTHER_DEST

|||

They are not the same...you have MO and MT, meaning originating and terminating.

Regards

Joos

|||

Do you want each UsageType to be independently aggregated, with no aggregation of usage across UsageTypes? If so, you could create a UsageType dimension, where the "IsAggregatable" property of the UsageType attribute is set to false. Otherwise, could you explain how you want usage to be aggregated in more detail, with an example?

http://msdn2.microsoft.com/en-us/library/ms174497.aspx

>>

SQL Server 2005 Books Online

Configuring the (All) Level for Attribute Hierarchies

...

The presence of an (All) level in an attribute hierarchy depends on the IsAggregatable property setting for the attribute and the presence of an (All) level in a user-defined hierarchy depends on the IsAggregatable property of the attribute at the top-most level of user-defined hierarchy. If the IsAggregatable property is set to True, an (All) level will exist. A hierarchy has no (All) level if the IsAggregatable property is set to False.

>>

|||

I do want aggregation of usage types, but I want to control aggregation...

I would like to include all attributes of the usage types as displayed in the table. With these attributes, I would like to build hierarchies, like for example origdesc - appl code - peak code, and origdesc - appl code - weekcode. However, for example for Voice, this is not the sum of all week, weekend, peak and offpeak usage types. Moreover, for example for mms, I do not have this distinction and ideally, I would only like to show the appl codes, for which the hierarchy applies, meaning that MMS should not occur in the given hierarchy. This last remark is impossible, I guess...

Most importantly, I would like to control which usage types should be taken to get eg the voice aggregate and eg the MO aggregate. If I would just sum up all voice usage types, this would not be correct...I would be counting more than double.

|||Well, you've given examples of how usage aggregates in some specific cases, but there's no systematic pattern that I can discern. One approach that comes to mind is a fact table where each row indicates (non-overlapping) usage, so that values there can always be aggregated. Then multiple UsageTypes could be related to each fact row via a bridge table/intermediate measure group, using the SSAS Many-to-Many dimension modelling feature.|||

I have chosen to include only those usage types that aggregate towards a real total. If there will be a need to include the others, I will do this in a seperate measure group.

Joos