Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Do you know what the best practices would be regarding SSAS and having real-time reports.
I am not sure if we should be pulling data off our live database or using SSIS to move data periodically to a Data warehouse database before re-processing the cube.
Any advice on what the best approach is would be greatly appreciated.
Operational vs. analytic reports
Ad-hoc reporting against an operational system is a bad idea, so the answer depends on what your real-time requirements are.
In most cases the actual realtime requirement is for a handful of operational reports. These are usually not aggregate or statistical in nature, and tend to be exception reports, status reports or to-do lists. They should not typically hit large amounts of data, but rather select certain records that match the criteria of the report.
Operational reports are almost always tied to a specific process and never analytic in nature. Usually, analytic reports can be run off a periodic load and do not need up-to date data. There are a few exceptions to this such as market data feeds, but these are exceptional cases.
In this case you should do a periodic load to a data mart, data extract or data warehouse system for the analytic reports and build the operational reports as bespoke, tuned reports. Run them of a replicated database instance if possible to reduce transient load on the operational system database.
Some applications do need real-time or near-realtime (low latency) data to be loaded into an analytic system. A couple of examples of this type of requirement are market data analytic systems or accounts reporting systems. In the former case the system is used by traders to run statistical analytics on the data and in the latter case there is typically a requirement for the accountants to be able to prepare and enter a journal and then report on it immediately.
These are really operational reports in disguise, and tend to have their SLA tied to the source system. One thing these systems have in common is that they tend to have a relatively straightforward model for the underlying data.
You can front certain operational databases with a ROLAP toolthat queries the underlying schema. It is rarely a good idea to do this with an operational system database as they tend not to lend themselves to efficient aggregate queries.
I have done this on one occasion with an accounts system and a report model - in theory a cube could have also been used. Oracle also supplies a discoverer based ad-hoc reporting tool for Oracle Financials that works similarly. However the native data model of an accounting system is actually quite close to a snowflake schema so you can sort of get away with this. However the reports tend to be quite slow and they still bog down the system if it gets loaded.
If you really want ad-hoc analytics on a low-latency source you could build a low-latency ETL process that populates a data mart. This sort of thing is more fiddly and complicated than a traditional ETL process, so you don't want to do this for applications where there is no genuine requirement. You can put a cube with a leading ROLAP partition on this and store the historical data as MOLAP. I have been involved in building a system that works like this.
Low-latency analytic systems
Depending on your data source you can use a changed-data capture mechanism or polling mechanism to identify new transactions. This process can then trickle the data into the data mart. If you want really low latency and control the source database you can implement triggers that push out transactions to the data mart, although this option may place siginifcant load on your system.
Leading ROLAP partition on a cube
A low-latency cube can be done with a hybrid architecture. Leading data (e.g. for the current open accounting period) can be queried through a ROLAP partition, which will issue queries against the underlying data. Closed or historical data is managed through MOLAP partitions that have aggregations. This gives you near-realtime data without having to issue database queries on large data volumes and the performance benefits of aggregations on the historical data.
This has a few caveats. Pure ROLAP dimensions substantially restrict the features that can be used on the measure groups (e.g. no aggregations except SUM or COUNT). If you expect incremental updates to dimensional data you are better off with MOLAP dimensions and an incremental update process triggered off from the job that updates the underlying dimension table. This is quite efficient if you process the dimension incrementally.
Testing this type of system is somewhat fiddly, as you have to make sure your changed data capture or incremental load mechanism is working correctly. You will probably need to create a test harness that can post transactions into the system in order to run unit tests.
The most significant balancing act to consider is whether your need for real-time reporting outweighs the performance hit that both your OLAP and OLTP systems will take from using your OLTP data source directly as fact and dimension tables, and then bouncing ROLAP/HOLAP queries off of them. If the tables are all quite small, and the server isn't under heavy load already, then the penalty is probably negligible. If SSAS queries are going to kick off 500MB+ reads, then that's a problem.
And unless you're doing some kind of high-frequency trading, you probably don't need to have your SSAS database that up to date. It seems like SSAS is more useful for big-picture kind of summaries, and if the past few hours of data aren't included yet, it's not going to make a big difference to those running the reports (ask around to be sure, obviously).
We load our data warehouses and process the cubes each night, and that's typically plenty. We also have a few simple reports built against OLTP tables for viewing more targeted, up-to-the-minute data (stuff that's really more OLTP than OLAP in nature).
If you are using SSAS in MOLAP storage mode, then you have to process the cube to refresh reports that use the cube as a data source. Whether you use your "live" database or a "non-live" version generated via ETL processes, you must process the cube to bring the cube up to date. If you are using SSAS for the advantages of high performance aggregations, then you will have to accept some degree of data latency.
On the other end of storage modes for SSAS is ROLAP. ROLAP does not require processing a cube since it doesn't actually generate a cube data store. Instead, all it does is spawn SQL queries against the source system database anytime you run a MDX query. This means that it is going directly against your underlying database. So if you have a "live" database, you can get current data from it without processing a cube if your storage mode is ROLAP. My own presonal experience is the underlying queries are generally horrific. For instance, if I wanted to query a fact and a few attributes of a dimension, the MDX would spawn a SELECT DISTINCT * FROM Dimimension.Table JOIN Fact.Table ON Key.Column = Key2.Column2. This basically made ROLAP useless for me since it performed worse than any alternatives.
In the middle you have HOLAP storage mode. This is basically MOLAP for some dimensions and facts tables and ROLAP for others. So you have to process part of your cube and the other part is live data from the database. To me, this was not a viable solution since it still suffered the same performance issues as ROLAP and added some latency like MOLAP.
If you really want to query your live data without forcing users to learn SQL, you could create a report data model in BIDS, deploy it to your Reporting Services server, and then use it as a data source. The data model is a metadata later that is almost identical to the model in SSAS -- it just doesn't allow you to generate a precached cube from it. This could be a reasonable alternative to SSAS. However, report data models only work with SSAS -- they do not work with Excel. If your users want a high-speed data source for Excel that does not require writing SQL, then your only viable solution is MOLAP SSAS. If your users want "live" data and don't need Excel as a starting point, then go with SSRS and a report data model.
If you are using Cognos, Business Objects, Microstrategy, or some other BI solution, then please note they all have data model metadata layers similar to the report data model and they can also query the "live" source systems with generated SQL statements.
A word of advice: don't program a reporting solution against a production database unless you are building the reports into the underlying production application's interface. Even then, you probably still want to offload report queries to some replicated data source instead of the production OLTP system. You don't want someone's query about past sales to block an actual new sale. If you are building a reporting system for internal users only, then take the time to load the data into another server either via replication, log shipping, mirroring, backup/restore, etc., and then load the data into a data warehouse via SSIS or some other ETL solution. You really don't want a report to be the reason for a production system outage or latency issue.
