September 12, 2011 at 6:34 am
Hi Everybody,
I need advice.
I have a cube with large processing time. Can I do the following?
SQL Server is a standard version so I can't use partitions. I would like to create 2 cubes with the same dimensions and measures.
For example the first cube would contain data to 2011. 08. 31. and the second one would contain data from 2011. 09. 01.
Can I write mdx query which can union data from the two cubes?
Or has anybody a good idea instead of this?
September 13, 2011 at 1:13 am
Well, thats one way to do it. Maintenance will be a bit of a bugger though, and I'm not to sure what would happen if your hierarchies got out of sync? This will also affect scalability and overly complecate reports, after a while I feel it would negativly impact query responce time. I take it you cant aggregate your fact table any more? Can you slim down your hierarchies or security? Are your processing options correct (updates rather than full process etc) for each dimension? Hows your aggregation strategy? can that be adjusted?
In all honesty once you've confirmed those things are as slick as possible partitioning is the next place to go.
Hope this helps.
September 13, 2011 at 6:10 am
Thank you for your answer.
My cube structure is very simple. Behind the cube there is a view which is slow and complex and it cannot be simpler. There are 20 million rows.
How can I create 2 cubes (one for old data and one for recent) and a union mdx or anything else which make it faster?
September 13, 2011 at 8:41 am
If you have a complex view I would strongly suggest you look at turning it into a physical table. This will mean that a lot of work is done by the SQL engine prior to processing the cube and could be done out of hours (systems permitting). This will reduce your processing time. A view should only really be used as a layer of abstraction, at the most to conduct simple calculations (although they can be done in the DSV - not my preferred option)
If you try this and your still have issues you could create a second view (apply the relevant time filters) and recreate a duplicate cube and change your connection manager to look at the new view. Then you will be able to construct you MDX combining the two data sets. Although I would say this should be your last resort.
September 19, 2011 at 6:01 am
Hi wildh,
Thank you for your answer.
I followed your instruction. I created a physical table and I filled up from the view. The cube processing is much faster. Only the insert (and the selection from the view) is slow. It is not a big problem because I created 2 tables. One for the old and one for the recent data and I say a union all in my cube's datasource.
Just out of curiosity, how can I say a union in mdx query (one result from 2 or more cubes with the same dimensions and measures).
September 19, 2011 at 8:49 am
Your taking a path I've never had to walk. I've never tried unioning two cubes like this before, I've always had virtual cubes and partitions. You could try googling it.
September 19, 2011 at 8:55 am
You probably first need to look at the query plans for your view. Write indexing that might help the sql for the view process faster.
The overhead of building the reporting data might be eliminated if you fix the indexing issue. If you see 'Table Scan' in your query plan, you got work you need to do.
The first line of code starts with coffee. The last line ends with alcohol.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply