April 7, 2015 at 12:00 pm
Hi
I created a ROLAP cube from oracle data source and it was successful and running fine for few days.
I used Excel as a reporting tool and creating a Pivot tables.
Later we are encountering a performance issue in the below scenario.
I pulled measure on columns, and 1 dimension to rows. This is working fine as we expected and coming in 2 minutes which is acceptable.
When I pull 2nd dimension attribute from same Dim table, then its taking hours and no response.
I checked in trace and when I put second attribute from same Dim table, its trying to check all dimension attribute columns and trying to pull the whole data of 100 million instead of only the pulled columns.
How do I fix this problem?
Do we have any way in ROLAP, instead of processing the dimensions and measure, SQL Server Relational Engine should just generate the SQL Query and pull it from oracle data source.
If I run the query that was generated by SQL Server Engine in Oracle its coming in 2 minutes.
Thank You
April 8, 2015 at 4:36 pm
Although old, the following article may help you to optimize a ROLAP cube (or at least point out some things that impact performance): http://download.microsoft.com/download/1/6/F/16F258DF-BB38-471B-AC4F-EC126DC9FE9B/SSAS%20ROLAP%20for%20SQL%20Server.docx
With that being said, have you considered an alternative solution like Power Query or Power Pivot? A few years back, ROLAP was the only option when you wanted "real-time" OLAP. But these days I think there are more options that provide a better alternative to ROLAP.
April 8, 2015 at 11:13 pm
Alternatively, you do have Microstrategy for ROLAP too. It works rather well compared to SSAS.
April 10, 2015 at 12:26 pm
PB_BI (4/9/2015)
xsevensinzx (4/8/2015)
Alternatively, you do have Microstrategy for ROLAP too. It works rather well compared to SSAS.Yes, go out and spend $600,000 per core to solve your problem 😀
If only that was accurate. I think the website is not updated last time I spoke to someone. You can pay per user seat.
April 10, 2015 at 1:14 pm
xsevensinzx (4/10/2015)
PB_BI (4/9/2015)
xsevensinzx (4/8/2015)
Alternatively, you do have Microstrategy for ROLAP too. It works rather well compared to SSAS.Yes, go out and spend $600,000 per core to solve your problem 😀
If only that was accurate. I think the website is not updated last time I spoke to someone. You can pay per user seat.
My point was less about what the *actual* cost is, more that it's silly to buy a piece of software because of one problem. Anyway, I hope your career at Microstrategy is working out for you. 😉
April 10, 2015 at 2:39 pm
Martin Schoombee (4/8/2015)
Although old, the following article may help you to optimize a ROLAP cube (or at least point out some things that impact performance): http://download.microsoft.com/download/1/6/F/16F258DF-BB38-471B-AC4F-EC126DC9FE9B/SSAS%20ROLAP%20for%20SQL%20Server.docxWith that being said, have you considered an alternative solution like Power Query or Power Pivot? A few years back, ROLAP was the only option when you wanted "real-time" OLAP. But these days I think there are more options that provide a better alternative to ROLAP.
Thank you for your reply Martin.
I am already using this document as reference and worked on this ROLAP cube. I was really happy when I first see the data coming up in 2 minutes using Excel Pivot. But later it started giving trouble.
We already used PowerQuery and PowerPivot tools and those are really a nice tool as per their standards. We do use the PowerBI tools for our dashboarding needs by writing custom SQLs.
But we have our data warehouse tables which are big in width and depth.
150 columns and more than 100 million records each. So I went for ROLAP concept since In-Memory concept wont work out in this case.
Thank You
April 10, 2015 at 2:43 pm
PB_BI (4/10/2015)
xsevensinzx (4/10/2015)
PB_BI (4/9/2015)
xsevensinzx (4/8/2015)
Alternatively, you do have Microstrategy for ROLAP too. It works rather well compared to SSAS.Yes, go out and spend $600,000 per core to solve your problem 😀
If only that was accurate. I think the website is not updated last time I spoke to someone. You can pay per user seat.
My point was less about what the *actual* cost is, more that it's silly to buy a piece of software because of one problem. Anyway, I hope your career at Microstrategy is working out for you. 😉
I think I can go for Business Objects as we already have that in house and have licenses 🙂
but we want to go with MSBI tools as we are moving to MSBI.
Thank You
April 10, 2015 at 3:05 pm
VRT (4/10/2015)
Thank you for your reply Martin.I am already using this document as reference and worked on this ROLAP cube. I was really happy when I first see the data coming up in 2 minutes using Excel Pivot. But later it started giving trouble.
We already used PowerQuery and PowerPivot tools and those are really a nice tool as per their standards. We do use the PowerBI tools for our dashboarding needs by writing custom SQLs.
But we have our data warehouse tables which are big in width and depth.
150 columns and more than 100 million records each. So I went for ROLAP concept since In-Memory concept wont work out in this case.
Understood, but why not use MOLAP (or at least HOLAP). If you have a close to real-time requirement, you could consider switching in partitions for data that recently changed.
In my opinion, any tool would struggle to aggregate hundreds of millions of rows over very large dimensions. You probably either need to reduce the scope (i.e. multiple smaller cubes), or find somebody with a heavy check book and spend a few million.
One last suggestion. Depending the version, have you considered using Oracle's equivalent of columnstore indexes for some of these large tables? It would definitely speed up any relational query significantly, if you could live with the nuances.
April 10, 2015 at 3:28 pm
PB_BI (4/10/2015)
xsevensinzx (4/10/2015)
PB_BI (4/9/2015)
xsevensinzx (4/8/2015)
Alternatively, you do have Microstrategy for ROLAP too. It works rather well compared to SSAS.Yes, go out and spend $600,000 per core to solve your problem 😀
If only that was accurate. I think the website is not updated last time I spoke to someone. You can pay per user seat.
My point was less about what the *actual* cost is, more that it's silly to buy a piece of software because of one problem. Anyway, I hope your career at Microstrategy is working out for you. 😉
Well, you brought up the costs and how high it was. So, why wouldn't your point be about the actual costs? If it's not, don't bring it up and just simply state, "why invest time into another tool instead of using the existing one?"
Then that's when I would chime in saying, "why wouldn't you use the right tool as opposed to the wrong one?" Then you would retort with, "This is the right tool." Then I would disagree and worlds will continue to collide.
So... It was just a general suggestion in case alternatives were considered. 😎
April 13, 2015 at 2:01 pm
Martin Schoombee (4/10/2015)
VRT (4/10/2015)
Thank you for your reply Martin.I am already using this document as reference and worked on this ROLAP cube. I was really happy when I first see the data coming up in 2 minutes using Excel Pivot. But later it started giving trouble.
We already used PowerQuery and PowerPivot tools and those are really a nice tool as per their standards. We do use the PowerBI tools for our dashboarding needs by writing custom SQLs.
But we have our data warehouse tables which are big in width and depth.
150 columns and more than 100 million records each. So I went for ROLAP concept since In-Memory concept wont work out in this case.
Understood, but why not use MOLAP (or at least HOLAP). If you have a close to real-time requirement, you could consider switching in partitions for data that recently changed.
In my opinion, any tool would struggle to aggregate hundreds of millions of rows over very large dimensions. You probably either need to reduce the scope (i.e. multiple smaller cubes), or find somebody with a heavy check book and spend a few million.
One last suggestion. Depending the version, have you considered using Oracle's equivalent of columnstore indexes for some of these large tables? It would definitely speed up any relational query significantly, if you could live with the nuances.
MOLAP is not accepted here due to governance rules as its a kind of duplication of data.
I will check your last option. I am not aware of column indexing availble in Oralce.
Thank you for your valuable inputs.
Thank You
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply