Dynamic Mathematical Relationships

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Mathematical Relationships

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-04 : 08:24:26

Rhys writes "I'm currently specifying a small application to monitor and report flows thru' measuring devices which are child records of sites. A Site has between one and ten measuring devices measuring and recording flow (in cubic meters per second) every 15 minutes. In a given day each measuring device will provide 96 flow readings, and there is a mathematical relationship between the measuring devices on a site, allowing for derivation of site level flow readings,

i.e., for each of the 96 daily periods;
FlowMeter1Value + FlowMeter2Value - FlowMeter3Value = SiteLevelFlowValue

I have a requirement for the Business to administrate these mathematical relationships independantly of each other, so the relationship between devices on a site is unique. Also, these relationships will need to be utilised by a daily process taking in new readings, (all of which come in at a measuring device level), and deriving up the measuring device reading values to site level reading values. So for each period of each day being calculated up, this mathematical relationship needs to be applied for each site record.

At the client end it's fairly easy to produce a web page to allow management of this as I'm working in .Net and can comfortably utilise dynamic controls or web user controls to present a selection of devices belonging to a given site and mathematical operators, then allow the user to create an onscreen mathematical relationship. My concern is in trying to store and utilise these relationships in DTS and Stored Procedures.

Because each site may have a different set of relationships I'm concerned and a little blank, (excepting possibly tring to maintain a dynamic Sql String to be pulled in and used in a sProc or DTS - but thats why I'm asking you), about the best way of maintaining these relationships, and being able to utilise them.

I'm not looking for an instant solution, but am concerned about introducing methodology that becomes rapidly unmaintainable or creates a horrible bottleneck in overnight processes making them take too long to run to be practicable."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-04 : 11:47:40

This forum is a much better recource for solving specific Sql problems but
having said that, and since no one else is jumping in on this, what do you
think about this concept?

Store calculation information as metadata in the database.

Create a generic calculate SP that takes the Site, Device,
DailyPeriod, and an array of values to apply to a calculation.
The SP finds the appropriate calculation(s) for the given Site/Device
from the metadata, applies the values, perform the calculation and
stores the results in a results table (or returns it to the caller).

This solution provides simple, (dumb) compiled code and leaves the specifics
in a form that is more easily maintained, the database.

The challenge becomes representing calculations as metadata. It could be as simple
as a string that the sp could perform search and replace operations on ie:
'<FlowMeter1Value> + <FlowMeter2Value> - <FlowMeter3Value>'
or paramaterized type of thing ie:
@FlowMeter1Value + @FlowMeter2Value - @FlowMeter3Value
or you could build a more suffisticated set of tables that could be used to build complex
calculations.


Simplified DB structure:

Tables:
Site
	SiteID		PK
	<SiteInfo>

Device
	SiteID		PK
	DeviceID	PK
	<DeviceInfo>
	CalculationID (this could be moved to an association table for one or many to many relationship)

Calculation 	<metadata (1 table or more tables)>
	CalcID
	Calculation  - this value can either BE the calculation
			or point to more tables 
Result
	SiteID
	,DeviceID
	,DailyPeriod
	,Result

StoredProcedure:
spCalculate
	@Site
	,@Device
	,@DailyPeriod
	,@CalculationComponentValues
		if all calculations take the same operands then this could be
		a specific list

	Find the appropriate calculation(s) for this Site/Device
	Apply the values passed in to perform the calculation(s)
	Insert result table

Be One with the Optimizer
TG

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-04 : 12:55:57

Depending on how your data is stored, you may be able to create a view for each site. I suppose this also depends on how many sites and what not.

How complex are these mathematical relationships? simple add/subtract or do you also multiply, divide, powers, etc.?

You have a small set of sample data (with DDL, DML) and desired output that we could work with?

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources