I must just be stupid 
Author Message
 I must just be stupid

I have a very simple problem, and I am sure as I have printed off all the
BOL stuff that I can to read tonight I will solve it real soon now, but it
would be great if some one could give me a kick start.

We have been given a database with about 1M records in the fact table, which
I really do not want to have to rebuild.  The table contains a record per
sale and only has one measure, the purchase price of the item.  The cost of
the item, which is fixed to the product code, is stored in a Dimension
table.

Problem, I would like to create a "Calculated Member" and use this as a
measure.  I thought this would be easy, just create a "Calculated Member"
with the field in it and then Analysis Services would do the rest.  I am
being to think that I am so wrong.  I have tried (and read much about)
Sum(...), but this does not seem to be the right way to do it.

The only answer I seem to have found is add a new field to the fact table
and populate that, but it kills be to think of the wasted space, espically
as this fact table could get as big as billions of rows.

Any clues would be most greatfully accepted.

Many thanks in advance
Chris Morgan



Sat, 22 May 2004 03:47:03 GMT
 I must just be stupid

To create a calculated measure for cost, try this:
1. Create a member property from the Cost column in the dimension table
2. Define a calculated measure that uses this property as its value. You can
do this with the expression:
Val([dimname].CurrentMember.Properties("Cost"))
You need to use the VBA function Val because a member property value is
always returned as a string.

HTH

Quote:
> I have a very simple problem, and I am sure as I have printed off all the
> BOL stuff that I can to read tonight I will solve it real soon now, but it
> would be great if some one could give me a kick start.

> We have been given a database with about 1M records in the fact table,
which
> I really do not want to have to rebuild.  The table contains a record per
> sale and only has one measure, the purchase price of the item.  The cost
of
> the item, which is fixed to the product code, is stored in a Dimension
> table.

> Problem, I would like to create a "Calculated Member" and use this as a
> measure.  I thought this would be easy, just create a "Calculated Member"
> with the field in it and then Analysis Services would do the rest.  I am
> being to think that I am so wrong.  I have tried (and read much about)
> Sum(...), but this does not seem to be the right way to do it.

> The only answer I seem to have found is add a new field to the fact table
> and populate that, but it kills be to think of the wasted space, espically
> as this fact table could get as big as billions of rows.

> Any clues would be most greatfully accepted.

> Many thanks in advance
> Chris Morgan



Sat, 22 May 2004 04:34:35 GMT
 I must just be stupid
Howard,

Thanks for the advice, unfortunatly it did not work.  We understand the
concept, but it must be a syntax thing that we are getting wrong.

Is dimname (as in your text) just the Dimension.  If it is, how does the
command know what member to look at its properties.

Any advice will be very greatful. I have ordered a couple of books, so I am
hoping we will get there in the end.

I find it hard to believe this is so hard,  it must be quite normal to have
static numeric data (i.e. number of screws in a wdiget, the amount of square
feet in a building, number of seats on a plane etc) that you need to use
within the measures.  We have spent the best part of a week trying to solve
this now.  I am starting to think I will go back to boring old relational
dbs and give up on this multi-dimensional cleaver stuff.

Thanks for you help
Chris


Quote:
> To create a calculated measure for cost, try this:
> 1. Create a member property from the Cost column in the dimension table
> 2. Define a calculated measure that uses this property as its value. You
can
> do this with the expression:
> Val([dimname].CurrentMember.Properties("Cost"))
> You need to use the VBA function Val because a member property value is
> always returned as a string.

> HTH


> > I have a very simple problem, and I am sure as I have printed off all
the
> > BOL stuff that I can to read tonight I will solve it real soon now, but
it
> > would be great if some one could give me a kick start.

> > We have been given a database with about 1M records in the fact table,
> which
> > I really do not want to have to rebuild.  The table contains a record
per
> > sale and only has one measure, the purchase price of the item.  The cost
> of
> > the item, which is fixed to the product code, is stored in a Dimension
> > table.

> > Problem, I would like to create a "Calculated Member" and use this as a
> > measure.  I thought this would be easy, just create a "Calculated
Member"
> > with the field in it and then Analysis Services would do the rest.  I am
> > being to think that I am so wrong.  I have tried (and read much about)
> > Sum(...), but this does not seem to be the right way to do it.

