PowerPivot, SQL R2, Sharepoint 2010, Office 2010.

So we’ve been using PowerPivot for a while now, and Office 2010 has been part of our lives for some time. I’ll use this blog to answer some of the questions that keep cropping up in conversation with our customers:

1. Does XLCubed work with Excel (Office 14) 2010?

a. Yes, we’ve been using it since the first CTP release and each release since then.

2. Can I use XLCubed Web with SharePoint 2010?

a. Yes, publishing to the web and embedding the reports within your SharePoint site works in exactly the same way as with previous versions.

3. Does XLCubed connect to PowerPivot?

a. Yes, XLCubed connects to the PowerPivot published cubes, and our client tools can be used to build reports and dashboards from them.

4. Can I build reports from SQL Server R2 using XLCubed?

a. Yes this will work just fine, just as you can build reports from previous version of SQL or other relational sources. (here is an example)

PowerPivot in the real world

The services team have been working on migrating some of our internal models and sample databases across to a PowerPivot environment – looking at the pros and cons, using DAX rather than MDX to perform some calculations. Results have been varied, its been interesting to see some features that we’ve had for a while (like cube formulas, slicers and web parameters) appear in a similar way in PowerPivot.

Quite clearly PowerPivot isn’t the be all and end all or anything like a replacement for Analysis Services, but it certainly has a role for tactical solutions, some power user analysis, and we think likely also for RAD prototypes of larger scale AS implementations. It doesn’t venture into the gap left by PerformancePoint Planning (as many thought it would in early 2009) – we’ve moved to address this area with the XLCubed PM suite that uses in memory OLAP cubes and/or Analysis Services.

Trying out some of the tools

Here’s a few download sets for you to try, take careful note of the hardware spec and requirements for the MS ones though:

The 2010 Information Worker Virtual machine

Register and Download Office 2010

PowerPivot 32Bit, 64Bit

XLCubed Evaluation

If you would like to evaluate against your own data – contact the XLCubed Product team for evaluation editions or if you want to try a no risk proof of concept or prototype contact the XLCubed consulting team.

Cube Design – meeting the business needs

Following on from our previous blog post on a couple of the common cube performance issues we’ve seen this last month, I thought I’d mention some of the non-technical issues we see quite often. In one case, once we’d made a few teaks and sorted out the cube performance issues we had to ask – Is the cube doing what it needs to? (Of course we did ask this first but the priority was sorting out the current cube performance!) Does it meet the business requirement? There’s no point in having the most complex cube that uses all the greatest features if it can’t answer the users queries.

In reports, we’ve seen examples where clients have nested four or five attributes to build up the effects of a hierarchy or run huge queries then vlookups on them to get the data they need, or bring back 12 columns of data and manually work out year to date, or not have any hierarchies that reflected commonly used groupings of members, or not have member names formatted in the way the business needs. To us this just isn’t right.

The users might not seem to care too much if they don’t know how the cube could work or if it runs fast enough to bring back huge result sets they can manipulate themselves – but doesn’t that negate the point of having a cube and your investment in it? Consumers of the cube should have fast, timely, accurate and importantly appropriate data made available to them in a manner that makes sense.

Cube design and build is about understanding the business and users needs and then building the cube and associated processes, that’s before even starting to build the reports and conveying the information using good data visualisation practices.

All too often we’re seeing a drive to use the latest tech, the flashiest widgets, cool looking 3D and shading effects on reports through to cubes and databases with every conceivable hierarchy or type of measure thought possible but not bearing much resemblance to what the users need to see.

I won’t hide the fact that we’re very proud of our skills and experience in ensuring our clients get not just a technically excellent system but also one that fits their needs. If you want to talk to one of the team about how they can help, you can find our contact details here.

Common Analysis Services Performance Issues

A quick blog post from the Services team here at XLCubed on some performance problems with SSAS that we’ve seen again recently. With the processing power and memory available it’s pretty easy to build a fast cube – both for query performance and processing time. It is also easy to be lax in cube design, ignore the warnings and best practice guidelines, and end up with a cube that’s looks concise, is neat and clever but performs terribly for end users.

We’ve come across a couple of examples of this at client sites in the last month, and there are some common issues that always seem to jump out – rectifying these normally has a very positive impact. The three most common culprits we see are:

