Need Help in Table Design

[Shantha]

Hi,

My requirement is to collect metrics from database/ datawarehouse environment and publish it.

A master table with the below structure will hold the required information:

Metrics Master
==============

Metrics Id
Metrics Name
Query

The query will contain the select query which would be executed dynamically by a procedure and collect the resultant.

The query & the number of columns would vary from metrics to metrics...

Now require help in designing transaction table which would hold the data collected by dynamically executing the query...

Metrics Transaction
====================
Metrics Transaction Id
Metrics ID
Col1_value
Col2_value
:
:
Coln_value

Where 'n' would be the maximum no of columns available in all of the designed metrics

I found that this design is not so good. Does anyone have any better suggestion or any other alternative design to suit

Thanks,
Shantha.

 

[Remou]

Why is it not possible to publish the data from the query?

http://lessthandot.com

 

[Shantha]

Not for all metrics, but for few we want the data to be stored in the db to do some trend analysis.

Thanks,
Shantha.

Talent is what you possess;
genius is what possesses you

 

[SkipVought]

It might be better...
[tt]
Metrics Transaction
================
Metrics Transaction ID
Metrics ID
FieldName
FieldValue
[/tt]



Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Remou]

I suppose you could use something on the lines of:

Metrics_Transaction_ID
Metrics_ID
Col_Name
Row_Name
Cell_value

However, if it is only for a few metrics to be used in trend analysis, I suspect the best thing to do would be to create tables for each of them, so that the data can be appended.

http://lessthandot.com

 

[Shantha]

Hi Remou,

What row_name indicates?

Thanks,
Shantha.

Talent is what you possess;
genius is what possesses you

 

[Remou]

I do not know what your queries return but a crosstab is possible so it may be necessary to store Column 1 values as row names for ease of retrieval.

http://lessthandot.com