Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Is SSAS the right way to go?

Is SSAS the right way to go?

Is SSAS the right way to go?

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.

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

RE: Is SSAS the right way to go?

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.

RE: Is SSAS the right way to go?

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.

RE: Is SSAS the right way to go?

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.

RE: Is SSAS the right way to go?

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.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close