Defining measure with a varchar field in OLAP Cube

0
votes

I'm trying to create a multidimentional database from a preexisting database using SQL Server Analysis Services. My problem is that the original database stores all information on a varchar field called "value". What's in that field depends on another field that holds the type of statistic. So I can have for example a fact with statistic_type "number of products sold" with value 1000 and another with type "cost of material bought" with value 5000. The values can have completely differentic meanings, some are numeric values, others are percentages and others are strings.

How do I turn those into measures. Should the statistic_type be a dimension of the cube and have the value as a measure? Does a measure always need to have a numeric value? Should I separate the fact table amoung several tables, one for each type of statistic? Or is there some sensible way to create a cube using just the one table.

It's the first time I'm working with multidimentional databases and SSAS so I'm a little lost.

1
sql-serverssasolapcubeolap-cube
You need to specify the other dimensions in order to answer this question correctly. The all the measures in a single measure group must have the same dimensionality/granuality. Also, if you were to pivot the data back into separate columns (1 for each statistic_type) what does the null density look like? Are the statistics related or not? – James

1 Answers

1
votes

A measure always needs to have a numeric value. In fact, you will probably have to cast the value column as a numeric datatype in your Data Source View in order for it to even be a candidate for a measure in your cube.

You should make statistic_type a dimension and "value" a measure. It's ok to just use the one table, although it might be easier to work with if you make a lookup table of the distinct statistic_types.