SQL Server 2008 R2 Service Pack 2 (SP2) is Released!

Note the release of SQL Server 2008 R2 Service Pack 2 (SP2). Both the Service Pack and Feature Pack updates are available for download on the Microsoft Download Center. 
This a free upgrade, so please head over to the Download Center for a look.
This particular pack is interesting because it contains a few key community Reporting Services requests

  • Reporting Services Charts Maybe Zoomed & Cropped
    Customers using Reporting Services on Windows 7 may occasionally find charts are zoomed in and cropped. To work around the issue some customers set ImageConsolidation to false.This is tackled in the Service Pack
  • Batch Containing Alter Table not Cached
    In certain situations with batch files containing the alter table command, the entire batch file is not cached.
  • Collapsing Cells or Rows, If Hidden Render Incorrectly
  • Some customers who have hidden rows in their Reporting Services reports may have noticed rendering issues when cells or rows are collapsed. When writing a hidden row, the Style attribute is opened to write a height attribute. If the attribute is empty and the width should not be zero.

I’m holding a Business Intelligence and Data Visualisation Precon!

I’m delighted to announce that I’m holding a pre-conference one-day training in Data Visualisation in SQL Server 2012 in Cambridge on 7th September, 2012. For more details, and to register for the precon, please click here and to register for SQL Saturday 162 event, please click here.
My thanks go to SQLPass and to Mark Broadbent in particular, who have worked extremely hard in order to put on this community event. Here is an outline for my precon and please don’t hesitate to get in touch if you have any other queries!
If you want to conduct advanced, business-oriented Business Intelligence analysis in SQL Server 2012, then it is essential to understand data visualisation.

Using the new SQL Server 2012 Tabular Model and PowerPivot as data sources, this course will aim to teach about the new self-service Business Intelligence features in SQL Server 2012 whilst focusing on data visualisation. We will also look at SharePoint, and what it can offer.

This session is aimed at Excel and/or Business Intelligence developers who want to make informed data visualisation decisions about reporting, with supporting cognitive psychology theory where relevant. The takeaways will focus on:

  • Finding patterns in the data. 
  • Further Data Visualisations – learn about visualisations that are perhaps not so well-known including Stephen Few’s bullet charts and Tufte’s Sparklines in SSRS.
  • The complexities of displaying multivariate data. For example, we will look at Tufte’s “small multiples” in Power View and in Reporting Services.
  • Putting it all together: Considerations for Dashboards with PerformancePoint
9:00   Welcome and What is data visualisation? What it is not, and why is it important? What products make up the SQL Server 2012 Business Intelligence stack?
09:30  Tabular Model – what is it? When is it best used? What distinguishes it from multidimensional cubes?
10:30   PowerPivot – what is it? When is it best used? What distinguishes it from multidimensional cubes? What are the new features in SQL Server 2012?
11:15     Break
11:30   Finding patterns in the data. You will learn about the process involved in finding patterns in the data, looking at some of the more well-known data visualisation examples. 

 – We will learn about: Power View, using PowerPivot and the Tabular model as a basis. 
 – We will also look at tips and tricks in optimising Tabular Models so that they can render Power View effectively.

12:30     Lunch
13:30  Further Data Visualisations. In this section, you will learn about visualisations and how they are best used. This includes bullet charts, and other visualisations based on Tufte’s “small multiples” such as sparklines. We will learn:

 – We will look at PerformancePoint, and how we can use PowerPivot as a basis and ‘gotchas’ in using PowerPivot as a basis for PerformancePoint
 – We will look at Excel Services, and how we can provision self-service using the Tabular Model and PowerPivot as the basis for Excel and Excel Services
–   We will look at other helpful visualisations such as sparklines, bullet charts and marimekko charts.

15:00 Multivariate statistical data. In this section, we will cover the complexities of displaying multivariate data since is potentially more complex. Here, we look at ways of displaying multivariate data such as table lenses and crosstab arrangements of Tufte’s ‘small multiples’. We will use Reporting Services as a technology to surface multi-dimensional data.
15:15 Break
16:15 Dashboards – Putting it all together. We will look at different ways of implementing Dashboards, KPIs and other visualisations. This will involve a range of technologies, from KPIs in PowerPivot, to the new features in Power View.
17:30 Close

Power View connection error – Resolved

When you’re starting off to create a visualisation in Power View, you might notice the following error:


rsCannotRetrieveModel
400 

