The Shins - Chutes Too Narrow
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.
Subscribe to:
Posts (Atom)
Posts
