Hi,
I am facing a performance problem. Here is my scenario:
I am using ProClarity to create reports/graphs by connecting to ssas 2005. These are not dynamic graphs but are created beforehand in proclarity and accessed by users. The graphs enable drill-down/drill-up features.
My Cube contains:
1 MeasureGroup
4 Measures
7 calculated measures
4 dimensions ( 1 time dimension: date)
dimension partition count:
1,095
4,018
8
453
4,018 (hierarchy)
11 (hierarchy)
7 (hierarchy)
5 hierarchies in time dimension
1 hierarchy in other dimension
1 partition, partition count: 33,574 (current)
partition size : 708.4 kb
storage: MOLAP
Partition aggregations: 15
current performance gain: 51%
This cube is processed manually and appx of 20,000 records are pumped everyday.
The data to be displayed is daily information in bar charts and pie charts.
The proclarity rendered graphs are hyper linked inside jsp pages to display 2-4 different graphs in one page.
The jsp pages are taking about:
2 seconds for the initial page components (non-ssas/proclarity based)
1-2 minutes+ for the proclarity graphs to appear.
10-20 seconds for drill down /drill up
1. What are the things that I need to change/address to get better performance?
2. How can I measure the performance of ssas 2005? (the cubes, dimensions.etc)
3. How can I measure the performance of ProClarity?
4. How can i detect if the bottleneck is in ssas 2005 or Proclarity?
5. Is my cube desing correct?
6. can i use usage based analysis?
7. can i move the performance gain to more than 51%?
Ok, sorry for the long post. but I kind of feel that I need to know more in-depth workings of ssas.
thanks in advance guys
Hello!
First, you do not mention if you have defined attribute relations for your user hierarchies in your dimensions? If you have, check if they are set to rigid or not. Also check that the cardinality of the attribute relations are correct. You can see this if you expand an attribute in the left pane of the dimension editor.
To detect bottlenecks you can run the profiler and trace what is happing in your cube.
Use Management Studio and paste the MDX from ProClarity professional and see if you get the same responstime. Also, when you run the same report in Professional, do you have the same slow responstime.
HTH
Thomas Ivarsson
|||Hi Thomas,
Thanks for the fast reply.
Here is the hierarchy relationship information:
I have the Date dimension which is from the relational table containing both attribute information columns and also the hierarchy info columns.
meaning I dont have multiple tables defining the hierarchy information.
To create the hierarchy I just dragged the respective columns from the Data Soruce View (left hand side) to the Hierarchies and Levels (center col). This has automatically created attribute relationships in the the dimesion.
The attribute relationships that were created automatically became attribute relationships to the key attribute of the Date. That is the other columns that I dragged to create hierarchies got added as attribute relationships to the key attribute of the dimension.
next, the relationship type was set as flexible > I have changed it to Rigid (now). Please tell me as to what this is and how it works internally?
the attribute relationships have cardinality set as Many, can i set it to One . I feel that I can set it to one. because the dimensions one key can be associated with only one hierarchy value. is this correct? do I change this?
in Proclarity: how do i get the mdx from proclarity?
using profiler and trace: I opened the profiler, but what is the query/xmla that i should be looking for, meaning > what should not be happening?
How to run the trace?
sorry I got in a lot of questions. hope the post is not cumbersome to read.
Thanks a lot
Regards
Vijay R
|||If you have a time user hierarchy like Year-Quarter-Month-Date you set the attribute relations like the following.
Expand the date-level in your user hierarchy(the central window) and drag Month as an attribute relation to date.
Expand the month level and drag quarter as an attribute relation.
Finally, define year as an attribute relation to the quarter.
Set each attribute relation as rigid.
This is a natural hierarchy so the cardinality is one two many from top and down to each level in the user hiearchy.
When you have done this you can remove the same attribute relations that are under the dimension key.
Start with this for each dimension. After you have finished all dimensions you will have to redefine your aggregations.
There is an MDX-editor under View (meny) in Professional.
Cardinality is like in a data model. A color attribute have one to may relation with the dimension key because a product can have many colors. A customer name have a one to one relation with the customer key.
HTH
Thomas Ivarsson
|||Hi Thomas,
I tried defining the Hierarchies as mentioned by you.
I removed all the attribute relationships from the dimension key. Then created the hierarchy with only one attribute the day-of-month. then added month, quater, year as attribute relationships to the hierarchy. browsing this gives just the days (1 to 31) repeatedly (days of month). There are no levels which can be used in the hierarchy.
I mean drilling from year > Quater > Month > day is not possible. Am i doing something wrong?
I require the drilling option, and I thought that defining hierarchy is the solution for it.
Hierarchy: Year-Qtr-Month-Day
Level/attribute : Day of Month
relationships to above attribute:
Month name, quarter name, year name
What am i missing? please help.
Regards
Vijay R
|||
Create a user hierarchy with the levels and these attribute relations.:
-Year(level)
-Quarter(level)
--Year(attribute rel)
-Month(level)
--Quarter(attribute rel)
-Date(level)
--Month(attribute rel)
HTH
Thomas
|||Follow Thomas's suggestion for creating strong hierarchies. That is the single most importatnt improvement you can make. You will need to redesign the aggregates to take advantage of the hierarchy changes you made.
With ProClarity, you need to take the generated MDX and test its timing in MDX query in Mgt Studio. This will give you the raw query timings. The total elapsed time of the ProClarity view includes the time to populate slicers and rendering the chart and running the MDX query as well. If the elapsed time is significantly higher than the base query, check that the slicers are not based on an attribute with too many members. Actions to take are to minimize slicers with a large number of members.
Secondly, if you are using a perspective chart and plotting around 10K points, it will be slower depending on the client PC. You need a minimum of 512mb memory and a decent processor to run charts that are rendering a lot of data.
|||If you have installed the SQL Server 2005 samples, there is a complete Adventure Works cube project, with attribute relations, that you can have a look at.
HTH
Thomas Ivarsson
|||Mosha has written about attribute relations in his Blog which you can find here(http://sqljunkies.com/WebLog/mosha/archive/2006/11/09/natural_hierarchy.aspx). There are also links to other whitepapers about this subject.
Chris Webb have a blog entry here(http://cwebbbi.spaces.live.com/blog/) "Designing Effective Aggregations in AS2005" where attribute relations is an important part.
regards
Thomas Ivarsson
|||Hi Thomas,
Thanks for the links.
Few more thoughts on this.
1) I have re-structured the time dimension with the necessary attribute relationships. but I had a slight difference, because i needed to use days_of_month, days_of_week also. Here is how I have structured the dimension its attributes and relationships. please do tell me if there is something wrong.
Structure:
Year (yyyy),
Quarter (Q1/2003,Q2/200x,Q3/200x,Q4/....),
>Year
Month (1,2...12),
>Quarter
Week (week starting from date),
>Month
Date (dd/mm/yy),
>Day_Of_Month
>Day_Of_Week
>Month
>Week
Day_Of_Month (1,2,....31) , Day_Of_Week (1,2...7)
The reason why I have both Month and Week as member properties of Date is because different users need to view either Week info or Month infor in their hierarchies. Is this right?
Here are the Hierarchies created:
Year_Qtr_Month_Day (Year>Quarter>Month>Day_of_month)
Year_Week_Day(Year>Week>Day_Of_Week)
Year_Month_Day(Year>Month>Day_Of_Month)
Year_Month_Date(Year>Month>Date)
2) I have set the Date attribute as usage KEY and key column pointing to the key in the database table. The namecolumn points to the Date column in the database. The attribute TYPE property is set to "Date".
similarly the TYPE property of the attribute is set for all attributes accordingly: Year->Years, Week->Weeks, Quarter->Quarters, Month->Months, Day_Of_Week->DayOfWeek,Day_Of_Month->DayOfMonth, Date->Date (attributes in Bold)
AttributeHierarchyEnabled=True for all, AttributeHierarchyOptimized=True for all, AttributeHierarchyVisible=True for all.
3) KeyColumns of attributes: currently I have set the KeyColumns of the attributes to point to their respective database columns. but from what I have read in some post, we need to set the KeyColumns to multiple keys (composite keys) to form a collection.
What is the correct design for this?
Do we have to add composite keys also cascading in the similar way the attribute relationships are designed?
for example: Quarter has keyColumn collection of (column bindings): Quarter, Year
then does Month keycolumn collection need to have only Year or both Quarter and Year?
I know that this is important for the correctness of data, but does it not depend on how the relational database tables are modelled?
for example, consider my relational database Date table:
Id : xxxx
Date : 01/01/2003
Day_Of_Week : 1-7
Day_Of_Month : 1-31
weekId : wwww
week : Week Starting 01/05/2003 (1/12/2003,1/19/2003.....)
monthId : zzzz
Month : 01/2003 (01/yyyy - 12/yyyy)
quarterid : qqqq
quarter : Q1/2003 (Q1/yyyy - Q4/yyyy)
yearId : aaaa
year : 2003 (yyyy)
(Note: the QuarterId, WeekId, MonthId columns are not used, they were existing to join information from seperate month, quater and week tables. we do not use them now.)
In the above sample data, quarter, month and similar ones are stored with complete information, that is with month,year information. The table does not store only Q1,Q2,Q3,Q4 but stores Q1 with corresponding year name (Q1/2003) similarly for Month (01/2003). In this case we dont need the composite keys in the attribute keycolums, right?
4. In this case the data correctness should be ensured by the relational database itself, right?
5. Now because of this, think of the performance, we are storing redundant data, when the time intelligence of ssas could have handled it. we had to only store four values for the quarter and 12 values for the month. but we are ending up in storing as many values as there are dates. Is this relational database design correct? How do we correct this?
6. Finally, most of the articles describe setting the attribute's AggregationUsage property. Where is this property? I checked the attribute property list but it does not have anything like it. please throw some light on this too.
apologies for the lengthy post thomas, but did not want to break this up into seperate posts.
Had forgot about this, so editing again....
7. Slicing the partiton: I tried slicing the Date partition, so that if they are sliced by year, the performance would improve because of the reduced size of the query set. but I could not do this. the BI studio kept giving errors. not sure what are the pre-requisites for this and how to do this correctly?
8. coming back to the original problem, that the proclarity graphs were coming in slow. I was investigating on it and found that the graph has a dimension in the slicer, which is displayed to the users for selecting the customer they want to see graphs about.
This customer dimension contains about 1500 records. the dimension has only one attribute, whose keycolumn is pointing to the key column in the database table and whose NameColumn is pointing to the customerName column in the database table.
If I remove this dimension from the proclarity slicer (users can't select which customer they want in the dropdown). but remember I will still keep it in the proclarity's background column. Now the graph is BLAZINGLY fast. the performance difference is about 10 times. I can still drill-down on the customers, but can't select one by one using a drop down.
So what is wrong? Is there something else that needs to be done in proclarity, ssas?
Thanks and Regards
|||1. The only problem I can see with your time-hiearchies is that week and month can split each other. A month can change within a week and a week can cross a month. So that hierarchy is not correct. Instead you point the week to a year, but you will need a standard to handle weeks when they cross a year. Many countries have different standards for this and if you use the TSQL function week you can get some strange results. Do not forget to make your attribute relationsships rigid.
2. Everything looks OK.
3. You will only nead a composite key if you use 1,2,3,4 for decribing quarters. If you have an int or a string like 20061,20062,20063,20064, you are OK. The same problem for months.
4. Not sure what you mean by this.
5. Not sure what you mean by this either.
6. You do this on the first tab in the cube editor. Right click on the dimension down in the lowest left corner.
You will find a goof Blog post on the subject here(http://www.sqlskills.com/blogs/liz/2006/07/03/InfluencingAggregationCandidates.aspx)
7. Partions are only supported in the Enterprise edition. Check the adventure works cube project(samples). You should create partitions by using queries. You can test the TSQL generated for partitions in Management Studio.
8. I will have to think a second time about this. What version of Proclarity are you using (6.2)?
Late Edit. I have sliced(by date, month,quarter,year) the Adventure Works Enterprise edition cube on my laptop, without finding any long response time. For this I have used ProClarity Professional 6.2 .
Regards
Thomas Ivarsson
|||Hi Thomas,
Thanks a lot for the reply.
I am using ProClarity 6.1 and Enterprise version of analysis server 2005. (not sure if I need an proclarity update or a fix)
Now...
1. Because of one of the performance optimizations that I did, I can't specify slicerSel parameters to my proclarity graphs! Let me explain:
I have a slicer in Proclarity graph for which I used to specify a url querystring parameter as slicerSel=xxxxxx. This was working until I modified the dimension.
The modification: initially my dimension had the following attributes: Key, Name. Now I just have one attribute Name, whose key column points to KEY (db) and Name column points to NAME (db). because of this my slicerSel parameters do not work.
I checked the html of proclarity by doing an view souce and found the following difference.
before:
<option value ="[CLIENTNAME].[NAME].&[ABCDE FGHIJK]">ABCDE FGHIJK<option>
After modifing the Keycolumn and Namecolumn:
<option value ="[CLIENTNAME].[NAME].&[4.94735E6]">ABCDE FGHIJK</option>
Now I can't specify the slicer using the number 4.94735E6 which i dont think is the key (its in scientific format).
My question is, Is there any way out to specify the slicer as ABCDE FGHIJK as before ?
If it is not possible, shall I add another attribute to the dimension called Key and then seperate the Key and Name from the single attribute? will this impact the performance? especially because the dimension will not have another attribute which contains just the key, which will not be displayed but used in the relationships. Is there any specific way to design this? (Key is the primary key column of the database table, which is used for storing dimension values).
Regards
Vijay R
