WhereScape RED Builds data warehouses, fast.

WhereScape RED is not a cube development tool - whilst many people use WhereScape RED to build cubes over a star schema it does not replace Visual Studio BI Development Studio (BIDS), because it does not provide the same level of flexibility for cube development as a cubing tool such as BIDS.

WhereScape RED will very quickly build a SQL Server Analysis Services (SSAS) OLAP cube for you over a data warehouse – just drag the fact table over and ta-da you have a cube defined, a couple more clicks and it is deployed and processed. This is great for developers during the prototype phase of a star or subject area. It is of course also great for those that want to deploy cubes into production.

Often we get asked why WhereScape RED doesn’t support this feature or that in SSAS cubes. And to answer that we need to look at what we built WhereScape RED for, and how that is different from why Microsoft built BIDS - Microsoft’s cube development environment.

Straight off the bat – WhereScape RED will never offer the flexibility and functionality that BIDS offers for cube development. Why re-invent BIDS? The criteria we use to assess what features to include – will the feature help WhereScape RED users to build cubes quickly and efficiently and manage them easily as part of a data warehouse? Using these criteria we cater for 90%+ of the scenarios our customers’ meet – while always giving them the opportunity to use the full Microsoft development environment for more esoteric features.

Philosophically WhereScape RED builds cubes to provide a meta data layer and an aggregate layer over the top of a data warehouse. There are some features of SSAS that WhereScape therefore does not support – Analysis Services needs to support scenarios where there is no data warehouse, whereas we can always assume that a warehouse exists.

There are four basic options for using cubes with a data warehouse:

1. Use cubes as an aggregate layer over an extensive data warehouse – this provides a good mix of the abilities of cubes and of a relational data warehouse. WhereScape RED will build and manage cube as part of the data warehouse.
2. Use cubes extensively over a simple data warehouse. WhereScape RED can build base cubes which are then extended in BIDS. This allows WhereScape RED to integrate cube processing within the data warehouse workflow but allows the developer to use the flexibility of BIDS for cube development.
3. Use cubes within a prototype to workshop the data warehouse but actually do not use them in production. Instead use WhereScape RED to build an aggregate layer in the data warehouse to simplify support and development.
4. Use cubes directly over source systems with no data warehouse.

WhereScape RED provides immense value in all but one of these scenarios; scenario four, where there is no data warehouse, will not benefit from using WhereScape RED. WhereScape views the cubes as complementary to the data warehouse – great for exploring summarized data, but detailed reporting is more efficient in the data warehouse.

The ability to break complex processing into smaller steps for development and for troubleshooting has provided tremendous value for many organisations that have used WhereScape RED to build and manage an enterprise data warehouse. Cubes provide the capability for building extensive business rules and calculations using MDX within cubes – however the skills, complexity and support overhead becomes significant very quickly.

WhereScape RED supports building native cubes on SSAS 2000, SSAS 2005, and SSAS 2008 Analysis Services. These cubes are plain, vanilla cubes that can be opened with BIDS if they need to be edited or extended. WhereScape RED supports:

• Multiple hierarchies per dimension
• Attribute hierarchies (member properties in SSAS 2000)
• Attribute relationships (SSAS 2005+)
• Calculated members
• KPIs (SSAS 2005+)
• Linked cubes (SSAS 2005+ - Virtual cubes in SSAS 2000)
• Cube partitioning
• Processing options
• Drill-through

WhereScape RED does not (or not yet) support:

• Multi-fact cubes (this is coming, see below)
• Perspectives (this is coming)
• Translations (no plans, yet)
• Aggregate design (can be added using BIDS, or through usage optimization).
• Security (this is coming – can be added using BIDS or a script)

Value add – what WhereScape RED also brings:

• WhereScape RED allows you to manage cubes as part of the data warehouse – not some additional technology that is managed separately. If you make a change to the data warehouse you can manage that change and its effects on the cube just using WhereScape RED.
• Integrated documentation – WhereScape RED auto-generated documentation includes cubes.
• Integrated migration – you can migrate cube meta data with the rest of the data warehouse.
• Integrated workflow – you can process the cube within the same jobs that process the data warehouse.
• Integrated versioning – if you want to reverse out a change or deleted something that was useful then WhereScape RED versioning can save the day.

One topic we get asked about is how WhereScape RED combines different facts / stars into a single cube. In a mixed data warehouse / cube environment there are two base options – integrate this data in the data warehouse and build a cube over the top or combine the data in the cube.

Currently WhereScape RED uses linked cubes to join together separate physical cubes, as opposed to building a single physical multi-fact (multi-measure group) cube. Linked cubes provide equivalent analytical functionality by displaying a single cube containing a superset of measures and dimensions. Multi-fact cubes are coming to WhereScape RED as an alternative option so that the developer has a choice. WhereScape RED will continue to offer linked cubes to combine multiple subject areas in the OLAP layer as they can provide performance benefits over multi-fact cubes. This is relevant when there are many fact tables or the fact tables are large.

Another question we get is how we support cubes building from a Teradata normalized data warehouse. This can be accomplished by utilizing views to provide a dimensional interface.

In summary, WhereScape RED makes managing cubes as part of a data warehouse easy. Where a cube only infrastructure is sufficient WhereScape RED will not provide all the bells and whistles that BIDS provides. If a data warehouse provides value to an organization then WhereScape RED will offer significant value to that organization over the entire lifecycle of that data warehouse.