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. 

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

ShareThis

The Competition

Like last year, the competition is for real world solutions, we are not providing a sample data set, and we’re looking forward to seeing some great examples of reports, charts and dashboards.

The dashboards are judged on the clarity and effectiveness of their design, particularly

• Clean and clear organization
• Effective table and chart design
• A single-screen display, properly designed for the web, screen or print outs

We’ll also consider technical aspects of the dashboard, did it use effective  techniques for

• The Dashboard layout
• Data management, data logic and calculation : YTD figures, variances, etc….
• Dashboard delivery: Sharing the dashboard via PDF, the web or as an Excel Workbook

There will be prizes for the top 3 entries, with the winner having first choice of prize from:

• 1st Prize: Amazon gift certificate for $100
• 2nd Prize: Stephen Few’s New Book “Now You see it”
• 3rd Prize: Edward Tufte’s classical Book “The Visual Display of Quantitative Information”

The Rules
We’ve kept the rules simple:

• The solution must be in Excel 2000 or more recent, and not require additional software other than Excel and Chart Tamer , MicroCharts and XLCubed.

• Entries can use any combination of tables, Excel charts,  bullet graphs and MicroCharts (sparklines). Each have their strengths and role to play in an effective dashboard

• We will publish the top 3 dashboards on our website, so please ensure this is not problematic for any of your submissions.

• Please change names and data as appropriate in the dashboards to protect the innocent.

• If you are not an existing MicroCharts or Chart Tamer user, you can download your 30 day trial version at http://www.bonavistasystems.com/DownloadMicroCharts.html

• Final Entries by 19 July 2009, Judges decision final!

To get inspired have a look at last years winning entries.

Please send your entries to ExcelDashCompetition@XLCubed.com

ShareThis

Special thanks to our Expert Testers who helped us to make Chart Tamer a great product.

Chart Tamer aligns Excel’s charts with the best practices of data visualization .

For an introduction into Chart Tamer and Chart Tamer concepts read:

• Chart Tamer Introduction (PDF document)

• Practical Rules for Using Color in Charts

ShareThis

XLCubed Videos:

Video 1 - Getting started with XLCubed In this video we show how to get started with XLCubed, creating a connection to you analysis cube and building a sample report.

Video 2 - XLCubed Grid Navigation in this video we show how to navigate around an XLCubed grid in Excel and utilise some more of the functions.

Video 3 - Introduction to formula reporting with XLCubed Excel edition in this video we show how to use the formula mode in XLCubed to query an analysis services cube.

Video 4 - Publishing a dashboard to the web using XLCubed in this video we show you how you can publish your XLCubed report/dashboard to the web using XLCubed web edition.

Video 5 - Ad-hoc reporting using XLCubed Web using the XLCubed web edition we demonstrate ad-hoc reporting against an analysis services cube in a thin client web browser.

Video 6 - XLCubed user defined calculations This report shows how you can use XLCubed Excel edition to create user defined calculations.

Video 7 - Extending an XLCubed report with Excel functionality The first brief introduction to extending an XLCubed report using Excel functionality. In this video we show how you can drive a report from an Excel range.

Video 8 - XLCubed Relational database support The first video using XLCubed to connect to a relational database, querying the dataset using SQL.

Video 9 - XLCubed Report Templates How to use XLCubed templates to provide a starting point for end users.

Video 10 – coming soon, using parameters in a URL to drive an XLCubed report

Video 11 – coming soon, visual grids – XLCubed grids with integrated MicroCharts

Channel: http://www.youtube.com/xlcubed

MicroCharts Videos:

Video MC1 - Introduction to MicroCharts - Sparklines An introduction to creating Sparklines with MicroCharts for Excel, in cell charts for your Excel spreadsheet or dashboard.

Video MC2 - Bullet Graphs in Information Dashboards  An overview of Bullet Graphs, and how to build them using MicroCharts in Excel

ShareThis

Version 5 is joined by a new Website which now includes a series of ‘how do I’ youtube videos, available on the individual product pages or at the XLCubed youtube channel. The content will be expanded across additional areas in the coming months, and we hope will become a key resource for customers and evaluators alike.

