I have a data store containing typical star schemes and a whole bunch of code that does such things (obviously a lot more, but this is illustrative):
SELECT cdim.x
,SUM(fact.y) AS y
,dim.z
FROM fact
INNER JOIN conformed_dim AS cdim
ON cdim.cdim_dim_id = fact.cdim_dim_id
INNER JOIN nonconformed_dim AS dim
ON dim.ncdim_dim_id = fact.ncdim_dim_id
INNER JOIN date_dim AS ddim
ON ddim.date_id = fact.date_id
WHERE fact.date_id = @date_id
GROUP BY cdim.x
,dim.z
I am thinking of replacing it with a view ( MODEL_SYSTEM_1, say) so that it becomes:
SELECT m.x
,SUM(m.y) AS y
,m.z
FROM MODEL_SYSTEM_1 AS m
WHERE m.date_id = @date_id
GROUP BY m.x
,m.z
But the view MODEL_SYSTEM_1must contain unique column names, and I am also concerned about performance with the optimizer if I continue and do it because I am concerned that all elements in the WHERE clause are optimized to different facts and sizes, as the view will go through the whole star. and the points of view cannot be parameterized (boy, won't it be cool!)
So my questions are
, , , ?
, ( ), , FK ? SQL, INFORMATION_SCHEMA , .
: , - , .
, , FK/PK , , script, ( ), , INFORMATION_SCHEMA, .
- , , , .