Real time BI Solution

Question

Can anyone suggest what are the options to build real time BI solution?

I am looking to build OLAP, and I want to support my BI with no time delay.

Therefore I am thinking to get rid of Data warehouse step(ETL). Data warehouse was getting data from one data source only.

 

Answer 1

Hi,

The options are enabling proactive caching, if you can't enable proactive caching then enable ROLAP storage mode. Speaking from experience real time BI can be very resource intensive in terms of disk space and processing time. So I hope you have or are in the process of negociating with your client so that you only using real time BI when your business process rules require or your client has lots of money to throw at hardware.

Proactive Caching; - The server can listen for data change notifications and can update dimensions and measures dynamically.

Real-time ROLAP; - As with standard ROLAP, partition data and aggregations are stored in the relational database. The server maintains an internal cache to improve query performance.

Related links; - http://msdn.microsoft.com/en-us/library/aa216316(SQL.80).aspx

I understand that you need ETL even if you only have one data source. Please talk about why you believe your business processes guide you to not requiring ETL for your data warehouse.

I  hope this helps,

Kind Regards,

Kieran.

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds

Answer 2

According to another thread you opened, it seems the concept of “Real time BI Solution” includes two parts “real-time data warehousing” and “real-time OLAP”.

 

1)    You need to achieve Real-time data warehousing firstly because the cube base on DW. Generally, you can put triggers on the OLTP system so whenever there is a transaction inserted into the OLTP database, the triggers will sends the new record to the data warehouse. Of cause, this could be very complicated. Another one is near real time, you need to try to achieve mini-batch within several minutes, and this could be a normal ETL job with SSIS.

 

But if you familiar with Service Broker, I would suggest you try to achieve Real time DW with Service Broker, CDC (SQL Server 2008) and SSIS. More information, you can refer to this blog with sample:

http://blogs.msdn.com/b/sql_service_broker/archive/2008/07/09/real-time-data-integration-with-service-broker-and-other-sql-techniques.aspx

 

2)    Real-time OLAP: You can try to achieve that with Proactive Caching as Kieran posted. Another way is using Push mode to process cube, however, you have to use SSIS for the ETL job, because the only way to achieve push-mode I can think of is using SSIS. After each ETL transaction, you can push the data directly to the Cube. More information, you can refer to:

http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/cf817f65-c0d1-43c7-b28b-db59997426d2

You can also try to push data to the cube without data warehouse, but this only suit for a small project.

 

Hope this helps,

Raymond

---

Raymond Li - MSFT

Answer 3

Thank you for replies.

Option 2)

"You can also try to push data to the cube without data warehouse, but this only suit for a small project. "

What can we count as small project?

is that few hundreds update in database rows.

 

Answer 4

Thanks for your feedback. Can you give me some idea of scale please?
How many records do you anticipate having in your dimensions within your cube? Then we can probably focus more on answering your original question.

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds

Answer 5

For example, perhaps we need a cube with one fact table with several dimensions. And all we need is just a cube. We don’t need to record history in DW, we don’t need to expand the BI project, we don’t need to develop reports base on DW… However, DW is not only used for Cube, it can be used for data integration, reporting, history recording… So since you have created your ETL job to fit the cube with dims/facts then why don’t generate a data mart for Cube, and that’ the reason I don’t like to discussion this option. Anyway, to use push mode, you need to finish your ETL job with SSIS and then use SSIS to push data to the cube. For real-time OLAP, you can also refer to:

http://technet.microsoft.com/en-us/library/cc917689.aspx

 

Hope this helps,

Raymond

---

Raymond Li - MSFT

Answer 6

Thank you!

I will check that link.