Parent-Child dimensions – Parent-Child dimensions are nice and easy to build and use. However, as you can’t build aggregations that include a parent-child dimension it can make for a badly performing cube! Try to flatten dimensions out and evaluate exactly why a parent-child dimension is required and being used. They are not the only option..

Unary operators, Custom-roll ups – we’ve seen cases where these have been included in every dimension in a cube by default. If there isn’t a need for them – leave them out! If you can get around using a custom rollup or unary operator by some simple work in the ETL process it may be better to do that first.

If your query performance is bad – try removing all unary operators and custom rollups then re-test the cube. How’s the performance now? It should be significantly faster – evaluate and review the need for the unary operators and custom rollups and see if the same effect can be achieved differently (e.g. in the ETL layer)

Cache vs. Non-Cache Data – Basically is the cube recalculating and re-querying numbers over and over again or can it re-use results? Use profiler to check for cache or non-cache data when your queries are running. So many times we’ve seen all queries not using the cache because AS hasn’t been given enough available memory or volatile operators such as now() have been used in mdx calcs.

Resolving the issues above had a massive impact – reports taking up to 3 minutes to run were down to a few seconds, users could begin to use the application properly for the first time, however fixing the performance may be only part of the task. The cube of course needs to have been designed to meet the business requirements, but that’s another blog..

Microsoft Business Intelligence roundup

There have been a few key announcements in the Microsoft BI world recently, we’ve gathered them up and summarised below in case our readership have missed any of the key announcements.

SQL Server 2008 R2 – (CTP Summer 09)

“SQL Server 2008 R2 expands on the value delivered in SQL Server 2008 by providing a wealth of new features and capabilities that can benefit your entire organization. This release will further improve IT Efficiency with new and enhanced management capabilities and empower business users to access, integrate, analyze and share information using business intelligence tools they already know.” Read more here.

So what does this mean for you? In R2 there will be a number of new features from Gemini to Master Data Services, support for more than 64 processors to extended functionality in the Management Studio. We’re all looking forward to Gemini and the potential that has to offer – rest assured the XLCubed development team are working closely to ensure that the product is compatible straight out of the box. If you have any questions contact support@xlcubed.com

Service Pack 1 for SQL Server 2008 Available  (April 09)

Microsoft announced the release of SP1 for SQL Server 2008 earlier this month, for many this marks the psychological point at which they’ll take interest in and investigate the product in depth. With a large uptake of the product already in the market place and the fastest OLAP engine we’ve seen from Microsoft, there is now no excuse not to evaluate upgrading or migrating to SQL Server.

Contact our services team for more information or how we can help you with SQL Server 2008.

SQL Server 2008 SP1

Service Pack 1 for SQL Server 2008 is now available for customers. The Service pack is available via download here and is primarily a roll-up of cumulative updates 1 to 3, quick fix engineering updates and minor fixes made in response to requests reported through the SQL Server community. While there are no new features in this service pack, customers running SQL Server 2008 should download and install SP1 to take advantage of the fixes which increase supportability and stability of SQL Server 2008.

Customers have no reason to wait to upgrade to SQL Server 2008 and many are already taking advantage of SQL Server 2008 as a smart IT investment. In fact, there have been over 3 million downloads of SQL Server 2008 since the RTM in August. With this Service pack, Microsoft is introducing 80% fewer changes to customer configurations compared to previous SQL Server Service Pack releases. This remarkable decrease is a testament to a revised product development process and updated servicing strategy that is focused on ease of deployment while keeping customer environments stable.

Microsoft BI Conference moves bi-annual

The MS BI conference last held in October 2008 in Seattle, WA has now been changed to an bi-annual event, citing  global economic constraints to travel budgets worldwide, Microsoft are moving the BI conference to a bi-annual event, with the next conference scheduled in Seattle on October, 2010. The next BI Conference scheduled for October 2009 will be moved to October 2010 in Seattle, WA, and all further BI Conferences will be held every second year on an ongoing basis. Content till then will be covered at the SQL Pass Summit, TDWI and SharePoint conferences.

If you were looking forward to seeing the XLCubed product team at the BI Conference this year, don’t worry you can still contact them at xlsales@xlcubed.com

SQL Server Fast Track Data Warehouse (Feb 09)

Microsoft announced SQL Server® Fast Track Data Warehouse, a new set of Reference Architectures for SQL Server 2008 that enables customers to accelerate their Data Warehouse deployments and reduce cost.  In addition, customers can further jump start their Data Warehouse design with new industry solution templates provided by System Integrators – Avanade, Hitachi Consulting, Cognizant and HP.

Seven new Reference Architectures with storage capacities from 4 to 32 TB were unveiled in partnership with HP, Dell and Bull.  Developed and tested by Microsoft, these architectures use balanced hardware optimized for Data Warehousing.  As a result customers will get

  • Better price performance than competitive solutions.  Fast Track Data Warehouse offers similar performance to the competition at 1/5th the price
  • Faster time to value and lower cost to setup and configure
  • Better performance out of box through pre-tested hardware. 

clip_image001

Customers can also choose the right Fast Track Data Warehouse with the right performance, storage capacity and pricing to suit their business needs.   Unlike Appliance Vendors with proprietary solutions, the new reference configurations use industry standard hardware from Dell, HP and Bull giving flexibility and cost savings to customers.

Fast Track Data Warehouse is available from today: customers will buy their SQL Server 2008 licenses through their preferred Microsoft Partner and the hardware from Dell, HP or Bull. If you’re looking to implement a data warehouse, contact the services team to see we can help.

Demise of Performance Point Planning (Jan 09)

It’s been a few months now since the announcement by Microsoft of the demise of Performance Point Planning, and the rebranding of the Monitoring and Analytics elements as PerformancePoint Services. This was an announcement back in January (09) that caught many by surprise, however for us its provided a useful segue into the new XLCubed Planning application. Many customers were waiting to see what was coming next, when PerformancePoint would be ready to compete with the likes of existing players with proven planning technology (i.e. in memory OLAP) and the  tempting announcements around Gemini certainly added confusion. Now looking back at the conversations we had in Seattle and Microsoft presentations perhaps the announcement isn’t as big a surprise as it felt at the time.

As above our long term commitment to an Excel front ended planning application continues, the demise of PerformancePoint Planning has simply increased the market for us and in many ways freed clients from the constraints of using purely Microsoft technology. Augmenting the Microsoft toolkit and providing our clients with the functionality they need to build effective planning, budgeting and forecasting applications remains at the forefront of our product set and services.

If you want to know more about our products and services (consulting team) just send an email to services@xlcubed.com and someone in your region and market sector will get back to you straight away.

Augmenting the MS BI Stack

Here at XLCubed we’re often asked how the product sits in relation to the Microsoft Business Intelligence tools.
The answer is that we add to and augment the features and functionality that Microsoft has to offer. Excel is a fantastically powerful and flexible spreadsheet engine and this is exactly what it should be used for. However all too often, Excel is used as a database. With linked spreadsheets, and huge data extracts.

XLCubed have a number of products designed to take advantage of the functionality available with Microsoft Business Intelligence tools, these include XLCubed Excel edition, MicroCharts, and XLCubed

The basic strucure of a cube

This week we’ll take a look at the basic structure of a cube from an end user perspective, as opposed to the architectural underpinnings. This is intended as a high level overview, and for brevity contains some generalizations, and focuses on Microsoft Analysis Services cubes.

An OLAP cube consists of several key elements, the most fundamental of which are dimensions and measures

1) Dimensions.

Dimensions are the business elements by which the data can be queried. They can be thought of as the ‘by’ part of reporting. For example “I want to see sales by region, by product by time”. In this case region, product and time would be three dimensions within the cube, and sales would be a measure, below. A cube based environment allows the user to easily navigate and choose elements or combinations of elements within the dimensional structure.

2) Measures

Measures are the units of numerical interest, the values being reported on. Typical examples would be unit sales, sales value and cost.

Note that there are modelling techniques which develop cubes with only one pseudo measure, typically called ‘value’ or similar, and implement what the user would think of as the measures through a dimension. There are performance and navigational reasons which can make this a good approach, but not one we’ll cover here in our introduction.

The diagram below shows a very simple cube which we’ll use for discussion.

cube

 

 

 

 

 

 

 

 

 

This particular cube is for an exports business, and consists of 3 dimensions, Source, Route, and Time. The two measures are Packages, being the number of packages shipped, and Last, being the last shipped date.

Very few real world cubes will have just three dimensions, but I’ve yet to learn how to draw a 12 dimensional cube! The diagram above is enough to illustrate the fundamental principle, that at every intersection of the different dimensions, are stored the value for each of the measures. In larger real world cubes the principle is the same, just the numbers of intersections is larger.

The diagram highlights a few additional features of dimensions which need to be understood.

  • Hierarchies

A dimension can contain one or more hierarchies. Hierarchies are really navigation or drill paths through the dimension. They are structured like a family tree, and use some of the same naming conventions (children / parent / descendant). Hierarchies are what brings much of the power to OLAP reporting, because they allow the user to easily select data at different granularity (day / month / year), and to drill down through data to additional levels of detail.

    • Hierarchies consist of different levels. For example a time dimension would typically have a year, a month and a day level. A customer hierarchy may consist of Country, State, City, and Name levels.
    • The levels are either implied in the case of dates, or exist as ‘attributes’ in the source data. So for example customer number 12324, John Brown, would have additional information recorded such as his address, broken into house number & street, city, state, country. Each of these is an attribute.
    • Hierarchies are really ordered navigation paths through the attributes
    • In Analysis Services 2005, the user’s view of a dimension will typically consist of both the defined Hierarchies, and also the Attributes. Attributes are ‘flat’, i.e. contain no ordered drill path.

 

  • Members

A member is any single element within a hierarchy. For example in a standard Time hierarchy, 1st January 2008 would be a member, as would 20th February 2008. However January 2008, or 2008 itself could also be members. The latter two would be aggregations of the days which belong to them. Members can be physical or calculated. Calculated members mean that common business calculations and metrics can be encapsulated into the cube, and are available for easy selection by the user, for example in the simplest case Profit = Sales – Cost

  • Aggregation

Aggregation is a key part of the speed of cube based reporting. The reason why a cube can be very fast when for example selecting data for an entire year, is because it has already calculated the answer. Whereas a typical relational database would potentially sum millions of day level records on the fly to get an annual total, Analysis Services cubes calculate these aggregations during the cube build and hence a well designed cube can return the answer quickly.

Sum is the most common aggregation method, but it’s also possible to use average, max etc. For example, if storing dates as measures it makes no sense to sum them.

The cube introduces a number of dimensions, hierarchies and measures, modeling the business of interest, and all of which are available to the end user to quickly and easily select, drill, and slice and dice. With a well designed cube the user benefits from a reporting environment which is highly flexible, contains the pre-calculated business metrics they regularly use, and is fast in terms of data retrieval.

So, what’s an OLAP Cube, anyway?

As "An Excel User in a Cubed Kingdom" I’m starting my exploration of this new found land with a simple question: what is an OLAP cube? In plain English, please…

I like this simple, non-technical definition: a cube is a set of predefined answers. It’s up to you to select the right questions.

OK, let’s detail this.

Imagine that you have a very large database with the usual business data: orders, customers, sales representatives… Now you want to know how much a customer category ordered over the last year. You query the database and you get the answer. Then you want monthly sales. Query it again. Dig a little deeper to see what products that category ordered. Query it once more.

What is happening behind the curtain? Each time you enter a new query the system looks at each transaction (or a subset) and performs the necessary calculations to answer your query. You’ll get your answers, but it will be painfully slow: depending on your query and the database size, it may take hours. It is not an option.

But you don’t really need to see each individual order, do you? If you only need to know monthly sales, why should your system go through each transaction? If you pre-aggregate that data, you’ll get your answers much, much, faster, because there aren’t five million records, just 100.000. You’ll be able to actually work, instead of staring at your monitor, waiting for an answer.

This is what a cube does. It provides faster answers by eliminating the unnecessary detail for the task at hand. You shouldn’t look at a cube as an unique, condensed version of the database. While you have a virtually infinite number of questions that the database can answer, a cube focus on providing answers to a small set of questions. That’s why you can have different cubes (marketing, fin, sales), all of them getting data from the same source. They all answer different sets of questions.

When designing a cube (it’s your job, not IT’s), resist the temptation of a one-size-fits-all cube. Clearly define a coherent set of questions for your fundamental business needs and make sure they are answered once the cube becomes available.

If you are exploring your data you will not want to wait one hour each time you make a change. On the other end, a fast cube with no data to explore is useless. There is  a fine balance between maximum flexibility and maximum performance.

An OLAP cube not only ensures that you retrieve the right data from the database but also allows you to explore it efficiently. Two good reasons to add OLAP cubes to your toolbox.

Next time we’ll see how plain English can describe the structure of a basic OLAP cube.

