To say things have been quiet on here would be an understatement but there is a very good reason for this, content is going to be moved somewhere else and a lot more of it will be produced (minus the things I'm currently listening to).
So what more can I say but watch this space............
Friday, September 30, 2005
Tuesday, August 23, 2005
Back to the slog
After what has been a pretty wasteful month so far culminating in an excessive trip to the 2005 V Festival I'm back on the more serious subject of work starting with a small tip in Analysis Services that I see get missed a lot on my travels.
You've got a dimension, say a time dimension that has levels of fiscal year, fiscal period and fiscal year. You have 2 cubes, one that looks at data at week level and another looking at period level for one reason or another. On many occasions I've seen a second time dimension added for the period level cube (or similar scenario) when all that needs to be done is disable the fiscal week level in the time dimnesion in the period cube.
It's pretty easy to find, in the cube editor open up the dimnesion whose level you want to disable, highlight the level and in the advanced settings set 'Disabled' to Yes and there you have it, one less dimension, member properties and performance hit to worry about.
You've got a dimension, say a time dimension that has levels of fiscal year, fiscal period and fiscal year. You have 2 cubes, one that looks at data at week level and another looking at period level for one reason or another. On many occasions I've seen a second time dimension added for the period level cube (or similar scenario) when all that needs to be done is disable the fiscal week level in the time dimnesion in the period cube.
It's pretty easy to find, in the cube editor open up the dimnesion whose level you want to disable, highlight the level and in the advanced settings set 'Disabled' to Yes and there you have it, one less dimension, member properties and performance hit to worry about.
Tuesday, August 16, 2005
Monday, August 01, 2005
Normal Service Has Been Resumed
My deepest apologies for anyone who takes the slightest bit of notice of this blog but I am currently sitting in the "internet cafe" within the complex I'm staying at in Lanzarote on my honeymoon (there's dedication for you) and have found out that my blog was not functioning in anything but Firefox, whoops. I believe those problems have now been rectified but would appreciate a mail to yourblogisrubbish@stevemchugh.com if you spot any other bloopers. As a good friend recently commented, you#ll never be a web developer steve....
Wednesday, July 27, 2005
Monday, July 18, 2005
Wednesday, July 13, 2005
Tuesday, July 12, 2005
2D 3D
Last week was a titanic one for me to say the least. My ongoing battle shoe horning Analysis Services data into Reporting Services continues with the completion of another 40 reports to add to the others completed so far on the project I've been working on.
To try and make my life a little easier I decided to spend a little time working on a way of easily tweaking each report to minimise the amount of work necessary to complete the report suite.
Bit of background, the report I'm working on has 3 alternate hierarchies in a store dimension which need to be drillable (whilst displaying different subreports at each differing level of each alternate hierarchy). On another axis is a crossjoin of a subset of the measures and what could be best compared to as a product dimension. A better explanation maybe would be to try and visualise the report....
Standard matrix with the first row group displaying a list of the children of the current store hierarchy parameter value. Row group 2 is a list of the children of current product hierarchy parameter unioned with a list of measures which effectively gives you the sales value for product sub categories A B and C and then things like number of stores and number of units sold for the parent category of A B and C.
Finally the column group was driven from a named set containing the previous 14 fiscal periods. As I previously said the report was drillable by store but also by product. This meant that a single report to the user was in fact built up of 40 reports linked and passing parameters between themselves to cater for every level in every dimension multiplied by 3 due to the alternate hierarchies.
What I ended up doing was removing the 'database' fields from the matrix and replacing them with calculated fields meaning I didn't really have to touch the matrix for the changes but only the calculated field for each separate report.
This may seem like going the long way around but for this requirement and this set of data this was quite literally the only solution and meant I was producing reports in several minutes along with thorough testing. Anyway I just thought I would share that one.
To try and make my life a little easier I decided to spend a little time working on a way of easily tweaking each report to minimise the amount of work necessary to complete the report suite.
Bit of background, the report I'm working on has 3 alternate hierarchies in a store dimension which need to be drillable (whilst displaying different subreports at each differing level of each alternate hierarchy). On another axis is a crossjoin of a subset of the measures and what could be best compared to as a product dimension. A better explanation maybe would be to try and visualise the report....
Standard matrix with the first row group displaying a list of the children of the current store hierarchy parameter value. Row group 2 is a list of the children of current product hierarchy parameter unioned with a list of measures which effectively gives you the sales value for product sub categories A B and C and then things like number of stores and number of units sold for the parent category of A B and C.
Finally the column group was driven from a named set containing the previous 14 fiscal periods. As I previously said the report was drillable by store but also by product. This meant that a single report to the user was in fact built up of 40 reports linked and passing parameters between themselves to cater for every level in every dimension multiplied by 3 due to the alternate hierarchies.
What I ended up doing was removing the 'database' fields from the matrix and replacing them with calculated fields meaning I didn't really have to touch the matrix for the changes but only the calculated field for each separate report.
This may seem like going the long way around but for this requirement and this set of data this was quite literally the only solution and meant I was producing reports in several minutes along with thorough testing. Anyway I just thought I would share that one.
Monday, July 11, 2005
Thursday 7th July
It's been quite an eventful past 10 days here. My stag do, London winning the Olympics and finally the bombings in London which has devastated so many innocent people.
After so many highs it's difficult to conceive how low things can get. Much as I doubt that anyone this statement is directed at will ever see this I just wanted to give my condolences to all the friends and family of the people whose lives were lost because of Thursday’s event.
After so many highs it's difficult to conceive how low things can get. Much as I doubt that anyone this statement is directed at will ever see this I just wanted to give my condolences to all the friends and family of the people whose lives were lost because of Thursday’s event.
Monday, July 04, 2005
Sunday, June 26, 2005
Thursday, June 23, 2005
MIssing a Trick
In the world of OLAP tools Analysis Services pretty much sits on top the heap. No one really denies it except a small band of nomadic Oracle developers who cling to the hope that Express will rise like a Phoenix from the ashes. I’m not one of those people who draws a huge line between the Oracle and Microsoft camps denying to the one that the other doesn’t exist and it’s probably something that MS should think about when they are bidding in the area of Business Intelligence.
I know of at least two cases where Microsoft were in a good position to implement a BI solution in companies that were effectively wall to wall Oracle and failed due to their insistence on predominately pushing the database server and not the other tools in the SQL Server suite. Think about it, I have no centralised reporting, no in house ability to perform slice and dice analysis but what I do have is a shed load of Oracle Systems and the DBA’s and administrators that go with it. Even if the data warehouse is a new build you’re not going to ignore the skills you have in house but I think in these cases Microsoft has.
An enterprise Analysis Services license is going to cost the same as the SQL Server database engine so push the market leading OLAP product and get the money that way. Maybe try pushing a great reporting tool like Reporting Services in addition to Analysis Services, an even more attractive proposition. If the organisation is open to bringing in Microsoft server tools but Oracle is the database of choice, market yourselves in that company by first showing what that product can do. This is probably going to be more the case when 2005 is releases and SSIS comes into the fold.
In the meantime, where the situation arises that Oracle / Microsoft mixed implementations are the best for an organisations particular situation I will carry on recommending it until something changes my mind.
I know of at least two cases where Microsoft were in a good position to implement a BI solution in companies that were effectively wall to wall Oracle and failed due to their insistence on predominately pushing the database server and not the other tools in the SQL Server suite. Think about it, I have no centralised reporting, no in house ability to perform slice and dice analysis but what I do have is a shed load of Oracle Systems and the DBA’s and administrators that go with it. Even if the data warehouse is a new build you’re not going to ignore the skills you have in house but I think in these cases Microsoft has.
An enterprise Analysis Services license is going to cost the same as the SQL Server database engine so push the market leading OLAP product and get the money that way. Maybe try pushing a great reporting tool like Reporting Services in addition to Analysis Services, an even more attractive proposition. If the organisation is open to bringing in Microsoft server tools but Oracle is the database of choice, market yourselves in that company by first showing what that product can do. This is probably going to be more the case when 2005 is releases and SSIS comes into the fold.
In the meantime, where the situation arises that Oracle / Microsoft mixed implementations are the best for an organisations particular situation I will carry on recommending it until something changes my mind.
Tuesday, June 21, 2005
Project '4' REAL
Take a look at this site for Project REAL. There was a number of sessions on it at PASS Europe that were really interesting. It's essentially Microsofts attempt to build a Buissiness Intelligence system using SQL Server 2005 and real data for a well known on-line retailer in the U.S. This is going to hopefully be incredibly useful for not only developers observing the kinds of issues that they will come up against during their own implementations but also for managers needing to plan SQL Server 2005 based BI projects.
There's a couple of sites, one is the original technical overview here by Len Wyatt and now the Project REAL website which is going to be an ever expanding information resource on the progress of the project. There is already a link to a piece on SSIS detailing lessons learnt during the ETL design process and is probably essential viewing for anyone with an interest in the ETL space.
I'm lead to believe there will be a number of webcasts as well as the content available on the site which will increase ramping up more and more the closer it gets to the 2005 launch date (November currently and looking certain) so I'm sure there will be something for everyone to be interested in but if you're looking for reporting services info expect it to be towards the end of the project :)
There's a couple of sites, one is the original technical overview here by Len Wyatt and now the Project REAL website which is going to be an ever expanding information resource on the progress of the project. There is already a link to a piece on SSIS detailing lessons learnt during the ETL design process and is probably essential viewing for anyone with an interest in the ETL space.
I'm lead to believe there will be a number of webcasts as well as the content available on the site which will increase ramping up more and more the closer it gets to the 2005 launch date (November currently and looking certain) so I'm sure there will be something for everyone to be interested in but if you're looking for reporting services info expect it to be towards the end of the project :)
Thursday, June 16, 2005
Diet Projects
I’m currently trying to put together a full end to end demo of SQL Server 2005 based on an existing implementation I’m working on (You could call it my own little Project Real Lite). This is an Oracle data warehouse with Microsoft presentation tools and as I really need to demonstrate all aspects of SQL Server 2005 I need to pull the data out of Oracle and into the database engine so first up is SSIS.
Good tool this, very good and with the help of an expert I know who I’m trying to convince to start blogging it’s going quite well. But, there’s always a “but” pulling Oracle data out with SSIS has proved trickier than it shouldn’t have been. Yes I know I’ve cut a few corners to speed the process up but numerical data appears to be coming out as numeric(38,4) and trying to any data conversion to other numerical data using the derived column component is causing it to fail consistently. I’ve managed to get it working but I have to do something pretty evil and convert the data. First to a string in the select statement then back into decimal in the derived column component, the overhead is obvious but I should only have to do this once and when I’m all done I think I’ll reinvestigate all the little irritations I’m finding.
I'm sure i'm just missing something that a little preperation couldn't solve but it's still a somewhat odd.
Good tool this, very good and with the help of an expert I know who I’m trying to convince to start blogging it’s going quite well. But, there’s always a “but” pulling Oracle data out with SSIS has proved trickier than it shouldn’t have been. Yes I know I’ve cut a few corners to speed the process up but numerical data appears to be coming out as numeric(38,4) and trying to any data conversion to other numerical data using the derived column component is causing it to fail consistently. I’ve managed to get it working but I have to do something pretty evil and convert the data. First to a string in the select statement then back into decimal in the derived column component, the overhead is obvious but I should only have to do this once and when I’m all done I think I’ll reinvestigate all the little irritations I’m finding.
I'm sure i'm just missing something that a little preperation couldn't solve but it's still a somewhat odd.
Wednesday, June 15, 2005
Monday, June 06, 2005
Service Pack 4 Weirdness
Here’s something I’ve been meaning to put on here for a while around a bug that appears after the installation of service pack 4 for Analysis Services.
The server is sitting on version 8.0.2026.0 msolap80.dll (This I believe is the beta for service pack 4 but the behaviours here have been recreated in the release version of service pack 4 which I’m told is 8.0.2029). Two clients one on service pack 3a (8.0.760) and the other on service pack 4 release.
Create a calculated member in the Warehouse cube on the Foodmart 2000 database called warehouse count using…
Distinctcount([Warehouse].[Warehouse Name].members)
No problems, you will be able to get the correct values out on either version of the clients msolap80.dll. Import the calculated member or recreate it on the virtual cube built from the Warehouse cube that has just the dimensions and measures copied in from the physical.
Look at the calculated member in the virtual cube with a client on 8.0.2026 and you get nothing returned, look at it with a service pack 3a client you will get the correct value. Now this may not seem like much of a problem when you haven’t or have no intention of deploying service pack 4 PTSlite to clients but if you have Analysis and Reporting Services pulling data out of the cubes on the same machine then you will be using Service Pack 4 PTS.
This has caused me a lot of problems and even to a certain extent caused me to have to redesign some of the cubes. We could role back the service pack but in this situation the querying and processing stability issues resolved were too important to overlook. Suffice to say we are now building VMWare images to allow us to test this kind of deployment in future without actually deploying.
If anyone else gets a different result or has any similar experiences then I would be really interested to hear about them.
The server is sitting on version 8.0.2026.0 msolap80.dll (This I believe is the beta for service pack 4 but the behaviours here have been recreated in the release version of service pack 4 which I’m told is 8.0.2029). Two clients one on service pack 3a (8.0.760) and the other on service pack 4 release.
Create a calculated member in the Warehouse cube on the Foodmart 2000 database called warehouse count using…
Distinctcount([Warehouse].[Warehouse Name].members)
No problems, you will be able to get the correct values out on either version of the clients msolap80.dll. Import the calculated member or recreate it on the virtual cube built from the Warehouse cube that has just the dimensions and measures copied in from the physical.
Look at the calculated member in the virtual cube with a client on 8.0.2026 and you get nothing returned, look at it with a service pack 3a client you will get the correct value. Now this may not seem like much of a problem when you haven’t or have no intention of deploying service pack 4 PTSlite to clients but if you have Analysis and Reporting Services pulling data out of the cubes on the same machine then you will be using Service Pack 4 PTS.
This has caused me a lot of problems and even to a certain extent caused me to have to redesign some of the cubes. We could role back the service pack but in this situation the querying and processing stability issues resolved were too important to overlook. Suffice to say we are now building VMWare images to allow us to test this kind of deployment in future without actually deploying.
If anyone else gets a different result or has any similar experiences then I would be really interested to hear about them.
Wednesday, June 01, 2005
New Cube Design Features - Part 2 - More on Hierarchies
Part 1 further down the page was cut short because I could see that it was going to be one of those 4 page word document blog postings that I would never go back and read let alone someone with any interest in this thing taking any notice of. Anyway, the dimension / hierarchies change thing is pretty huge in 2005 so continuing my piece on Chris Webb’s piece a bit more about attribute hierarchies.
The last thing I mentioned was around relationships between attributes in hierarchies being cemented with member properties. 2005 also understands transitive relationships, again plagiarising Chris’ presentation and example; If there is a one to many relationship between City and Store and City is a member property of Store, Analysis Services automatically infers the relationship of State and Store, Clever huh?
Member properties can express two types of relationship, rigid and flexible. Rigid relationships will retain aggregations when the dimension is updated and is used for relationships between members that do not change over time. Flexible relationships can change over time such as in a slowly changing dimension. The point to remember and what makes this relationship type a little more critical is that if you mark a dimension as rigid and there are changes then Analysis Services will error during processing similar to when a member changes parent in a non changing Analysis Services 2000 dimension.
The structure of hierarchies has changed quite a bit also. Traditionally I’ve worked on projects that have made heavy use of alternate hierarchies. There’s never been anything particular alternate about them apart from the naming, nothing that distinguishes them from any other dimension with some applications allowing you to slice by more than a single alternate hierarchy at one time. Analysis Services is capable of opening up all attributes as hierarchies that would be usable from a client. This may sound great but obviously becomes impractical when attributes such as telephone number or house number are included.
Attributes have a setting called the AttributeHierarchyEnabled property. Setting this to true means that the attribute will be available as a hierarchy on the dimension with an ‘All’ level and its members under that. Setting it to False makes the attribute behave in same manner as a traditional member property. There are many other important settings for attributes, for example if you want to add an attribute as a level to multilevel hierarchies, but you do not want users to see the attribute hierarchy in client applications, set AttributeHierarchyEnabled to True and set AttributeHierarchyVisible to False. These settings prevent users from browsing an attribute hierarchy without disabling it. The AttributeHierarchyVisible setting is ignored if AttributeHierarchyEnabled is set to False.
An attribute hierarchy will have an ‘All’ level if the IsAggregatable property is set to True and can also be added to any level of a hierarchy. If it’s set to False then it can only be used at the root of a hierarchy. Settings on the AttributeHierarchyOptimizedState property determine whether additional indexes are built on the attribute to improve query performance.
Many of the concepts within Analysis Services 2005 user and attribute hierarchies are going to be relatively easy to grasp for anyone involved with implementations on previous versions of the product. Personally I think that it should be easier to understand how to build dimensions that perform and emulate the behaviour of the business for someone starting out in Microsoft OLAP technologies. It’s been too easy in the past to build a cube and just ignore the advanced settings then let it run like a dog as it’s still faster than trying to write SQL that traverses multiple levels in a hierarchy. You can still build poor structures but 2005 seems to be a lot clearer in reasons why and the consequences if you don’t understand the additional properties of a dimension.
The last thing I mentioned was around relationships between attributes in hierarchies being cemented with member properties. 2005 also understands transitive relationships, again plagiarising Chris’ presentation and example; If there is a one to many relationship between City and Store and City is a member property of Store, Analysis Services automatically infers the relationship of State and Store, Clever huh?
Member properties can express two types of relationship, rigid and flexible. Rigid relationships will retain aggregations when the dimension is updated and is used for relationships between members that do not change over time. Flexible relationships can change over time such as in a slowly changing dimension. The point to remember and what makes this relationship type a little more critical is that if you mark a dimension as rigid and there are changes then Analysis Services will error during processing similar to when a member changes parent in a non changing Analysis Services 2000 dimension.
The structure of hierarchies has changed quite a bit also. Traditionally I’ve worked on projects that have made heavy use of alternate hierarchies. There’s never been anything particular alternate about them apart from the naming, nothing that distinguishes them from any other dimension with some applications allowing you to slice by more than a single alternate hierarchy at one time. Analysis Services is capable of opening up all attributes as hierarchies that would be usable from a client. This may sound great but obviously becomes impractical when attributes such as telephone number or house number are included.
Attributes have a setting called the AttributeHierarchyEnabled property. Setting this to true means that the attribute will be available as a hierarchy on the dimension with an ‘All’ level and its members under that. Setting it to False makes the attribute behave in same manner as a traditional member property. There are many other important settings for attributes, for example if you want to add an attribute as a level to multilevel hierarchies, but you do not want users to see the attribute hierarchy in client applications, set AttributeHierarchyEnabled to True and set AttributeHierarchyVisible to False. These settings prevent users from browsing an attribute hierarchy without disabling it. The AttributeHierarchyVisible setting is ignored if AttributeHierarchyEnabled is set to False.
An attribute hierarchy will have an ‘All’ level if the IsAggregatable property is set to True and can also be added to any level of a hierarchy. If it’s set to False then it can only be used at the root of a hierarchy. Settings on the AttributeHierarchyOptimizedState property determine whether additional indexes are built on the attribute to improve query performance.
Many of the concepts within Analysis Services 2005 user and attribute hierarchies are going to be relatively easy to grasp for anyone involved with implementations on previous versions of the product. Personally I think that it should be easier to understand how to build dimensions that perform and emulate the behaviour of the business for someone starting out in Microsoft OLAP technologies. It’s been too easy in the past to build a cube and just ignore the advanced settings then let it run like a dog as it’s still faster than trying to write SQL that traverses multiple levels in a hierarchy. You can still build poor structures but 2005 seems to be a lot clearer in reasons why and the consequences if you don’t understand the additional properties of a dimension.
Thursday, May 26, 2005
Ouch...!
Liverpool (whom I am not a regular fan of) won the Champions League final in what I must admit was pretty spectacular circumstances last night. I watched it in a pub and to say I feel pretty spectacular this morning would be wildly off target.
Now where are those asprin?
Now where are those asprin?
Subscribe to:
Posts (Atom)
Posts
