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?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

imani_technology_spam@.yahoo.com wrote in message news:<8be6e8.0402030738.213518f7@.posting.google.com>...
> 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?|||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.

toddack@.hotmail.com (Todd) wrote in message news:<a4f4b945.0402031412.4c3173fd@.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
> imani_technology_spam@.yahoo.com wrote in message news:<8be6e8.0402030738.213518f7@.posting.google.com>...
> > 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?

Design Question - Is Integration Services the way to go ?

we need to transfer data from a OLTP SQL 2005 database to the backend OLAP Datamart and Datawarehouse databases. This data is a long string of XML (from Infopath), out of which we need to extract the data values and populate the Datamart and datawarehouse. Ideally this will be an automated process - and will also have the flexibility to cater for changes in the xml structur.

we would also like the Analysis services cubes to be "updated" in real-time as the transfer occurs.

is Integration Services the way to go ?

You have a lot of choices here. I would recommend starting out by investigating the XML methods you have in SQL Server in general. Paste this link in Books Online in the "URL" bar:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/63cbe6c9-79d0-4cf3-b451-36dbe4668bf6.htm

Without knowing more, I think SSIS is the way to go for you, but that depends a great deal on whether you are "trickling" the data or loading it in one pass periodically. SSIS gives you a lot of flexibility and functionality, but there is a learning curve with it. That being said, I think it's worth the effort. Spending a little more time getting a package together and documenting it well is a great way to implement your solution in SSIS.

Buck Woody

http://www.buckwoody.com

|||

Buck

thx for this

the destination datamart stores the latest version of the case (i.e. the latest record), the destination datawarehouse stores all of the versions of the case (i.e. all records). Ideally we would be updating both of these records at the same time in real-time "trickling" mode - but if necessary, we could bulk the warehouse updates to (for example) an hourly process.

also need to consider the analysis services part - we are using analysis services cubes for our reporting - and would need these to also be updated in real-time (or bulk upload if required on the datawarehouse database) - is this easily done within SSIS ?

also - alternatives... have considered a couple of alternatives

might SQLCLR code done as a trigger on the required table be an alternative solution ? - how would this perform in comparison to the possible SSIS solution ?

amending the web service that does the update of the OLTP table to also do the update of the datamart / datawharehouse ? (not so keen on this one as we are trying to separate these out ?)

thx

mark

|||

The performance questions you have would have to be tested - there are just too many variables to say one way or another.

The bigger question is how your Analysis system will be used. If you need "real-time" information, then trickling is the way to go, and I'd recommend using an HTTP endpoint or one of the other XML methods native to SQL Server, or perhaps the CLR route you've mentioned if the business logic should be coded in.

If your analysis is truly strategic (based on this data, should we make hubcaps or aircraft parts), then SSIS is my recommendation. Strategic decisions aren't made multiple times a day, so a bulk load process is better tolerated. In either case you will transform "in-flight", meaning you don't have to stage the data, so you save a lot of resources and time.

And in answer to your question about cubes, yes, SSIS is suited for that. In fact, it has many steps and processes built right in specific to AS.