Many-to-many dimension processing performance

Hello,

I have a problem regarding M2M dimension processing performance that I would like some help with please.

We are using SSAS 2005 SP1 Entreprise Edition x64 bit
The main fact table has over a 100 milliion rows.
The entire SSAS database, both dims and facts, are MOLAP.

We have several many-to-many (M2M) dimensions connected to the main fact table via an intermediate measure group (IMG).
The M2M dim and the IMG are using the same database object as their source and are thus linked as fact relationship. This shared object is a 'normal' view not a table or indexed view.

We need to be able to process the M2M dim on a regular basis as user defined members change - sometimes every few minutes.

To do this we need to
1. Process Update the M2M dim
2. Full (or incremental) process the IMG
3. Process Indexes on the main fact

Steps 1 and 2 seem quite quick and arent currently thought to be a problem
Step 3, index processing, is taking too long, a few minutes or more, which our user base wont accept - we need to reduce this time.

Question
-
How can we reduce the time to process the indexes? Can we remove this step altogether in some way? Are there properties I can set to remove the process index necessity?

Please help if you can

--
Thanks in advance
Mgale1

You can try and skip step 3 all together and enable Lazy processing. This allows you to let users query the cube right after step 2. Analysis Server will be processing indexes in the background for you while users query.

This has obvious peroformance impact on the cube, users will be getting slower performance while indexes are not there and lazy processing is still going.

Another way to minimize time for building indexes is to patition you cube and let build index for several partitions to run in parallel. This should shorten processing times.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

Lazy Processing is enabled on the server; it is the default.setting.
I have also noticed that each fact or dim has a 'processing mode' available from the properties window.
I am confused as to where I should set this.
Do I need to set it on the M2M dimension, the Intermediate Measue Group, the 'local' dimension or the main fact table?

Please help if you can
Thanks
Mgale1

|||

As far as I understand the main time in your schenario goes for processing indexes for the partitions in the main fact measure group. These partitions the candidates for setting processing mode to LazyAggregations.

The rest of the objects should be relatively fast to process.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Many to Many - Performance & Aggregations

HI,

I am interested in knowledge about Many to Many and possible sideaffects on aggregations and performance - any recomandations? (links, helpfile, ...)

I am about to model a big cube with 10 measuregroups and 8 of this measuregorups have an many to many related dimension (the immidiate fact table for the many to many dimension has about 500.000 Records per day for 2++ Years finaly) and I want to use this for a imidiate measuregroup (the many to many is in relationship with two dimensions)

It is "article" published on a website per day - the website is the many to many dimension - because the article could be publised on this day on this 7 websites and on the next day on this 11 websites and we want to report how many articles are on this website with this possible revenue and much more.

Does analysis services design proper aggregations for the website if its a many to many relationship? Any sideeffect in performance because I have a huge amount of data? (and a lot of calcuated members)

I think it is better do model as three dimension (artice [100000 Members], time [1000 Members] and website [500 Members]) as MTM then one dimension with aprox. >100Mio members ?

The backgroud - I have setup an prototyp and it is not performing as expected....

THANKS, HANNES

This is from Christian Wade's Blog: "Note that many-to-many are not pre-aggregated across dimensions(analogous to a materialized reference dimension) and my understanding is that Microsoft curently has no plans to create a "materialized many-to-many dimension" "

Have a look at blogs.conchango.com/christanwade and "Many-to-Many Dimensions".

Regards

Thomas Ivarsson

|||

Doesn′t sound to good what you say. Is there any other place you know with information - because the website you mention seems to be offline now.

what design would you recommend? And how to best tune the many to many related queries ?

THANKS HANNES

|||

Hopefully the website will be online soon. This is the best guide I have found on this issue.

Regarding your data model you can try to have one measure group for each website to avoid the many-to-many problem with articles. I am not sure that this solution can solve all your problems. I think that you can find a simular discussion regarding(but in another business case) this problem on his blog when it goes online again.

Regards

Thomas Ivarsson

|||

Marco Russo is working on an extremely detailed paper on this subject, which will be published soon:

http://www.sqljunkies.com/WebLog/sqlbi/archive/2006/08/21/22495.aspx

Chris

Many left joins and slow performance

I am combining several columns and tables and I am wondering if there is a way to improve performance, for instance can I write an implicit join version of the following code, rather than having these explicit joins?

objCmd = new OleDbCommand ("SELECT MAmunicipalities.*, MAcitytown.*, countiesalias1.countyname, countiesalias1.countylinktitle AS clink1, countiesalias2.countyname, countiesalias2.countylinktitle AS clink2, countiesalias3.countyname, countiesalias3.countylinktitle AS clink3 FROM (MAmunicipalities LEFT OUTER JOIN MAcitytown ON MAmunicipalities.citytown = MAcitytown.citytown) LEFT OUTER JOIN MAcounties AS countiesalias1 ON MAcitytown.county1 = countiesalias1.countyname LEFT OUTER JOIN MAcounties AS countiesalias2 ON MAcitytown.county2 = countiesalias2.countyname LEFT OUTER JOIN MAcounties AS countiesalias3 ON MAcitytown.county3 = countiesalias3.countyname WHERE MAmunicipalities.municipality='plymouth'", objConn);

Sadly, I have even more joins left to add as well as other columns to add to the SELECT statement and more aliases to add as well. Any thoughts would be greatly appreciated.

So far, this appears to be a five table JOIN. That in itself 'shouldn't be a preformance issue.

Judicious usage of TABLE aliases would make the code a bit more readible (and maintainable).

And it is widely considered a 'best practice' to specifically identify columns instead of using [ SELECT * ].

For Example:

Code Snippet

SELECT
m.*,
c.*,
c1.CountyName,
c1.CountyLinkTitle AS cLink1,
c2.CountyName,
c2.CountyLinkTitle AS cLink2,
c3.CountyName,
c3.CountyLinkTitle AS cLink3
FROM MaMunicipalities m
LEFT JOIN MaCityTown c
ON m.CityTown = c.CityTown
LEFT JOIN MaCounties c1
ON c.County1 = c1.CountyName
LEFT JOIN MaCounties c2
ON c.County2 = c2.CountyName
LEFT JOIN MaCounties c3
ON c.County3 = c3.CountyName
WHERE m.Municipality = 'plymouth'

Are there additional tables to JOIN, or is it additional JOINs to the same tables (like with MaCounties above)?

|||I have additional tables to join. Right now it is taking between 10 and 15 seconds for a page to load using the code that is posted. I have indexed columns within those tables which has helped somewhat but it is still taking way too long. I suspect that having varchars being joined instead of ints is also contributing to the problem. Would a stored procedure help?|||

A stored procedure may help some -but its doubtful that it would provide the kind of improvement you really need.

As you suspect, the major issue is most likely the varchar() fields used for the JOINs.

One of the most significant things that help with speed on JOINs is the 'width' of an index. An integer field is 4 bytes. A varchar() field is as many bytes as characters (nvarchar() is double that.) The 'wider' the index, the fewer entries on an index page, therefore the more pages that have to be 'crawled' through and read to find the data.

Ideally, your CityTown and CountyName values would be integer values (CityTownID and CountyNameID).

I suggest that you explore using the Database Tuning Advisor to get assistance in fine tuning the indexes.

Refer to Books Online, Topic: 'Database Engine Tuning Advisor'

Managing Connections for optimal performance question, switch from Oracle to SQL

I was told in one of my systems classes that the real performance bottleneck in accessing information from the database was the opening of a connection from the application to the database.

To combat that problem I was advised to use a Singleton Factory pattern and to have that Factory instaniate a connection and open it, then pass references to that connection for all of the objects that it created. All of those objects passed the connection reference to the objects they created and so on. Basically that meant that I only ever had one connection open at any one time for my entire application. And I was able to implement this solution at my previous job where I was developing in Oracle. I primarially used OracleCommands and OracleDataReaders to get the informaton into and out of the database. I thought this was a very nice solution. Having this many DataReaders accessing a single connection was not a problem because OracleConnections don't get locked from having more than one DataReader open at once.

At my current job, however, I use SQL Server. I am concerned that the single connection will not work in my new enviroment as the SQLDataReaders lock up the connection while they are using it. If the information that I recieved about opening connections being the real bottleneck, then I am hesitant to have a connection instanciated and opened for each method, but I am concerned that a whole lot of errors will be generated if I use the single connection method. Also, how do DataAdapters effect my decision of which approach to use.

Any advice would be most helpful. If you have any questions that would help answer just ask. Thanks.No! Definitely not the way to do it. Just use ADO.NET's connection pooling to manage it. Out of the box it's pretty efficient, but you can tweak it if necessary. There's no way you'll write anything that will be as efficient as what's already there.

You're right that a data reader hogs a connection.

And the decision is the same whether you use data adapters or readers.

Don|||How do I use ADO.Net's connection pooling? Is there an article or something that you know about that can teach me to manage this problem better.

Thanks again for your help.|||That connection pooling stuff is pretty cool. My systems class was based on Java so apparantly that type of behind the scenes work didn't take place to efficiently manage the connections. I didn't realize that when I was instanciating a connection it was already managing a pool of connections for me. slick stuff.|||So you figured out how to use it? Cool. Yeah, it definitely is an area where all of Microsoft's hard work is paying off.

Don