Gemini – Smarter Excel Dashboards with End-User BI

I attended beginning this week the Microsoft BI Conference in Seattle where Microsoft presented an interesting new product that might change the way you build your Excel dashboards in the future: “Gemini”, an End-User Business Intelligence plug in for Excel.

End-User Business Intelligence has been around for quite a while with some of the most prominent Applix TM/1 , PALO, PowerOLAP and MIS Alea. These tools where tailored for non-IT proficient Excel users to allow them to build real Business Intelligence solutions with Excel, without leaving Excel or having to learn complicated BI or Data Warehousing techniques. All you have to learn is that a cube is like a very powerful multidimensional spreadsheet that can easily aggregate and hold large amounts of data. To pull out data from the cube you use a formula like this…

=LookupCube(“Sales”, “Units”, “Hats”, “Store 19”, “Oct-2007”)

…returns unit sales information consolidated by product line and region for the month shown. And this formula…

= LookupCube(“Sales”, “Sales USD”, “Hats”, “Store 19”, “Oct-2007 YTD”)

…returns year-to-date sales in US dollars for the same product line and region

Continue reading “Gemini – Smarter Excel Dashboards with End-User BI”

Data sources for Excel dashboards: avoid spreadsheet hell

This is the first of two twin posts where we’ll discuss the alpha and omega of Excel dashboards: data access and dashboard publication. These are two weak areas in Excel, and they should be approached carefully when planning for a new dashboard. Let’s start by reviewing the available data access options.

Copy / Pasting data

Are you or some one in your organization populating the spreadsheet manually? Or are you copy/pasting the data into the spreadsheet? This is the simplest method of getting data into Excel, but it can be dangerous. It should be avoided when better options are available.

When you are dealing with some kind of structured data management (like you do when you create a dashboard) you have to plan ahead and make sure that when data changes it doesn’t break your well crafted dashboard. Each function, each chart, must know where the data is and adjust for these changes when needed.

When you are pasting data there is a a high risk of break something. The number of rows or columns in the new dataset may change, and things like a time series chart may not recognize the new time periods and probably you’ll have to update references manually. Again, plan carefully or you end up in a maintenance hell.

External table

You can create a link to an external table in Access, Oracle or other database tool via a standard ODBC connection. This will ensure that the data is correctly funneled into the spreadsheet, but with real-world data it is very easy to have more records than the Excel 2003 limit of 65,536 rows. You’ll be better off if you link not to the raw data itself but to a query/view that aggregates the data (one of the basic rules for dashboard design in Excel is to avoid calculations and derivative data; the data should come from the source already prepared to be displayed).

Once the data is in Excel, there is not much difference between this and the previous option. You still need to use use lookup functions to retrieve the data and use it in report tables and charts, and data integrity is a stressful thing that you must ensure all the time. When possible, use database functions like DSUM instead of lookup functions (there will be a post discussing this).

Pivot tables

For an out-of-the-box Excel installation you may want to consider pivot tables. They are an interesting option for smaller datasets and they have a nicely flat initial learning curve. Please note that pivot tables will make your file size much larger because they store all the data in the spreadsheet, so scalability can become a major issue. Also, they work best with a strict hierarchical data structure. If your data doesn’t fit exactly in this concept this may be a problem. If you have a larger dataset you should consider an OLAP cube instead.

OLAP Cubes

The concept of an OLAP cube can be something scary for the average Excel user, but once you start using them you’ll never turn back. Specially of you are using what Charley Kid calls an “Excel-friendly OLAP cube”.

Unlike the other methods, an Excel-friendly OLAP cube (like XLCubed) will not store the data in the spreadsheet, thus eliminating the need for the usual data refreshing methods (open the dashboard, refresh, save and close). The cube is automatically updated and you can query it using formulas similar to GETPIVOTDATA. This makes a huge impact on the way you work. You get all the benefits of a regular pivot table plus several life-saving extras. The dashboard will be simpler, cleaner and easier to maintain.

Final Thoughts

You have several methods for data management in Excel, and you must decide what is the best method for each specific dashboard. Scalability is always an issue, so be sure your data don’t outgrow your chosen method. An Excel-friendly OLAP cube may require some immediate investment but will save you a lot of hassle in the long run.

Data management in Excel is a critical factor, and it will discussed in detail in future posts.

The next post discusses the other end of a dashboard project: how to make the dashboard available to the users.