Recently I have been working for a financial institution who are using Analysis Service 2005 for their ad-hoc analysis. This particular client was experiencing issues with one of the Analysis Services servers producing memory mini-dump files during processing and causing service crashes and stalls whilst other servers were performing acceptably well.
The analysis of this issue started towards the end of 2007 and the reason for this has only now been diagnosed with the able help of Microsoft support. The particular system setup for this client is quite complex, using Oracle 10gR2 as the back-end relational database using flashback for referential integrity and consistency, the 11g (11.1.0.6.0) 64 bit OLE DB driver for connectivity from AS to Oracle and SSIS for scheduled processing. The AS database also makes extensive use of linked measure groups in order to provide a single cube of data with mixed granularity.
In order to diagnose the cause of the heap corruption we had to jump through quite a series of hoops. We initially went down the route of sending the mini-dump files to Microsoft for analysis but these naturally only provided limited and static information. Sending full dump files proved very tricky in the short term as the information that the cubes contain is confidential and cannot be sent off-site. In the end, restarting AS and only processing the dimensions and cubes (i.e. no queries being sent to AS) proved adequate to be able to send the full memory dump files off-site, as it would be extremely hard to put this in-memory information together in any meaningful way.
Still, the full dump files are also static and thus proved inconclusive. It could be seen from the dump files that heap corruption was indeed occuring during processing and there always appeared to be an Oracle error in the dump file as well. It seemed logical that the Oracle 11g driver could be the cause, as most clients tend to use the 10.2.0.3.0 Oracle OLE DB driver when connecting to Oracle 10.2.0.3.0 or later. In order to try and eliminate the 11g driver as the cause, we backed out the 11g driver and installed the 10.2.0.3.0 driver. (Note that in true Oracle fashion, this proved to be an exercise in and of itself. There is no Oracle 10.2.0.3.0 64 bit client installation. Noooooooo, that's waaaaay too easy! You have to install 10.2.0.1.0 and then patch it to 10.2.0.3.0. Oh, but wait, there is a patch before you apply the patch in order to get the patch to work etc. yadayadayada.) Anyhoo, I digress...
In the end, we found that using the Oracle 10.2.0.3.0 driver did not fix the problem. The next step then, was to divide and conquer so to speak...
Part one was to try and create a reduced test case by systematically removing complexity from the AS database. This was achieved by first removing the linked measure groups (I'm not aware of many people using this feature) and then to remove cubes and dimensions until a simple AS database could be created in which the issue could still be reproduced. The idea of this approach was to be able to get a small AS database with a tiny amount of non-confidential data so that Microsoft could perform in-house testing.
Part two was to use Time Travel Trace testing (TTT) which effectively allows one to debug the issue by being able to play forward and rewind the process over a given time span. This would allow one to see the changes made to the heap and replay them to identify the point at which the heap was corrupted. Rather amusingly though, there is a slight catch. This system was using AS2005 on Windows Server 2003 as are most installations I would suspect. However, TTT only works on Windows Server 2003 on a 32 bit architecture or Windows Server 2008 on a 64 bit architecture or if virtualised, only on Hyper-V.
Right, so a few changes to the test case then. As it happened we used a 32 bit environment and as luck would have it we were actually able to reproduce the issue, though TTT failed to work. Initially it exhibited the same symptoms as seen when used on Vista, where it "watched" the incorrect "thread" for activity. This should have been fixed be setting the processor affinity for AS. It wasn't...
As it happens, the TTT testing wasn't required. The reduced test case was effective and we were able to remove all confidential data. Microsoft took this in-house and managed to find the answer. So, are you ready for this? Do you want to know why memory heap corruption was occurring during processing? Well, do you?
Rather bizarrely, the reason for the heap corruption was the use of the ".Properties" function when specifying the default member in a dimension. Yup, the use of a specific function when specifying the default dimension member caused heap corruption during processing, not querying but processing. So how lucky am I that the reduced test case that I created used one of the only two dimensions out of 30 odd using that function when specifying the default member? Now I'm not sure why that function causes the issue and nor are the AS development team. They are still looking into it and from what I understand, were equally surprised. As it happens, the MDX that was being used to specify the default member wasn't particularly efficient in the first place (I must point out that it wasn't my code) and that once the code was changed to use the "Exists" function, it solved the problem.
Clearly your mileage may vary but nonetheless please be aware that the use of the ".Properties" function when specifying the default dimension member can cause heap corruption during processing. This is true for both AS2005 CU11 (or SP3 CU1) and AS2008 CU2. I'm still not sure why this only occurred on one of the servers when the other servers have the exact same database build but hey, removing the ".Properties" function solved the issue so clearly that was the problem.
You have been warned...
Monday, 2 February 2009
Tuesday, 21 October 2008
Is Oracle AWM 11.1.0.7.0A Better?
So, I'm about to present at a workshop tomorrow on Oracle 11g's Interval Partitioning and on the OLAP option and in preparation for the event I have had to create a demo. A while back I patched our 11.1.0.6.0 relational database to patch 8 but although both patch 10 and 11.1.0.7.0 have since been released, I decided not to risk upsetting things too much by installing those and have thus gone with the status quo. (Note that we have lots of Oracle software on that machine for demos and breaking things at any time is just not on!)
Incidentally, I just have to ask, does anyone actually know what each number in Oracle's version numbers stand for? I searched a while back but to no avail. But I digress...
Searching around on 11g versions and patches I came across an article that said that in order to patch the OLAP option you need to install either Patch 'A' or Patch 'B' but in order to patch the relational side of the engine you use the Patches 6, 8, 10 etc. Huh? When I installed patch 8 it had to back out patch 'A' (which is what the person who posted on the forum also found) so does that mean that the OLAP patches are mutually exclusive from the relational patches? That's a question I have yet to answer 'cos maybe you are meant to re-apply Patch 'A' after patch 8 etc. but that's a subject for a later post. (Especially since 11.1.0.7.0 has been released which appears to be for both relational and OLAP with patch #6890831. See the OLAP Certification page.)
Anyhoo, my big grumble with the Oracle Analytic Workspace Manager is that it seems to contain more bugs than an anthill. Seriously, does anyone actually test this thing? Within 2 minutes of using it I can get it to break. Lol, now I appreciate that my ineptitude is clearly a culprit in this issue in that if I did everything correctly it wouldn't break. However, exceptions are just that, exceptions and they need to be catered for.
Documentation seems to indicate that the preferred method of creating analytic workspaces etc. is to use Oracle Warehouse Builder and that the Analytic Workspace Manager is for "end users" who don't have access to the ETL/ELT process. It can also be used where OWB is not the ETL/ELT tool being employed.
Really? Whenever an exception is thrown I seem to get some Java exception ramblings about not being able to create a cursor or transaction or something equally inane. I'm sorry. It's coding like this that gives Java a bad name! Issues such as not being able to switch between a "star-schema" dimension and "snowflake" dimension after it has been built. Sure, you click on the drop down box and select it but sometimes the relevant member is selected but you don't get the correct representation in the mapping pane (i.e. visible/invisible "parent" member) or it just won't select.
Other irritations include:
Does anyone on the development team actually use the product in a way that is not based on contrived samples? We all know that clinical test cases will work but what about the real world?
After all that, is the new version of AWM better? Yes. The fact that you can now create dimension levels at the time when you create a dimension (which I always thought was odd that you couldn't) is accepted with open arms. Despite all the ramblings above, it does seem a little more stable though that really isn't saying much. The default way of mapping columns to OLAP structures has changed from the graphical mapping view to the table mapping view which I think is for the better.
Bottom line, if you haven't switched yet, switch now. It's better for sure but don't expect an "Analysis Services development environment (i.e. BIDS)" type experience or you'll be sorely disappointed!
Incidentally, I just have to ask, does anyone actually know what each number in Oracle's version numbers stand for? I searched a while back but to no avail. But I digress...
Searching around on 11g versions and patches I came across an article that said that in order to patch the OLAP option you need to install either Patch 'A' or Patch 'B' but in order to patch the relational side of the engine you use the Patches 6, 8, 10 etc. Huh? When I installed patch 8 it had to back out patch 'A' (which is what the person who posted on the forum also found) so does that mean that the OLAP patches are mutually exclusive from the relational patches? That's a question I have yet to answer 'cos maybe you are meant to re-apply Patch 'A' after patch 8 etc. but that's a subject for a later post. (Especially since 11.1.0.7.0 has been released which appears to be for both relational and OLAP with patch #6890831. See the OLAP Certification page.)
Anyhoo, my big grumble with the Oracle Analytic Workspace Manager is that it seems to contain more bugs than an anthill. Seriously, does anyone actually test this thing? Within 2 minutes of using it I can get it to break. Lol, now I appreciate that my ineptitude is clearly a culprit in this issue in that if I did everything correctly it wouldn't break. However, exceptions are just that, exceptions and they need to be catered for.
Documentation seems to indicate that the preferred method of creating analytic workspaces etc. is to use Oracle Warehouse Builder and that the Analytic Workspace Manager is for "end users" who don't have access to the ETL/ELT process. It can also be used where OWB is not the ETL/ELT tool being employed.
Really? Whenever an exception is thrown I seem to get some Java exception ramblings about not being able to create a cursor or transaction or something equally inane. I'm sorry. It's coding like this that gives Java a bad name! Issues such as not being able to switch between a "star-schema" dimension and "snowflake" dimension after it has been built. Sure, you click on the drop down box and select it but sometimes the relevant member is selected but you don't get the correct representation in the mapping pane (i.e. visible/invisible "parent" member) or it just won't select.
Other irritations include:
- After defining a cube you can click on the cube name in the schema tree and even though the dimensions are greyed out, you can double click an available dimension and it will appear to be added to the cube's dimension list. It hasn't really, click off the cube and come back and the model is back as expected.
- Try creating a dimension over the "Sales Territory" dimension as available in the Microsoft AdventureWorks database and it will build. You can even browse it. However, join that dimension to a cube built off of the Internet Sales fact table and whoa... nasty exception.
- Why does the above nasty exception terminate my connection and cause random stuff to happen?
- How come building cubes off a database using the WE8MSWIN1252 characterset causes ORA-12704 characterset mismatch errors seemingly at random? Sometimes when I merely process something but always when trying to create cube organized materialized views. Sure I should be using UTF8 but the default for the "warehouse" database created by dbca is to use WE8MSWIN1252. What gives with that?
- ...
Does anyone on the development team actually use the product in a way that is not based on contrived samples? We all know that clinical test cases will work but what about the real world?
After all that, is the new version of AWM better? Yes. The fact that you can now create dimension levels at the time when you create a dimension (which I always thought was odd that you couldn't) is accepted with open arms. Despite all the ramblings above, it does seem a little more stable though that really isn't saying much. The default way of mapping columns to OLAP structures has changed from the graphical mapping view to the table mapping view which I think is for the better.
Bottom line, if you haven't switched yet, switch now. It's better for sure but don't expect an "Analysis Services development environment (i.e. BIDS)" type experience or you'll be sorely disappointed!
Subscribe to:
Posts (Atom)