> > The only answer I seem to have found is add a new field to the fact
table
> > and populate that, but it kills be to think of the wasted space,
espically
> > as this fact table could get as big as billions of rows.

> > Any clues would be most greatfully accepted.

> > Many thanks in advance
> > Chris Morgan



Tue, 25 May 2004 00:37:03 GMT
 I must just be stupid
I think that what may be happening is that Howard's measure will only
show you a result when you are viewing the very bottom level of the
dimension that contains the member property. I do not believe that this
measure will aggregate up automatically.

I do not have an exact example at hand but the logic would have to go
some thing like find all the bottom level children under the currently
selected member (which is the "All" member by default if you are not
using the dimension on one of your axis) and add up their costs.

I have not tested it be something olong the lines of the following
should work.

SUM(DESCENDANTS({<dimname>].CurrentMember,,LEAVES),Val([<dimname>].Curr-
entMember.Properties("Cost")))

HTH

--

Regards
Darren Gosbell

Posted via dBforums
http://dbforums.com



Tue, 25 May 2004 12:22:37 GMT
 I must just be stupid
The usual solution to this is to join the product table to the fact table in a
view, create a new column in the view that is the multiplication of price by
units, and build the cube from this view, making a measure from this new column.
Takes about 3 minutes of DDL and work in the Analysis Manager.

HTH.

Quote:

> I have a very simple problem, and I am sure as I have printed off all the
> BOL stuff that I can to read tonight I will solve it real soon now, but it
> would be great if some one could give me a kick start.

> We have been given a database with about 1M records in the fact table, which
> I really do not want to have to rebuild.  The table contains a record per
> sale and only has one measure, the purchase price of the item.  The cost of
> the item, which is fixed to the product code, is stored in a Dimension
> table.

> Problem, I would like to create a "Calculated Member" and use this as a
> measure.  I thought this would be easy, just create a "Calculated Member"
> with the field in it and then Analysis Services would do the rest.  I am
> being to think that I am so wrong.  I have tried (and read much about)
> Sum(...), but this does not seem to be the right way to do it.

> The only answer I seem to have found is add a new field to the fact table
> and populate that, but it kills be to think of the wasted space, espically
> as this fact table could get as big as billions of rows.

> Any clues would be most greatfully accepted.

> Many thanks in advance
> Chris Morgan

--
George Spofford
Microsoft MVP
Chief Architect / OLAP Solution Provider
DSS Lab



Tue, 25 May 2004 23:52:59 GMT
 I must just be stupid
This is interesting and of course it would work, but does it not cause a
performance issue, I have no idea if it would or would not, but as our fact
table could go grow bigger than 1000 million records (billion(s)) I am very
carefully about any joins to it.

Chris


Quote:
> The usual solution to this is to join the product table to the fact table
in a
> view, create a new column in the view that is the multiplication of price
by
> units, and build the cube from this view, making a measure from this new
column.
> Takes about 3 minutes of DDL and work in the Analysis Manager.

> HTH.


> > I have a very simple problem, and I am sure as I have printed off all
the
> > BOL stuff that I can to read tonight I will solve it real soon now, but
it
> > would be great if some one could give me a kick start.

> > We have been given a database with about 1M records in the fact table,
which
> > I really do not want to have to rebuild.  The table contains a record
per
> > sale and only has one measure, the purchase price of the item.  The cost
of
> > the item, which is fixed to the product code, is stored in a Dimension
> > table.

> > Problem, I would like to create a "Calculated Member" and use this as a
> > measure.  I thought this would be easy, just create a "Calculated
Member"
> > with the field in it and then Analysis Services would do the rest.  I am
> > being to think that I am so wrong.  I have tried (and read much about)
> > Sum(...), but this does not seem to be the right way to do it.

> > The only answer I seem to have found is add a new field to the fact
table
> > and populate that, but it kills be to think of the wasted space,
espically
> > as this fact table could get as big as billions of rows.

> > Any clues would be most greatfully accepted.

> > Many thanks in advance
> > Chris Morgan

