November 17, 2011 at 12:08 pm
I didn't see a 2008 BI forum, so Ill put my post here.
I am designing a BI system right now and have stumbled across a design issue. Right now the site which generates the data allows for different types of values to be created and saved in the system (this is for collecting data from equipment). For example, an admin user can create a piece of equipment and associate measurements with it of different types (some might be ints, decimals, strings, dates, etc). Another piece of equipment added would be a different set of measurements.
For example, they might create a widget with name=string, output temperature=decimal, numberofjobsdone=int, and so on. Then they create a Chopper with inputsupplier=string, startdate=date, enddate=date, output=int, outputtemperature=decimal. So, all data is saved as string in the OLTP database and the warehouse (for the moment). I can create a cube based on strings but it would eliminate most of the advantages. Is there sort of dynamic typing I can use to account for this (or any other ideas)?
The goal is to allow them to not only store data, but to be able to see trends, performance, etc. For example, Output Temperatures for Widgets by Month by Plant/Site.
Thanks!
November 18, 2011 at 1:31 am
You'll need to put some ETL in place to convert the measures to the corresponding data type.
Furthermore, normally a measure isn't a string. Strings mean it is some sort of description, and those should be put into a dimension. Dates go into the Time dimension.
If at the end you still have one measure with different types (int, currency, percentage), you can use FORMAT_STRING to change the display format. But your (sub)totals created by the cube will be completely rubbish.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 18, 2011 at 3:37 am
Thanks!
My hope was to set the datatype just before the cube. My initial hope was to use a calculated value in the measure to convert the string to the correct type, but I can't seem to find a way to get that to work.
I hope this makes sense. Obviously strings are not the preferred method for saving values. However, when you allow the user to create values of many different types, you don't have much choice. I could use something more like an EAV, but I don't want to introduce that.
The issue here is that they have requested the ability to create any combination of anything, and then run reports against it. That is why I am where I am.
November 18, 2011 at 3:52 am
Elliot Rapp (11/18/2011)
Thanks!My hope was to set the datatype just before the cube. My initial hope was to use a calculated value in the measure to convert the string to the correct type, but I can't seem to find a way to get that to work.
I hope this makes sense. Obviously strings are not the preferred method for saving values. However, when you allow the user to create values of many different types, you don't have much choice. I could use something more like an EAV, but I don't want to introduce that.
The issue here is that they have requested the ability to create any combination of anything, and then run reports against it. That is why I am where I am.
I would create the reports directly on the DB, not in SSAS. SSAS expects numeric values so it can aggregate them (the whole point of SSAS). When writing SQL queries in SSRS, it doesn't really matter of the values are strings or not. Obviously they don't need aggregates, as everything can be anything.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply