473,322 Members | 1,403 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Design Question: Calculated Field

I'm trying to create an OLAP system using SQL Server 2000 Analysis
Services (AS). I want the AS cube to be based on a database with a
star schema.

I have a field called Ratio. The initial ratio value is based upon
this formula: (CurrentTimePeriodAmount -
PreviousTimePeriodAmount)/PreviousTimePeriodAmount. However, that
initial ratio can be manually overridden and replaced. Then the ratio
is used to calculate future Amounts.

Should I place that calculated Ratio field in the "source" database
design or should it be part of the Analysis Services cube?
Jul 20 '05 #1
2 3919
What you may want to consider instead of creating a new field in the
physical table, is to use a view with the calculation there. I am a
huge advocate of using views for fact tables for the reason you are
talking about below.

The problem is if the ratio is going to be replaced/overridden you
need to ask yourself do you need to create a new record for this
ration value so you can track the changes over time. Are these changes
going to be adhoc, if so does your front-end tool allow for this type
of What If Functionality. Things to ask yourself.

HTH
-todd

im*******************@yahoo.com wrote in message news:<8b************************@posting.google.co m>...
I'm trying to create an OLAP system using SQL Server 2000 Analysis
Services (AS). I want the AS cube to be based on a database with a
star schema.

I have a field called Ratio. The initial ratio value is based upon
this formula: (CurrentTimePeriodAmount -
PreviousTimePeriodAmount)/PreviousTimePeriodAmount. However, that
initial ratio can be manually overridden and replaced. Then the ratio
is used to calculate future Amounts.

Should I place that calculated Ratio field in the "source" database
design or should it be part of the Analysis Services cube?

Jul 20 '05 #2
I want to be able to display an initial, calculated ratio. I also
want a seperate field for the user to type in a ratio. How could that
be implemented? Also, I'm considering using SQL Server Analysis
Services with some kind of MS Excel 2000 front end.

to*****@hotmail.com (Todd) wrote in message news:<a4**************************@posting.google. com>...
What you may want to consider instead of creating a new field in the
physical table, is to use a view with the calculation there. I am a
huge advocate of using views for fact tables for the reason you are
talking about below.

The problem is if the ratio is going to be replaced/overridden you
need to ask yourself do you need to create a new record for this
ration value so you can track the changes over time. Are these changes
going to be adhoc, if so does your front-end tool allow for this type
of What If Functionality. Things to ask yourself.

HTH
-todd

im*******************@yahoo.com wrote in message news:<8b************************@posting.google.co m>...
I'm trying to create an OLAP system using SQL Server 2000 Analysis
Services (AS). I want the AS cube to be based on a database with a
star schema.

I have a field called Ratio. The initial ratio value is based upon
this formula: (CurrentTimePeriodAmount -
PreviousTimePeriodAmount)/PreviousTimePeriodAmount. However, that
initial ratio can be manually overridden and replaced. Then the ratio
is used to calculate future Amounts.

Should I place that calculated Ratio field in the "source" database
design or should it be part of the Analysis Services cube?

Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Stefanos | last post by:
Hi everybody, I need some help on calculated values in my database design. I'm creating an accounting / business management application with the usual modules like A/R, A/P, Inventory, etc. ...
5
by: Richard Holliingsworth | last post by:
Hello: Thanks for reading this post. I need to create a metrics (form or report - I don't care which) to display calculated fields about the database (A2002 front end to SQL Server 2K) 1) I...
11
by: NC Tim | last post by:
Hello, I think the question i have is fairly straightforward, but I can't seem to replicate the old SAS frequency procedure when I try to accomplish this in MS Access. anyway, i have about 10...
1
by: Norbert Lieckfeldt | last post by:
MS Access 2002 here. I am just trying to set up a simple database for a friend who's an optician. Basically, all clients with address details, date of last eyetest and a drop-down combo box to...
2
by: hhathome | last post by:
In my report I have a calculated DateDiff Field, I also have a calculated count field. I'm trying to get a total of the DateDiff field and dividing it by the count field and I'm having problems --...
29
by: MP | last post by:
Greets, context: vb6/ado/.mdb/jet 4.0 (no access)/sql beginning learner, first database, planning stages (I think the underlying question here is whether to normalize or not to normalize this...
2
by: Jason | last post by:
I am trying to create a simple database, but it's proving too complex for me!!! I have only just started a new database and am trying to have two fields in a table: the first holding the first...
5
by: Henrik | last post by:
The problem is (using MS Access 2003) I am unable to retrieve long strings (255 chars) from calculated fields through a recordset. The data takes the trip in three phases: 1. A custom public...
2
by: jcf378 | last post by:
hi all. I have a form which contains a calculated control ("days") that outputs the # of days between two dates (DateDiff command between the fields and ). However, when I click "Filter by...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.