I am building an aggregated FACT table. There is one dimension that primarily dictates what data will be in the FACT. This data will be selected from another FACT table and each member of my primary dimension will select this data using slightly different categorization criteria. The categorization criteria effectively define each member of this dimension and are given a suitable names, such as "ProductType XYZ", "ProductType PQR" etc. A product type could contain aggregated data for several products that all have certain things in common. By storing the SELECT statement with the appropriate categorization criteria as an attribute (field) on each of the "Product Type" dimension members, I can iterate over the dimension using a cursor (it is not a large dimension and is never likely to be - less than 50 members) and generate data for the new FACT by building a dynamic insert query that uses the SQL from each member on the dimension, recovered from a cursor variable. This feels like a neat and efficient way to load the FACT table and the stored procedure (SP) that uses the cursor and inserts the data is very simple.

The key issue is that I am putting "code" (select statements with specific filters) into my "data" - the code will be captured as a attribute on a dimension. I am then pulling this code out with a cursor and executing it as part of a dynamic SQL statement in a stored procedure to load my secondary FACT table. Is this a good idea or not? If not, why not?

I am getting resistance to doing this from colleagues. It does strike me that putting select statements in the dimension attributes could make them susceptible to injection attacks. Are there any other reasons for not loading the secondary FACT table in this way and how worried should I be about the injection issue? Also, I would appreciate any other comments on the pros and cons of using this design approach. TIA.

This is not so much a problem. I am looking for opinions on different design approaches, specifically if the one I have suggested is a "bad idea" and if so, why?