I have a problem with processing mdx queries after create ROLAP/HOLAP cube, if I create MOLAP cube everything works fine (processing time about 0-2000 miliseconds), but when I change cube structure to ROLAP/HOLAP, my mdx queries invokes very long time (20min + and they never end), or then they (Rolap/Holap cubes) throws the arithmetic overflow error. In my data warehouse I have about 20 milions (for US bilions) records. I use Visual Studio 2013 Data Tools and Microsoft SQL Server 2014.
Here is error which I get:
arithmetic overflow error converting expression to data type int 22003
I will be very grateful for help!
Data is preprocessed and stored in cube. So whenever an MDX query runs, it picks up aggregated data directly from cube. The complexity of MDX has no effect on the relational source of data underneath. Bottom line: It is fast and independent of data source(once the cube is processed).
Whenever an MDX query runs, it is in turn converted to SQL, crudely speaking, in the background and data is fetched from the relational data source. Since data is fetched real-time, these are obviously slow(As slow as a standard optimized SQL query. It can never match up to MOLAP in terms of speed, unless the MDX is very simple one. Also, since it is closely tied with the relational database, it generally comes with limitations associated with the relational database beneath(data type overflow, being one of them). Bottom line: It is real time but comes at the cost of being slow and dependent on the underlying relational data source.
It tries to combine best of both worlds but still has the same limitations of ROLAP.
Bottom most line!! Thus your queries are slow and error prone if you happen to use ROLPA/HOLAP.