ShareThis

You can download MicroCharts for Reporting Services 2008 here.

ShareThis

We released today a beta version of our latest BonaVista product Chart Tamer. We released Chart Tamer as a private beta to a group of Excel Experts: Jon Peltier, Charley Kyd, Jorge Camoes, Kelly O’Day, the folks at Juice Analytics, Rolf Hichert and Chandoo. Based on the feedback we get from this group of selected experts make the product public available end March.

I would like to share with you some first impressions about what Chart Tamer does and how it looks like. The following is an an excerpt of the Chart Tamer introduction document written by Stephen Few, which you can download below.

What Chart Tamer Does

Chart Tamer improves the charts that we produce with Excel in the following ways:

• It limits the list of available charts to the few that are most useful, thereby reducing the complexity of choosing an appropriate chart
• It provides a simple new interface for selecting an appropriate chart, which guides us to the right selection when help is needed without bogging us down when it’s not
• It adds three useful charts that aren’t currently available in Excel: dot plots, strip plots, and box plots
• It revises the formatting defaults of Excel’s charts so that no extra work is necessary to create charts that work effectively
• It provides colors for use in charts and elsewhere that were designed to work especially for data presentation
• It allows us to quickly and easily improve old charts that were created prior to Chart Tamer

When you use Chart Tamer to create a new chart, the following dialog box makes it easy to choose the most appropriate chart:

Rarely will we ever need a chart other than the 14 that appear in the Select a chart section above. If we already know which of these charts will display the data best, we can click it immediately, without having to wade through any other choices. If we want some guidance, however, we can narrow the list of available charts by making selections in the Select a relationship and Select what you want to feature sections.

Let’s assume we want to create a chart for annual product sales data:

We select the time series relation ship type what narrows the list of available charts to the six charts below:

The list of available charts can be narrowed further by selecting an item in the "Select what you want to feature” section.  In our scenario we want to show how sales has changed through time and also want to feature the overall trend, so we choose Overall trends and patterns.  This further narrows the list of available charts to the two versions of the line chart charts below:

Clicking the line chart icon creates you a reasonable formatted line chart.

The Chart Tamer default dialog box allows you to adjust the defaults of the chart data objects:

Here’s the Color dialog box that we use whenever colors need to be assigned to something in Excel:

The colors that Chart Tamer provides were carefully selected by Maureen Stone of StoneSoup Consulting, an expert in the use of color for data presentation. Chart Tamer makes it easy to avoid bad uses of color in the following ways:

• It provides color palettes that were carefully selected for their ability to present data effectively, which means that you would have to go out of your way to select poor colors
• It sets default colors for all the components of charts (line colors, bar colors, etc.)
• It simplifies and streamlines the color selection process through its Color dialog box
• It teaches us effective color design practices through example

Download the full story behind Chart Tamer written by its Chief Designer Stephen Few:

(Update 11 Feb 2009: Temporarily removed the documents for a revision)

ShareThis

  
Once you have installed MicroCharts for Reporting Services 2005, you have a set of MicroCharts controls available in your Toolbox.

The MicroCharts controls can be used in the same way as other controls, i.e. the Textbox, you drag the MicroCharts control from the Toolbox into your report. MicroCharts are designed for use in the Reporting Services table object, and can simply be dragged into a column.

After you have placed you MicroCharts control in the report you can format the charts by right clicking it and selecting the command Properties:

The command opens the MicroCharts formatting dialog box which allows you to specify the chart’s formatting options:

The data is configured in the Values property of the standard properties pane under the Data section:

The MicroChart Value properties can be set to a normal Field Expression like

=Fields!LastMonths.Value

For any fields with more than 1 value (i.e. a MicroLine data series) the data values are defined as a pipe separated string

“Value1|Value2|…|ValueN”

To generate a pipe separated string in Analysis Services use an MDX expression like

GENERATE(LASTPERIODS(12,[Time].CurrentMember), [MyMeasure], "|")

And For SQL Server use an expression like