http://www.microsoft.com/sql/reportingservices”>An error occurred while loading the model for the item or data source ‘http://sapphire/PowerPivot Gallery/HelloWorldPicnicPowerViewTutorialRC0.xlsx’. Verify that the connection information is correct and that you have permissions to access the data source.

When you read down to the foot of the error message, the error message becomes more specific. Here is an example:

A connection cannot be made to redirector. Ensure that ‘SQL Browser’ service is running.


When you start up the ‘SQL Browser’ service, then the Power View visualisation works.  The SQL Server Browser operates as a Windows Service. It has two main roles: it listens for incoming requests for Microsoft SQL Server resources, and it provides information about SQL Server instances installed on the computer. If it isn’t running, then the connection fails since the data source can’t be found.




Collective Intelligence in the SQL Community

How does Collective Intelligence manifest itself in the SQL Server community? In case you are wondering, Collective intelligence is intelligence that emerges from the group, and shows itself in the collaboration and competition of many individuals. The way I look at it, this can be easily translated to the SQL Community which is a lively ecosystem, where the collaboration of many clever people accelerates the flow of knowledge and information for the good of everyone. If you’re interested in collective intelligence and new ways of thinking about this intelligence in an egoless environment, then I’d suggest that you take a look at Ledface.

In particular, the Ledface article on ‘Knowledge is not mine, not yours. Only the arrangement is ours’   is interesting, because we can see these concepts manifested in the SQL Server community. For me, Ledface make a very subtle point about helpers seeing an improvement in the overall domain in which they care about. I think that’s true in the SQL Community: I can see the passion where people really care about the next level for SQL Server, and pushing it forward for the benefit of SQL Server, its users, and the people who look after it. It’s about making it better for everyone else, as well as for individuals.

In order for this to work in a social environment, however, there needs to be minimal organisation with little or no rules. For example, if you use the Twitter hashtags incorrectly, then the community may sanction you by voicing this directly in a robust 140 character riposte, or by simply unfollowing you. If you’re really unlucky, you’d be blocked! For this to work, then I think that there is something in swarm intelligence to the SQL community; we organise ourselves, we help ourselves, and we sanction ourselves. The community is decentralised since we work all over the globe, which means that help is available 24 x 7 in a ‘follow the sun’ methodology.

In the SQL Community, we see examples of this helpfulness in many different ways.  For example, a newbie SQL novice contacted me recently to ask for links to T-SQL forums on the Internet, where they could post up some T-SQL questions. Here is a quick list of some useful resources:

SQL Server Central
In case you haven’t explored this site, it also has a dedicated scripts section which is a good place to look for scripts

Microsoft forums – This is a dedicated T-SQL forum, which is always useful

– I use Twitter in order to answer questions sometimes. I like doing this, because it means you are helping someone in ‘real time’ at the point at which they need it. The Twitter hashtag is #SQLHelp and if you need to know the very informal rules around asking these questions, a useful resource by Brent Ozar is here.  Although the help is ‘real time’, Jamie Thomson looked at this issue in his blog and I’d suggest you take a look.

Brent Ozar rightly points out that, as a courtesy, it’s nice to thank the Twitterati who have helped you via the #SQLHelp hashtag. I’d extend that courtesy out to the people on SSC and the Microsoft forums.

In my opinion, the SQL Community is stellar, partly because of our collective intelligence, but the ‘helping hand’ that we extend to one another. Long may it continue. I look forward to your comments.

Intelligent Laziness: Connecting to a GoDaddy SQL Server using SSIS and Tableau

I try to apply the principle of Intelligent Laziness to make life as straightforward as possible. It’s hard to find meaning and clarity in a world that guarantees neither, and the Absurd perspective means that I do not stop trying in any case. Please note that I’m not associated with GoDaddy in any way, and I’ll be pleased to hear of any other hosting options that you use; please leave a comment below.

One aspect is to try and get access to the data I need to deliver Business Intelligence projects, as quickly and effectively as possible. As a freelancer, however, there is an additional issue around physical location: mine and the clients. I don’t always work at the client site, and can work from home. In this case, I need access to data in a way that does not necessarily mean that I have access to the client’s network or VPN.

One solution I’ve found is to get client data via a GoDaddy hosted SQL Server databases, and I use SSIS to consume the data directly from there. There are a lot of posts already on connecting to GoDaddy hosted SQL Server databases using .Net and so on, but it’s very easy to connect directly from within SSIS. This post is aimed at showing you how easy it is to do that.