> --
> George Spofford
> Microsoft MVP
> Chief Architect / OLAP Solution Provider
> DSS Lab




Sun, 30 May 2004 03:26:23 GMT
 I must just be stupid
Darren,

Many thanks,  it turned out to be .....

[Measures].[Income] -
sum(Descendants([Route].CurrentMember,,Leaves),val([Route].CurrentMember.pro
perties("Running Cost")))

and it works a treat...I feel I am getting my head around this MDX and
nDimensional stuff finally, or is it I have just gone into some nDimensional
space and really lost it.

Again many thanks
Chris


Quote:
> I think that what may be happening is that Howard's measure will only
> show you a result when you are viewing the very bottom level of the
> dimension that contains the member property. I do not believe that this
> measure will aggregate up automatically.

> I do not have an exact example at hand but the logic would have to go
> some thing like find all the bottom level children under the currently
> selected member (which is the "All" member by default if you are not
> using the dimension on one of your axis) and add up their costs.

> I have not tested it be something olong the lines of the following
> should work.

> SUM(DESCENDANTS({<dimname>].CurrentMember,,LEAVES),Val([<dimname>].Curr-
> entMember.Properties("Cost")))

> HTH

> --

> Regards
> Darren Gosbell

> Posted via dBforums
> http://dbforums.com



Sun, 30 May 2004 03:56:06 GMT
 I must just be stupid
For good SS2000 performance, create an index on (product_join_key, price) on the
product table. I believe that the fact table will then essentially join only to
the index, and be very fast. If you are using Oracle8i, a similar RDBMS
optimization may be available.

HTH

Quote:

> This is interesting and of course it would work, but does it not cause a
> performance issue, I have no idea if it would or would not, but as our fact
> table could go grow bigger than 1000 million records (billion(s)) I am very
> carefully about any joins to it.

> Chris



> > The usual solution to this is to join the product table to the fact table
> in a
> > view, create a new column in the view that is the multiplication of price
> by
> > units, and build the cube from this view, making a measure from this new
> column.
> > Takes about 3 minutes of DDL and work in the Analysis Manager.

> > HTH.


> > > I have a very simple problem, and I am sure as I have printed off all
> the
> > > BOL stuff that I can to read tonight I will solve it real soon now, but
> it
> > > would be great if some one could give me a kick start.

> > > We have been given a database with about 1M records in the fact table,
> which
> > > I really do not want to have to rebuild.  The table contains a record
> per
> > > sale and only has one measure, the purchase price of the item.  The cost
> of
> > > the item, which is fixed to the product code, is stored in a Dimension
> > > table.

> > > Problem, I would like to create a "Calculated Member" and use this as a
> > > measure.  I thought this would be easy, just create a "Calculated
> Member"
> > > with the field in it and then Analysis Services would do the rest.  I am
> > > being to think that I am so wrong.  I have tried (and read much about)
> > > Sum(...), but this does not seem to be the right way to do it.

> > > The only answer I seem to have found is add a new field to the fact
> table
> > > and populate that, but it kills be to think of the wasted space,
> espically
> > > as this fact table could get as big as billions of rows.

> > > Any clues would be most greatfully accepted.

> > > Many thanks in advance
> > > Chris Morgan

> > --
> > George Spofford
> > Microsoft MVP
> > Chief Architect / OLAP Solution Provider
> > DSS Lab


--
George Spofford
Microsoft MVP
Chief Architect / OLAP Solution Provider
DSS Lab

ISVs & IT organizations: Find out how DSS Lab can speed your development!


Tue, 01 Jun 2004 02:05:25 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. Problems with sp_processmail (or am I stupid??)

2. Locking error, DataControl Error, or am I stupid ?

3. Am I just stupid ? (Part 2)

4. Am I stupid for not understanding MS EULA?

5. configuring FMPro unlimited: am I stupid?

6. web security am I stupid

7. I am so stupid........

8. Btrieve sucks and I am stupid please Help.

9. Maybe I am too stupid, but...

10. Stupid guy and Stupid problem

11. STupid guy and stupid problem

12. Stupid jdbc driver, Stupid TOMCAT


 
Powered by phpBB® Forum Software