cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Data Warehouse for long term interface statistics, reporting, trending, and capacity planning

Data Warehouse for long term interface statistics, reporting, trending, and capacity planning

One of the useful aspects of Orion and NPM is the statistics gathering for performance and capacity planning...until you have to change the IP address for whatever reason or similar events.
Now all of the carefully gathered performance data is gone.

What do you use to trend the old to the new?
How do you know if you have gained any ground or not?

Similar data is gone if you re-index a switch's interfaces (hardware upgrade).

There is also the issue of being able to retain detailed performance data for long period of time...6 months,1 year, 2 years, or longer for reporting and accurate trending.

One of the items Orion is missing is a data warehouse to archive that performance data for long term reporting.

It would not be practical to keep 18 months or more months of performance data in the production database...and then risk losing 6 months of valuable perfromance data after a hardware change. 

So introduce the data warehouse.

It would be a separate out of band database optimized for reporting.  It syncs updates every hour or so from the production database through a scheduled process.
This allows for near time data for reporting.  Thus any reports are not hitting the production database unless it requires recent data within say 4 hours.


Thus the production database can be set to trim at a lower level (days or weeks instead of months) keeping tables smaller and the product more efficient.
Based on this it would be desirable to locate the data warehouse on the SAN as it not as critical for shear speed or for SolarWinds to operate.

This will help product performance by keeping the production database size down.

Now you can retain detailed historical data for longer periods of time and be able to map annual trends/growth during seasonal fluctuations.
An example would be, your company has a spike in network activity related to the Christmas season every year.  How do you trend the last three years to see
if you may be close to saturation?  Without the historical data available you can't.  How does the new switch chassis you put into production 2 mnths ago fit into those trends?

Chances are all the data for the old interfaces prior to the current switch is gone since the new interface layout changed.

By having a data warehouse you would still have the data, but  in an out of band database you can query to your hearts content and not impact production polling thus providing the ability to answer those questions.

This should also apply to any monitored statistic in SAM and WPM as well.  This allows you to trend data for applications and see what changes to the environment have done and whether they
have positively or negatively impacted response time and availability as well as other monitored metrics over time.  This is huge for software upgrades and troubleshooting.

So the biggest wins are long term data points for  capacity planning, out of band reporting reducing overhead on primary database, reduced cost of SAN based database for long term storage, retention of data through hardware and IP address changes, smaller more efficient production database for increased performance via smaller polled metric specific tables.

320 Comments

How about a "cheaper" secondary implementation with no polling engine that only gets information from the archive database.

So like now after x hour(s), day(s) ect.. roll the data up to by hour, day ect.  you can instead have a sync between the two databases for more research if needed.    Then have the ability to define what gets sync like traffic data, syslog only critical events ect.

Then against the this secondary database a separate Orion instance that only uses that data for your research.

I know that would help me a lot as some times I want to go back min by min to see traffic patterns in the past but due to current database limitations I have to roll that information up and loose all the in depth analysis I need.

Level 8

Thanks for the hard work...However, as much as I want to like this idea, I believe this is a feature it should be included within Solarwinds. The ability to retrieve historical data in certain institutions is extremely important in today's technology world. Workarounds sometimes create a bigger problem when you run a lean IT department.

That's the whole point is that it needs to be part of Solarwinds because we can't track the long term in order to provide capacity planning with what exists today. 

This is especially true in a rapidly growing environment.  You can't see where you started from a year ago with any detail.  The potential performance hit in a production Orion database would be huge.

You need to be able to get your reporting and capacity planning data off the production database but have near time data updates for reporting.

Level 10

This can be done today, albeit it outside of Solarwinds... replicate the main database into an Enterprise Data Warehouse and then have the database team write a routine that runs periodically to append new data from the main database to the warehouse database... use the BI tool of your choice to write reports against the data warehouse.. tune data retention on the main database as allowed based on how often you append data to the warehouse and how big/small you want to keep the main database. I did this 3 years ago and it worked like a charm using SSRS as the reporting tool...

Yes I agree this is doable outside the product.

It should be a function of the product because of the nature of what it does and the need to report over longer periods of time.

Level 11

The win for SolarWinds would be twofold; their customers would enjoy better performance from an operational DB that is more lean & mean, and the stats that we love to hoard and report on could be squirreled away and queried out-of-band.

All the products (including the evil custom pollers) could write out data to their heart's content, and the warehouse would be on-stop-shopping for making those holistic views that our managers love so much.

I've requested this in various formats; here's hoping this one gets results!

A morning bump to keep this going...

Another daily bump for awareness

Level 11

Great thinking!  We just went through a process of determining whether or not to dump Solarwinds ... there are lots of great competitive products out there.  This was a feature that was on our list that didn't get a checkbox beside it.