In order to show what we’re aiming towards, here is an example of a very simple dataflow, which retrieves the data from the GoDaddy source, and places it into a text file. If you click on the image, it will take you to a larger version held at my Flickr account.

1_InitialDataFlowOverview

In order to access the GoDaddy source, then it will be necessary to set up the connection to GoDaddy.  To do this, you will need the following items:

Server details: This will look something like stirrj.db.1234567.hostedresource.com
Username: this will be SQL authentication, not Windows authentication. So, type your SQL authentication username here. In this example, the username is ‘stirrj’
Password: This will be your SQL authentication password.
Database list: Once you’ve put this information in, you should be able to see a list of databases in the drop down list. Here, the example database is called ‘denali’.

To assist you, here is an example of how it looks:

2_SSISConnectionManager

Once you’ve set up the connection manager, you can take a look at retrieving the data as normal.  Here is an example query, which retrieves data against the database hosted at GoDaddy.

3_OLEDB_SourceEditor

You can see your OLE DB Connection Manager name in the drop-down list.  Once you’ve set this up, you can test out that it works, and hopefully you will get some green boxes, as below:

4_PackageSuccess

I personally haven’t experienced any issues with the speed of retrieving data.  However, if you want to load this data into a Tableau workbook, you may want to consider a number of ways of optimising the speed of retrieval:

– you could try to connect directly. I haven’t tried this but I imagine you just use the sql server reference details as above
– Using SSIS, you could extract the data from GoDaddy to a local SQL Server environment for development work. I’m integrating data from various sources, so this is working for me. I then use a Tableau data extract (TDE) to hold the data in the Tableau workbook, and simply refresh the TDE extract. This works very nicely, particularly since I can share the Tableau workbook with other Tableau as required.

One important note to make is that the Tableau Desktop edition will not connect to SQL Server, remote or otherwise. If you are a Desktop user, you will need to extract the data to a CSV file, and then import the data from the CSV file.

I hope that helps someone; any questions, just leave a comment and I will come back to you as soon as I can.

Does Microsoft SQL Server Denali put us in denial about the ODS?

