Hi,
I am new to OLAP and was reading on this area on the web. I come across this statement like:
"while the relational database (OLTP) updates...the OLAP database will not be updated. "
I am not sure of the above statment.
In that case the data in OLAP is not the latest ? and how is it going to be updated? Thanks.
Regards
Alu
The OLAP database(Analysis services 2000/2005) is a separate engine and structure from the relational data warehouse or OLTP system. But every time you process an OLAP cube it will update with new records from the data source.
There is an exception to this with a possibility to update cubes very close to immediate time. Normally you will update a cube the day after an OLTP-system.
HTH
Thomas Ivarsson
Is there a way to update/process automatically or must it be manual? How is it possible/feasibility to update the cube very close to immediate time? :)
Regards
Alu
On the partion tab i BI-Dev Studio you have different setting for MOLAP(lower or higher latency) that you can try. I think this is a continous processing of cubes when new records arrive.
You can schedule cube processing in a job in SQL Server(SQL Server agent) or in a SSIS package.
HTH
Thomas Ivarsson
oh one more question how do I verified that an application (with an OLTP database) needs an OLAP database. I mean what are the factors considered in order to set up OLAP database? I know that if huge volume of data is involved its wise to use the OLAP as well. But how large is large? and any considerations? ( for i am looking into the feasibility of implementing OLAP in my application. Thanks.
Regards
Alu
OLAP is best for top level queries like analysing sales in different countries or regions within countries. The performance of an OLAP engine is always superior to a relational engine on top level queries.
Relational reporting systems will have to calculate all these aggregates at run time.
The problem for OLAP engines, like Analysis Services is when a user would like to combine leaf level products with leaf level customers on a single axis in order to analyse sales. OLAP engines will do a relational cross join, in this case, and combine every product, with every single customer. If you have 10.000 leaf level members in each dimension you will have a problem.
Relational queries, in this case, will better, because they will only show products that each customer have bought.
HTH
Thomas Ivarsson
In other words we have to be selective in the type of reports cater to the customers while certain types of combinations required a relational db to query while others need an OLAP db to use. We can query both dbs on a same application right?
Regards
Alu
Does it mean cubes are related to MDX queries whereas OLTP corresponds to sql queries and they cant cross reference to each other? (ie using mdx on OLTP and sql queries on OLAP),thanks.
Regards
Alu
With reporting services / report builder you can query relational sources and Analysis Services 2005.
Analysis Services also have actions that some OLAP clients support, like Excel 2007 and ProClarity Professional. An action will link members in a dimension with reports in Reporting Services.
HTH
Thomas Ivarsson
Correct. Analysis Services uses MDX and OLTP, Data warerhouse and Data Marts use SQL/relational queries. You cannot mix them in the same query.
The only product that use a separate layer against Analysis Services and Relational stores is Report Builder(part of Reporting Services).
You build report models in BI-Developer Studio against both type of sources and Report Builder use these report models for quering. You do not have to write any code.
HTH
Thomas Ivarsson
