 | | |
| | |
|
Spaces home  Andrew Wiles Olap Wareho...ProfileFriendsBlogMore |  Tools   |
|
September 14 SQLBits III ReportSQLBits 2008 is over. It is the first time that I have attended this event and I have to say that I was very impressed with the quality of the speakers and the content. I was particularly pleased that there was a strong, well attended BI track. A huge amount of kudos and credit should go to the organisers who cooked up this top notch event in their spare time and for no financial gain. If you have not been to a SQLBits I can definitely recommend it as a superb resource for understanding what is going on in the world of SQL Server.
I was privileged to provide one of the seminars at this year’s event. My slot was scheduled for early afternoon and I have to say that once I had attended the first 2 sessions of the day I was more than a little concerned about matching the standard of the other speakers. The session seemed to go well and I enjoyed the interaction with what was clearly a very knowledgeable audience. I would like to thanks those who got engaged for your questions and comments.
For those reading this blog who were not there the presentation was entitled “Does your cube support your users reporting needs” and was based on the warehouse toolkit work which caused me to kick off this blog in the first place. Preparing the presentation has also given me the impetus to update the databases for the first time in quite a while. If you follow the File Downloads links on this page you will see a new SQL 2008 relational and Olap database.
Over the next few days I will try to get my PowerPoint deck uploaded and as the whole of SQLBits was filmed by Microsoft I will also see if I can get hold of a copy of the video so that those who didn’t attend can see what I was talking about. This of course subject to the provision that it is not too embarrassing! August 05 SQLBitsI will be attending SQLBits on September 13th this year and have had a paper accepted!
I will be presenting in the BI track on the subject "Does your cube support your users reporting needs?". The agenda for the event looks great and I am looking forward to meeting a few folk that I have corresponded with in the past.
For more details check http://www.sqlbits.com/default.aspx July 07 A bit more on UI designI have just watched a presentation by Jensen Harris of the Microsoft Office team on the development of the "ribbon" menu system. Every web designer should be forced to watch this presentation at least once a day until they have got it "The Story of the Ribbon" July 06 Has the web destroyed UI design?Earlier this week I downloaded and installed the latest Silverlight and Expression tools to start some research for a new project. My intention is to determine whether it is possible to create a rich web client version of the Intelligencia MDX query builder that can be distributed via the web. I suspect that this will be a reasonably long process given that this is entirely new technology to me.
My observation in this blog entry however is not about my project but on the user interface for Expression and how this ties in with the influence the web has had on user interface design.
I understand Microsoft's decision to separate design from implementation. In larger development shops this is a logical and useful step toward building applications with a better user experience. For smaller organisations the designer and developer are likely to be the same person - i.e. me. Now maybe I am a bit old fashioned (and maybe my eyesight ain't what it used to be) but based on my very brief first introduction to Expression I really don't like the user interface. Gray on Black may look "modern" but it provides poor contrast for actually seeing what you are doing. It also seems that Microsoft have abandoned many long standing "fat client" UI givens, for example the right mouse click to obtain context menu's. In fact, even though I have a good idea of what I want to achieve I have had to refer to the documentation in order to carry out some very simple and basic "getting started" design tasks such as applying a background color to an object. Clearly the designer for the Expression tool comes from a very different background to me.
A long time ago - remember when Windows 98 came out - Microsoft published a guide to user interface design for Windows applications. The guide was highly proscriptive about how a UI should look and work right down to the greyness of the window and the exact fonts which should be used for labels and captions. The thrust of this guide was to encourage developers to build applications with a consistent look and feel. The reason for this was to make life easy for users. Application menus were proscibed to contain a File, Edit, View, Tools and Options menu (unless the application simply did not fit these terms). This meant that irrespective of which application you were using you stood a reasonable chance of finding the functionality you wanted by looking for it in the same place as the last app you used. By-and Large this worked where software companies bothered to read and apply the guide.
As the web has blossomed the influence of "design" has become a bigger and bigger factor in product development. The design of your web page says a lot about the character of your organisation and the role of graphic designers has taken on a great deal of importance in the projection of corporate identity. Recently however I have noticed an unwelcome (at least to me) shift in the software industry. We seem to have moved to a point where design has become more important than functionality. As part of my Sliverlight research I visited as many sites as I could find which use the technology and I have to say that I was somewhat stunned by how bad they were. Not, I have to say that the technology itself is bad because it most certainly is not (and I have to say that sites built on flash technology are equally bad), but the designers seem to have become so overwhelmed with the ability to spin, fade and animate that they have lost sight of the whole issue that a user has to be able to understand how to navigate the site. Here are a few of my gripes:
The bigger problem is that this approach to design led applications s now bleeding through into fat client apps. More and more I am opening tools to be presented with something that looks very nice but in reality is worse to use than the equivalent product from around 1980.
Am I just sad and old or has the software industry lost the plot when it comes to usability? May 16 Multi-Dimensional SpreadsheetIn my last blog entry I described the work we have been carrying out on data entry in Intelligencia.
In parallel with this activity we have also added that ability to update the MDX script associated with a cube allowing users to add/edit cube calculated membes and cell calculations. This functionality relies on AMO to read the cube script and then allows the user to interact with the calculations via our spreadsheet calculation interface. At present this functionality is only appropriate for users with administrative rights due to the way in which SSAS handles security.
The next stage in the development process will enable interaction with write enabled dimensions allowing users to add/remove data bearing dimension members.
Once this work is complete we should be quite close to providing a modeling interface that is not dissimilar to a multi-dimensional spreadsheet. It would not be a far stretch to allow users to create new cubes and add dimensions/hierarchies allowing ad-hoc building of small OLAP models. An interesting challenge would be to allow business end-users to add to a corporate planning/budgeting application so that they can model the specific behaviour of their part of the business.
April 11 Data EntryOver the last couple of months I have been working on some exciting new functionality for Intelligencia. This is the addition of data entry and modeling capability to the query builder. The ultimate objective of this work is to provide a comprehensive user interface in which an end-user can build an Olap data model and populate it with data. The first phase of this work concentrates on providing management tolls that sit on top of an existing data model.
Data Entry
The data entry component of the work allows interaction with write-enable cubes. The challenge here is to provide an intuitive means of handling allocation of data from aggregate cells. What we have decided on is to only allow data entry where a member is an aggregate in one dimension only. This restriction mens that it is clear on the screen where the data is going to be spread and also limits th possibility of creating allocations to huge numbers of cells. In the screen shot below the cells highlighted in orange can be used for allocation and thise which are gray cannot (and are also protected).
The next challenge was to come up with allocation rules that behave well in different circumstances. The first thing we encountered is that for parent-child dimensions data will be allocated to the data member for an aggregate node even if it is not displayed. The result of this is that the user enters a number into an aggregate node and finds the sum of the children when allocated does not appear to equal the total. We have therefore had to create allocation rules which prevents data from being allocated into data members. We have also made a distinction between allocation when the aggregate member has no data (when initializing the model) and allocating to a pre-populated model. In the first case we create an equal allocation in which all child nodes (except the data members) receive the same data value. Where values already exists we carry out a weighted allocation where each node receives a value in proportion to its contribution in the original total. The result of this work is intuitive and does not require any unerstanding of MDX or SSAS by the end user.
To top up the default allocations we have created a user interface for spreading data into ranges of cells based on linear increases/decreases in value and seasonal profiles. The following screen shot shows the seasonal profile window in which a user can define a source profile (this can be copied from the grid) and specifies a target value. A new profile is created that can be pasted back into the grid.
Next step in to allow the user to update the Mdx script for a cube thereby creating saved calculations in the model..... February 01 Cakes, Layers and SlicesThe following diagram shows the way in which I like to organise data when building a financial warehouse. I refer to this model as "Cakes, Layers and Slices" which provides a simple way to explain the component tableas and views to a client in a form that they can grasp.
The fact table is the "cake" containing all the data required to build a cube. The cake is split into "layers" representing each of the "reporting cycles" in the model. Actual data is a special case of a reporting cycle in which the data accumulates over time. Each layer of the cake (especially actual data) can also be split into "slices" representing a span of time. The layers and slices may be implemented as physical tables or views with the fact table being a union of all the layers. In an ideal world in which the data volume is not too large the entire model would be built as views from the imported warehouse data. As the data volumes grow the individual elements in the model can be built as tables with appropriate ETL to move data from one part of the model to another. The schema associated with each element (cake, layer and slice) is kept identical so that all transformations are carried out in the ETL or views that contribute. The result is easy to manage and elements can be swapped in and out as the content grows and the reporting cycles progress through the year.
November 27 SAP Business WarehouseIt has been too long since I last updated my blog, not because of a lack of desire, nor even a lack of anything to say - I have just been too busy!
A large part of that busyness has been associated with upgrading the iT-Workplace Mdx query capability to support SAP Business Warehouse. I have to admit that when I first started this project I was sceptical about BW as an Olap data source. I had heard quite a bit of negative response to the performance and architecture of BW therefore started the work with a fairly low expectation. Having worked with it for a while both in test and production environments I am now more positive.
SAP have certainly taken an unusual approach to implementing Olap for people who come from a Microsoft background. The SAP tools allow you to build queries that effectively define a sub-cube for analysis. This allows the performance of the implementation to be managed by limiting the amount of data a user will navigate at a given point in time. The downside is in the flexibility to investigate data which exists in the cube but is outside of the query definition. Given this restriction the performance of the resulting "views" is quite adequate for typical Olap navigation without being stellar.
The queries bring on their own complications when using Mdx due to some SAP specific extensions that allow you to define the "variables" that limit the scope of the query. SAP's core Mdx implementation is also very basic when compared to Analysis Services but then this is also true of TM1.
From the exposure I have had so far I would say the biggest problem with SAP BW is actually in the warehouse administrators unerstanding of what makes a usable cube/query. Most of the data I have worked could have been improved greatly if some usability testing had been done with end users. This is not of course a problem that is unique to SAP, I have seen some apalling Analysis Services implementations in my time, but because the SAP world is quite closed there does seem to be a lag in design skill for BW implementations.
I would love to implement the warehouse experiment into BW some time to see how easy it would be to create a rich Olap experience for end users. Somehow I suspect that I won;t have time to do this in the near future........ September 08 Insider view on Xmla CouncilI had a telephone conversation with Amyn Rajan of Simba technologies this week. Amyn, and Simba, was the Xmla council for a large part of its existence and he was kind enough to share some insight on what happened to the organisation. His thoughts are expressed on his blog at http://blogs.simba.com/
I am not as optimistic about the state of Xmla/Mdx as Amyn. There are some fairly rudimetary differences in the way that different vendors have implemented the basic Xmla specification (for example the SAP implementation uses different restrictions to Microsoft's) and these make working with the base standard difficult. Surely it should be possible to take any Xmla "consumer" that supports the 1.1 specification and connect it to any "provider".
Further to this Amyn's notes highlight the bigger problem for interoperability which is that the provider is currently unable to enumerate its support of Mdx. A consumer should be able to query whether a provider support a particular function, or option (e.g. does the provider support the POST option in the Hierarchize function or will the provider allow 2 hierarchies from the same dimension to be displayed on the same axis). Without these interfaces provider intelligence has to be embedded into the consumer software pretty much guaranteeing maintenence problems as the providers evolve.
Without an active standards body it is likely that Xmla and Mdx will drift further and further back into the world of proprietary software. August 01 Eric Nelson BlogI just noticed (a bit late it has to be admitted) that Eric Nelson has given iT-Workplace a nice mention on his blog (http://blogs.msdn.com/ericnel/archive/2007/07/23/uk-isv-delivers-mdx-query-builder-control.aspx) - Thanks Eric! July 27 More on XmlaYesterday I spent the day at Microsoft in Reading working with one of their SAP specialists. The upshot is that we have actually been able to connect to and retrieve data from SAP BW 3.5!
Apart from a couple of security problems the main stumbling block was the SAP implementation of "GetMembers" via the member schema rowset calls. The SAP documentation for this is quite strangely unhelpful and it appears to be the only schema rowset that they have implemented differently to Microsoft Analysis Services.
Since my last piece on Xmla Chris Webb has also blogged on the subject http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!1294.entry. I'm not sure that I agree with his conclusion that Microsoft should be the company to re-form the Xmla council. My experience of working with Xmla indicates that they were actually the only founder member of the council who properly implemented the specification and took the process seriously. I would like to see the other founder members, possibly joined by Pentaho (Mondrian) stepping up to the mark.
I do think that Microsoft own the current benchmark for interoperability in the form of their ADOMD.Net provider. A standard built around "does it work with ADOMD.Net?" would be widely acceptable from a customer perspective and would allow a significant number of mix and match application possibilities. July 02 Xmla is dead?I have for the last couple of weeks, on and off, been attempting to connect to SAP BW using the Microsoft ADOMD.Net provider. In theory this should be possible as Microsoft and SAP both claim to be Xmla 1.1 compliant as defined by the Xmla council specifications. My efforts to find technical information regarding the SAP implementation of Xmla have had emails bouncing between The USA and Germany with varying levels of surpise and incredulity that I should attempt such a feat.
This would be quite sad if I had not experienced pretty much the same problem 2 years ago with Hyperion Essbase, an excercise that I eventually gave up on.
Xmla was heralded as the way forward for Olap about 5 years ago. Microsoft, Hyperion, SAP and SAS Institute all put resources into the Xmla council along a number of other members most notably Simba. One of my old colleagues at SAS represented them on the council for a year or so and was full of excitement at the opportunity of getting involved in the delivery of a genuine cross-companystandard.
In truth the council has failed.
Microsoft, SAS, SAP and Hyperion (Essbase) have all implemented an Xmla based solution but, with the possible exception of the Simba O2X driver that can access Microsoft Analysis Services and SAP BW (technology from which was used to deliver Microsoft's SQL Server to SAP capability), I have seen no evidence of cross platform interoperability.
The Xmla council site www.xmla.org has been taken over by Simba, some time after the last working threads on the site died out about 2 years ago. The industry is left with 4 (or maybe 5 if you count Mondrian) independent and incompatible implementations of Xmla.
Is Xmla is dead? Does anyone else care? June 19 Video of BIDS AddinJune 18 Reporting Sevices (BIDS) AddinIt turns out that creating an addin for Visual Studio is now quite easy! There is a nice project template to get you started and if the objective is simply to add stand-alone applications to the menu system the rest is quite straightforward. iT-Workplace now has a shiny new Query add-in available for developers who work with BIDS and Reporting Services. The tool is delivered as part of BIDS rather than being integrated into the Reporting Services UI (I will be looking at whether that is possible next).
I would like to thank Darren Gosbell for his assistance on the install procedure for this project. I would also recommend anyone who has not been there to visit the BIDS Helper project which Darren helps to co-ordinate. This project has integrated a selection of useful SSAS utilities that "Extend and enhance the functionality of the SQL Server BI Development Studio" May 30 Reporting Services Mdx add-inI have a request for help...
I was involved in a discussion the other day with Chriss Webb http://cwebbbi.spaces.live.com/ He suggested that it would be really good to create an addin for Reporting Services that provides better access to Analysis Services data. This is clearly a desireable thing to have BUT I am not quite sure where to start.
I would be very interested to hear from anyone who has written a reporting services add-in with any information on how to get started. I would also like to hear from anyone who has strong feelings about what they would like to see in such an add-in.
If it looks feasible I might well give it a go! May 23 Query Builder Launched!Today we launched the Mdx Query builder project on the unsuspecting world. Officially named "Intelligencia Olap Controls" the component includes a full Office 2007 User Interface plus our unique Spreadheet Calculation Interface that allows the end user to create calculated members and cells without being exposed to any Mdx.
If you would like to try it out it is available for download from the iT-Workplace web site http://www.it-workplace.com
I am very excited about this tool. In addition to the spreadsheet stuff it includes a number of fetures that I have wanted to see in a query component for quite a while. An example of this is the work that we have done on large dimension/query support. For a start we honor the InstanceSelection attribute displaying a different selection window for large dimensions than for smaller ones. We are also able to estimate the size of the cellset that will be returned before the query is run warning the user if the resulting cellset is likely to be excessively large.
My only slight frustration is that at present because we are using the ADOMD.NET provider we only really support Analysis Services. The tool will pop up our old UI for databases using an ODBO provider but we really need to include ODBO support in the new UI. April 27 Olap Query BuilderThe attached screenshot shows the progress that has been made on the Mdx query builder I mentioned. Thanks to some excellent feedback on the original interface I decided it was time to bit the bullet and upgrade to an Office 2007 look and feel complete with Ribbon menu. The menu system comes from DevComponents (http://www.devcomponents.com/dotnetbar/). We have created a new grid control plus a selection of other new controls to present an "Office like" user interface.
The attached screen shot will give you an idea of how the interface looks.
I am still interested in hearing from anyone who wants in a standalone query component and has a bit of time to spare giving our pre-production code a workout. April 12 Windows VistaAbout a month ago I upgraded to Windows Vista. Anyone reading the myriad of blog entries devoted to problems with Vista upgrade might think this is the act of a madman but as a software developer I need to be prepared for the future.
I have to say that my experience has been OK (Intentionally neutral)
Before upgrading I did some research and bought appropriate hardware; something that a lot of the complainants out there seem to have ommitted. My configuraion is now a DELL XPS M1710 with 4Gb RAM running the x64 version of Vista Business.
I initially had a problem with the performance of Windows Explorer which was taking 15-20 seconds to load. It turns out that this was related to a problem with DNS on my network (Explorer tries to obtain information from Active Directory to enumerate the users full name) so I suppose that in some ways this did me a favour. Having fixed my DNS explorer performance is now fine. The general performance of applications is good but this will have more to do with the machine specification than the OS.
There are several applications that I use, most notably Visual Studio and anything that comes from Adobe/Macromedia, that need to be run with administrator rights. This is slightly irritating (i.e. an extra mouse click each time the app is launched) but certainly not a show stopper. As a developer I do wonder why Adobe felt they needed to write so much stuff to protected part of the OS in the first place but I'm sure that when I get around to upgrading these apps they will have sorted out the problems. I have had no problems with printers, wireless networking etc.
On the plus side Vista seems to boot a lot faster than Windows XP ever did on my previous laptop (INSPIRON 8600 with 1Gb RAM). It is particularly noticable that the applications invoked on startup e.g. SQL Server load very quickly. The sleep mode also appears to be stable which was never the case with XP and means that I switch the machine off far less often. I have experienced no instability in the form of unexpected OS exceptions which again I used to have occasionally on XP. The new start menu with its built in search is quite nice once you get used to it and I even use the rolodex window search because it works rather than just because it looks cool.
Like all transitions there were a number of occasions where I cursed Microsoft for changing the interface and moving/renaming features that I use frequently, but having found the new functionality I am now working as productively as I was before the change.
My overall feeling about Vista is very neutral. It does what I expect an operating system to do in much the same way that Windows XP did. It looks a bit nicer if you can afford to switch the Aero interface on but this is not as important as the changes that Microsoft have made to Office 2007. It seems to me that Microsoft have directed Vista firmly at the consumer market where it is bound to do well if only because every new PC will ship with Vista pre-insalled. As a business user I would struggle to come up with a compelling business case for upgrade particularly if I had thousands of PC's specified with <1Gb RAM to manage. In this respect Microsoft have created for themselves a huge challenge if they want to make Vista the operating system of chioce before the end of the decade...
March 14 TM1 V9.1TM1 version 9.1 has been made available to partners within the last week as a beta so I been having a play. There are changes to integrate the recently aquired Temtec Executive Viewer and to improve the configuration of the TM1Web interface. Of more interest to me however are changes that improve the granularity of locking for data and metadata update and the new rules editor.
Data entry is IMHO TM1's real USP. The effectiveness of this capability has previously been somewhat hampered by the need to lock large sections of the database during most forms of update. The decision to improve the granularity of locking should make a big difference for applications in which multi-user collaboration is required.
The new rules provides colorisation, outlining and improved tools for working with the TM1 function library. The old rules editor was a real weakness in TM1 being little better than notepad. The editor still lacks features such as intellisense which one would expect from a state of the art development tool and I would like to see some better tools for syntax checking and highlighting but overall it is a big step in the right direction. |
|
|