Microsoft have provided a first taste of the latest version of SQL Server, codenamed “Denali”. Denali focuses on mission-critical systems, scaleability, multi-site clustering and high availability, with facilities for data integration and data management tools to reliably deliver robust data. Denali also offers advanced data visualisation, supported by column-based accelerations in query performance. Given these technological advancements in database management in Microsoft (as well as other technologies), what’s the future of the ODS? 
Historically, mainframes could not offer quick reporting, and the data warehouse was subject-oriented. Thus, another reporting provision mechanism was required, that was situated between the source transactional systems and the final data warehouse. Data warehouses caused issues for database management, since they were required to balance small queries with large queries involving millions of records. This reporting needed to be abstracted away from the silo-based source systems, so integration was required; thus, the ODS was born. 
There are different methodologies surrounding the architecture of an ODS. Inmon defines the ODS as an integrated source of data, which offers an ‘as-is’ status of the business, holding only the most recent transactions. ‘Recent’ does not mean data that is only a few days old; the ODS may be feeding into a summarised data warehouse, and may need a full month’s data in order to come up with the final summary monthly data item required for the summary data warehouse. On the other hand, Kimball defines the ODS as the structure that stores detailed transaction data, which is not present in the data warehouse. However, as technological advances are made in database engines such as SQL Server, the transaction detail can be stored in the data warehouse, and can be interrogated by business users. This means that the place of the ODS can look uncertain.
In my experience, I’ve found that operational source systems are becoming more sensitive to the need for reporting. One example here is the Cisco Contact Centre call management database, which is based on SQL Server, which feeds into a dedicated reporting system. If Operational data sources are becoming more accommodating to serving reporting architectures, and technological advancements have been made in database management, what is the future for the ODS? Particularly, with the advent of more streamlined ETL applications such as SSIS offering lower-latency data delivery to users.
So where does the ODS fit in? Before the detailed transactional data can be placed into the data warehouse, it needs to be rationalised and integrated. Whilst stored in the ODS, the current operational data can be cleansed, integrated from its source systems, and steps can be taken towards redundancy resolution. Further, data in the ODS can be investigated for compliance with business rules.
However, an ODS should not, as Jill Dyche calls it, ‘compost’ data. In other words, the data sources have been pretty much copied into a data store with no concerted effort at integrating the data. Thus, you could have ten distinct ‘customer’ tables, from different sources, and have these ten tables copied directly into the data store with minimal – if any – integration taking place. This type of scenario is more akin to a staging area, than a dedicated ODS. However, here, the ODS starts to look like a data warehouse, since the ODS also has complex multiple data sources and dedicated to a number of subject areas. 
Instead, the ODS should be viewed as an interim logical step towards the data warehouse. At first glance, it may seem that the only difference between the ODS and the data warehouse is that the ODS does not store history, whereas the data warehouse does store history. This is not necessarily the case. The key to the ODS is current operational data. The ODS could potentially provide rapid access to current operational data, whereas the enterprise data warehouse may not get refreshed until overnight. How much ‘history’ is stored depends on user requirements, although I suspect that most business users would say ‘we need the data right now, and we want to keep it forever’ if they were consulted! This means that data could be interrogated for analysis and reporting purposes whilst the business operations are ongoing, and before the data is transferred to the data warehouse for longer-term reporting, data mining, or perhaps archiving.
The ODS can potentially be used to farm data back to source systems. However, it is optimised by having a Master Data Management system for as a robust library of reference data. Although Master Data Management has been around for some time, it has become more relevant with its specific inclusion with SQL Server 2008 R2. MDM is concerned with data quality and consistency, and the reconciliation of any data issues. Specific MDM implementations can depend on the environment. For example, the ODS and data warehouse can be supported by a Customer Data Integration module, or CDI, which is a ‘golden’ source of the enterprise customer data. The CDI can be viewed as a library or ‘hub’ for faciliating the consolidation and provision of good quality customer data across the enterprise, coming from various operational sources. An ODS and a CDI are complementary to one another; the CDI can have dedicated algorithms to perfect the customer data, the result of which can be served to the ODS. The CDI is not a facility for serving up reporting; this is the job of the ODS and the data warehouse. Further, the ODS should be viewed as a facility for integrating sources of different types of current operational data across the enterprise, but the CDI could be viewed as a nimble younger sibling of MDM, dedicated to customer information only.
With MDM, and potentially CDI in place, the ODS can offer data up more quickly since it has a good basis to offer up data, since the integration can happen more quickly. If the CDI and MDM are properly architected, then their offerings of good quality integrated data can result in low latency data served to the ODS, thus accelerating the process of provisioning data to users. 
To summarise, what’s the future of the ODS? With the advent of Denali, it is hoped that an optimised user experience is available to all, from DBA right through to operational users supported by MDM and data warehouse, with the ODS in a clearly-defined role. The key to an ODS is in integration; a well-integrated, current operational data store, where data is cleansed, governed and made compliant before it enters a data warehouse. If it isn’t integrated, its shelf-life is most likely minimal since the business users won’t be served properly, and may start to export data into Excel for their own slice and dicing. The ODS needs to be carefully considered, along with user requirements; if not, it is in danger of becoming a ‘political football’, and the business questions and needs ultimately not being answered.

Bullet Charts in SQL Server Reporting Services 2008 R2: ‘How To’ Video

Recently someone told me that they were struggling to produce a Stephen Few inspired bullet chart using Microsoft SQL Server 2008 R2. This is perfectly understandable, because, for some reason, bullet charts are considered a ‘gauge’ in Reporting Services. This is ironic because, if you’ve read Stephen Few’s material, then you will already be aware that bullet charts were designed to be more meaningful in displaying data than gauges and pie charts.
In order to help individuals to produce bullet charts rather than gauges in SQL Server Reporting Services 2008 R2, I’ve produced a quick video which I hope will help. Before we dive into the video, let’s have a look at the end result:
Bullet Graph Video Result
The purpose of this chart is simply an example, which shows the sales data for David Hume and John Napier. The sales ‘target’ is the little vertical line that you see in both charts. So, for David, the line is set at 75%, and for John, the target is set at 70%.
The ‘actual’ sales is represented by the thick blue horizontal line. So, for David, he sold 80% possible sales. John, on the other hand, sold 100% possible sales.
The bullet chart is nice simply because it allows you to quickly compare target values with actual values, and it also allows you to quickly look down and compare ‘between’ values as well. Here, it is quite easy to compare David’s actual and target sales with John’s actual and target sales.  Bullet charts take up less room and express meaningful information, so we can compress more information into a given space than we would get from a gauge.
Normally, in a bullet chart, you may consider removing the percentages or other values along the quantitative scale. However, in order to try and be as clear as possible, I have left them in.
I hope you enjoy the video, which I’ve placed on YouTube and you may also see here:

I look forward to your comments.

Add to Technorati Favorites