Is SSAS the right way to go? 2

[ZenRaven]

I'm looking for some advice on whether or not SSAS is the right tool for the job. I work for an assembly line type manufacturer. We collect data at certain points in the manufacturing process at certain intervals. You can imagine the amount of data we're talking about when you're running 24/7 and collecting data at the ms level. These measurements really don't mean anything in business terms until they're aggregated and compared (definition of analysis? <smile>). I'm thinking at this point that we're a good candidate for Data Warehousing and Analysis Services.

One of the 1st points that came to my attention is that we currently track threshold alarms. These alarms are defined as x number of times a measurement goes above threshold over y time range. (eg. measurement abc is only considered in an alarm state if it passes the threshold 3 out of any given 5 second window). Is this type of measurement something that can be defined within SSAS? I was thinking another option was predefining the definition of an alarm but that may change if the analyst wants to throw around what if scenarios.

I'm a SQL Server Developer with about 7 years experience but very little BI experience. I'd love to hear everyone's thoughts.


--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson

 

[RiverGuy]

SSAS is great for aggregating data different ways. That being said, it's not flexible enough to allow you to dynamically create categories. That's not a bad thing, it's just that the whole point of pre-aggregation is to have the business definitions before hand. You would have to predefine what an alarm state is, and most likely you would handle that in the ETL which loads the relational Star Schema. So this would be handled before the data even got to SSAS.

However, you don't need SSAS to utilize a data warehouse. One option in your scenario would be to create logic for your standard alarm states, predefined and loaded into SSAS. You could complement that with the ability for some of your power users to define their own alarm states, running queries against the relational database. The drawback here of course would be performance.

 

[MDXer]

SSAS Is a great tool for this any time you are dealing with aggregating large volumes of data for analysis or reporting you will benefit by some form of OLAP enviroment.

I will disagree with river guy in regards to where you would set your threshold alarms. These can be handled in the cube very effectively using KPIs.

Another benefit from using SSAS would be Proactive caching. Since it sounds like you will be looking at a near realtime requirement you will need to get the data available quickly, and utilizing proactive caching will aid in reaching this goal.


Even though I believe SSAS is the ideal tool for this doesn't mean it is going to be a quick and easy project. I already can see a couple areas that will probably require a lot of thought and clear cut business requirements. I wouldn't say this is the ideal project to learn SSIS or SSAS on.

Regardless of which direction you go make sure you have clear understanding of what the business users want and need and they clearly understand what is being delivered and when it will be delivered. More BI projects die from a misunderstanding from the business side of the house.

 

[RiverGuy]

I agree with most of what MDXer said, however, I'd be interested in how this could be accomplished with KPIs. My impression is that one would have to iterate over each second, examining each current set of five, looking for three of them over a threshold. At the very minimum, you would need to design the database at the granularity of one second which could get extremely large depending on the number of machines or whatever it is that is being tracked.

 

[ZenRaven]

From what I read up on KPIs, it was also my understanding that they couldn't do the job. You are correct about the alarm definition.

An example would be a 5 second interval. An alarm is defined as 3 out of 5 crosses. From seconds 5 to 10 there could be a cross on 8 and 9 which would be no alarm. If you slide the window to be 6 to 11 and 11 crosses, then the window would be considered an alarm. I figure even it was possible with SSAS, this type of thing would perform poorly seeing that it really doesn't take advantage of the pre-aggregation.

I'm in no way disillusioned that this will be a simple task. Just trying to get a feel for what my options are and what will serve us best in the long run. I appreciate all of your feedback.


--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson