Partitioning the Table into .net - Creating columner database

I am developing some tool for BI. There are terms like Fact, Dimensions and Measures. My application will connect to the normal database and read the data from that table. Now, I want to convert all this data into columnar database. That is all the columns from this table will be transfer into 3 tables each. Consider below table:

ID    Product  
1      XYZ  
2      ABC  
3      ABC   

Now, I want to convert the Product column i.e. dimension into 3 tables. dim_product_table:-

ID      Product  
1       XYZ  
2       ABC  

dim_product_fmk(fact map by key):- Where we will store key-value pair of

Key     Row  
1       1  
1       2  
2       3

dim_product_fmr(fact map by row):- The above table will be reversed.

Row     Key  
1        1  
2       开发者_开发技巧 1  
3        2

The another requirement is all above table should be generated dynamically and I should be able to JOIN all those.

I'm posting another answer so that I don't have to struggle with the comments interface, but this is a response to your request above.

If I understand you correctly, you'll need to determine the structure of the database, and generate scripts based on that. In Sql Server, that means reading from sysobjects, syscolumns, etc. You can determine the primary key dynamically, and build the fact tables above accordingly.

This should get you started:

--list tables
SELECT * FROM sysobjects where type = 'u' Order BY Name

--get columns for that table
SELECT * FROM syscolumns WHERE Id = @Id --from sysobjects

--get PK info
SELECT ColumnUsage.*
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TableConstraints
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ColumnUsage
ON TableConstraints.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TableConstraints.CONSTRAINT_NAME = ColumnUsage.CONSTRAINT_NAME
WHERE TableConstraints.TABLE_NAME = @TableName
ORDER BY ColumnUsage.TABLE_NAME, ColumnUsage.ORDINAL_POSITION

If I understand you correctly, you're trying to create an OLAP structure? I'm sure someone here will give you a direct answer to your question, but you might also consider looking at Analysis Services, which is Microsoft's platform for creating cubes and doing online analytical processing.

When you generate the cube in AS, there's a wizard that you walk through that will do at least some of what you're looking for here.