Fact table gets update every day with thousands of records, I been increase the aggregations manually. Is there a way I can increase aggregations (design storage) programmatically . My cub gets process ones in day with VB program. I want to increase aggregations before processing cub through VB.
It's argent
please help me
thanksCan you do a full process? If you're not running into time constraints, then I'd do a full process.|||As fact table's records increases, dont I have to increase the aggregation in the cub?|||No. Aggregations are dimension related, so as long as your not creating new dimensions then you don't have to worry about increasing aggregations. However, if processing time is not an issue, I'd do a full process.|||I am confused according to your statement
When I create cub with single records fact table the aggregations are zero.
When I create cub with thousands of records in fact table the aggregations are 850 or more.|||Here's what I'm trying to say:
The theoretical maximum number of possible aggregations in a cube is the product of the number of levels in each cube dimension. As you add levels and dimensions to a cube, the number of possible aggregations increases exponentially. The higher the number of dimensions and levels in a cube, the greater its complexity. In the example in Figure 2, the Time dimension has four levels, the Customers dimension has five levels, and the Products dimension has five levels. This yields a theoretical maximum number of aggregations of 100 (5 customer levels x 5 product levels x 4 time levels). However, this number increases exponentially as you add dimensions or levels. For example, if you add the Day level to the Time dimension, the theoretical maximum number of aggregations increases to 125 (5 x 5 x 5). Now, suppose you add two more dimensions to this cube, each with three levels. The theoretical maximum number of aggregations increases to 1125 (5 x 5 x 5 x 3 x 3). A cube with nine dimensions containing five levels each yields theoretical maximum number of aggregations of 1,953,125. A cube of this complexity is considered a cube of medium complexity. A cube of high complexity might have 20 dimensions with five levels each and yield a theoretical maximum number of aggregations of approximately 95 trillion. As you can see, you can directly affect the theoretical maximum number of aggregations in a cube by changing the number of dimensions or the number of levels. Having multiple dimensions with deep hierarchies improves the ability of users to perform analysis, but having too many of either can lead to resource problems during querying and processing.
Follow this link for more info...
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx|||Yes I understand that
What I did is, my fact table was MT (no records) I added one dummy record and created cub out of it, basically dimensions contains single level. I have 40 dimensions and 35 measures. I created a script out of it and sending the script to user to create the cub in their analysis server. In the script I had prompt for users data source name. Users fact table contains millions of records. When user processes the cub with millions of records the dimensions will have multiple levels. So user has to recreate the aggregations?
Sorry, I know you are trying to clarify my doubts but I am still confused.
Thank you so much.|||I think I remember now. You're sharing an identical cube structure with someone, but you're each pointing to different data sources, correct?
If that is the case, the user would have to re-process. Especially since the dimensions will have multiple levels once your script has run against their data. With the numbers you've provided, I wouldn't do a full process. However, again, aggregations are based on dimensions, so you don't need to programatically increase the number of aggregations as new records are added to the fact. Even if new dimension levels are being created, I wouldn't increase the number of aggregations. Just select a particular "performance gain level" and let AS do the rest (i.e. 30%). Having said that, you should probably look into usage based optimization. I have a similar sized cube as you've described and I know that there are a lot of wasted aggregations in the cube (fully processed). It only gets updated once a month, so it's not a big deal to do a full process. Remember, as your cube gets more complex, it's unlikely that your users are making the most of that complexity. That's why usage base optimization makes sense. I haven't done usage based optimization "programatically", so I'm no help there.
I hope that makes sense.|||Thanks so much
User doesnt know about analysis server.
I want to provide user to click option to design storage.
After crating the cub on user server,
I am looking for VB code to
1. Count dimension members
2. Design storage
So user doesnt have to do it manually .sql
Showing posts with label storage. Show all posts
Showing posts with label storage. Show all posts
Wednesday, March 28, 2012
Monday, March 19, 2012
INCORRECT DATE WHEN USING MOLAP AND HOLAP STORAGE.
When designing storage for my OLap cubes . I notice that the data is not correct when using MOLAP and HOLAP storage , but is absolutely correct when I use ROLAP .
I have a total of 204 aggregations.If I process only 18 of those aggregations ,the data is correct on all MOLAP HOLAP and HOLAP cubes . but if I go above 18 only ROLAP retrurns the correct data. If the data is correct in ROLAP shouldnt it be the same in MOLAP and HOLAP as well ?
No the data must be the same. I think you have wrong something else.Do you notice that data is double or greater?
Check partitions!!!
Try making only one partition first.
I hope it helps you.|||Well, the data is slightly different , I am using the following version of SQL
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
,only one Partition was created and that is the one done by the system while processing the cube . I am unable to create any additional ones beacuse this is the Standard version and it does not permit user defined partitions (I need the enerprise version to do that ) Could it be a problem with the version ?
I wish I could show you, its the weirdest thing ever . The Rolap cube is Spot on , processed to a %100 performance . POLAP and HOLAP are out by a little by about 2 % less that the correct data.It seems to be filtering out some of the data.
My problem is , it is Slooooooooooooowww , and not practical considering I'm working with a database with Millions of Records.
HELLLLLPPPP !!!!|||Let me give you an advice. Never proccess a cube more than 30% of performance.
If you have a large cube (more than 2 o 3 millons of records) and 15 or 20 dimensions then 5% - 15% of performance it's ok.
In my last job I have a cube with 30 millons of records and I have partitioned it monthly without aggregations. And the performance was good.
If you can't make partitions then proccess cube without aggregations.
Validate the cube structure and optimize it.
Have you many virtual dimensions? They are not good for performance.
Tell me if it improve the performance.
|||
To tell the truth , the cube was not optimized , I'll do that and get back to you .
I have no virtual dimensions present .
INCORRECT DATA WHEN USING MOLAP AND HOLAP STORAGE.
When designing storage for my OLap cubes . I notice that the data is not correct when using MOLAP and HOLAP storage , but is absolutely correct when I use ROLAP .
I have a total of 204 aggregations.If I process only 18 of those aggregations ,the data is correct on all MOLAP HOLAP and HOLAP cubes . but if I go above 18 only ROLAP retrurns the correct data. If the data is correct in ROLAP shouldnt it be the same in MOLAP and HOLAP as well ?
No the data must be the same. I think you have wrong something else.Do you notice that data is double or greater?
Check partitions!!!
Try making only one partition first.
I hope it helps you.|||Well, the data is slightly different , I am using the following version of SQL
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
,only one Partition was created and that is the one done by the system while processing the cube . I am unable to create any additional ones beacuse this is the Standard version and it does not permit user defined partitions (I need the enerprise version to do that ) Could it be a problem with the version ?
I wish I could show you, its the weirdest thing ever . The Rolap cube is Spot on , processed to a %100 performance . POLAP and HOLAP are out by a little by about 2 % less that the correct data.It seems to be filtering out some of the data.
My problem is , it is Slooooooooooooowww , and not practical considering I'm working with a database with Millions of Records.
HELLLLLPPPP !!!!|||Let me give you an advice. Never proccess a cube more than 30% of performance.
If you have a large cube (more than 2 o 3 millons of records) and 15 or 20 dimensions then 5% - 15% of performance it's ok.
In my last job I have a cube with 30 millons of records and I have partitioned it monthly without aggregations. And the performance was good.
If you can't make partitions then proccess cube without aggregations.
Validate the cube structure and optimize it.
Have you many virtual dimensions? They are not good for performance.
Tell me if it improve the performance.
|||
To tell the truth , the cube was not optimized , I'll do that and get back to you .
I have no virtual dimensions present .
Subscribe to:
Posts (Atom)