[Field1] + ‘|’ + [Field2] + ‘|’ + … + [FieldN]

All chart formatting properties are also available as in the standard properties pane under MicroCharts.

The formatting properties can use expressions to calculate the property directly from the data set:

For example you can achieve a dynamic chart scaling by calculating the Min/Max properties of the bar chart by using the formulas

=Round(Min(Fields!Variance_.Value)*1.2,1)

=Round(Max(Fields!Variance_.Value)*1.2,1)

The Action properties in the general property pane allow you to define a Jump into a detailed report.

To define the Jump parameters press the … button

Optionally you can pass parameters from the calling report to the target report:

Supported Data Sources

• Microsoft SQL Server
• Microsoft Analysis Services,
• OLEDB, ODBC
• Sap BI NetWeaver
• Hyperion Essbase
• Oracle
• Teradata

Features

• Line, area, column, pie, win-lose, icon and bar charts
• Bullet graphs - an effective replacement for dashboard gauges.
• All properties support expressions
• Support for Actions
• Intuitive formatting interface

We will soon release a version that supports Reporting Services 2008, so stay tuned!

ShareThis

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.

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, 1^st January 2008 would be a member, as would 20^th 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.

ShareThis

• Text alignment
• Text orientation
• Font size
• Conditional formatting
• Automatic alignment when the row height changes

 
Use text alignment to align MicroCharts in the Excel grid

Create vertical MicroCharts using Text Orientation

 
Color-code bars using Conditional Formatting

The downside of the font based charting is that you have to redistribute the fonts, otherwise end users see weird Chinese characters in their reports.

We usually recommend our users either:

1) Redistribute the free MicroChart fonts. With this approach, prior to distribution, the owner uses the ‘Convert to Font Charts’ option on a copy of the report and distributes this version. This which works well for static reports. Note that if there is any interactivity through drop down menus which can change the MicroCharts then Excel based recipients will need MicroCharts itself installed.

2) Publish the reports to XLCubed Web Edition. Here the reports and MicroCharts can remain interactive, and the end user does not require any client side installation or fonts on their local pc. There is also the benefit that the reports can connect directly to cubes or relational databases.

With the font based approach, experience shows often end users won’t install the fonts, and send emails to the report author asking about the weird characters.

To overcome this problem you can write some VBA code to test if the MicroChart fonts are installed. John Walkenbach has some nice VBA code on his site that allows us to test if a font is installed:

Function FontIsInstalled(sFont) As Boolean
‘   Returns True if sFont is installed
    FontIsInstalled = False
    Set FontList = Application.CommandBars("Formatting").FindControl(ID:=1728)
‘   If Font control is missing, create a temp CommandBar
    If FontList Is Nothing Then
        Set TempBar = Application.CommandBars.Add
        Set FontList = TempBar.Controls.Add(ID:=1728)
    End If
    For i = 0 To FontList.ListCount - 1
        If FontList.List(i + 1) = sFont Then
            FontIsInstalled = True
            On Error Resume Next
            TempBar.Delete
            Exit Function
        End If
    Next i

‘   Delete temp CommandBar if it exists
    On Error Resume Next
    TempBar.Delete
End Function

To test if MicroCharts are installed you call FontIsInstalled("Micro Line Charts 3.0") and if the test fails you navigate to the URL of the MicroCharts font installer package:

Sub TestMicroChartsFonts()
  If Not FontIsInstalled("Micro Line Charts 3.0") Then
    MsgBox "The MicroChart fonts are not installed on your computer. " & Chr(13) & "Open MicroChartsFonts.zip, run the font installer (SparkLinerFontSetup.msi) to install the MicroChart fonts."

    Set wshshell = CreateObject("WScript.Shell")

    wshshell.Run http://www.bonavistasystems.com/Download2/MicroChartsFonts.zip
  End If
End Sub

Finally copy the Micro Chart test code in the report workbook and call it in the Workbook_Open handler:

Private Sub Workbook_Open()
  TestMicroChartsFonts
End Sub
  

  
  
  

ShareThis