Before I answer that question I want to be clear on what exactly self service BI means as you could argue that users have been doing BI for themselves since the spreadsheet was invented. In those early days users would often have to rekey data into whatever analysis tool they were using as the source information was often a printout from a mainframe or mini. With the advent of odbc, users could be given permission to access source systems directly and get at the data they needed. However this lead to a number of problems:

· The end user would need a good knowledge of SQL

· The data returned might well be inaccurate as a knowledge of the structure and contents of the source system would be required.

· Their queries might perform really poorly and possibly affect other user of the system and overall performance.

· The resultant data was a point in time snapshot and often it would be impossible to track where a given set of data had come from and when the query had been run, as the end user had to consciously record that.

So IT had to help out. We had to provide appropriate access to the data, educate and train the users about SQL and the source systems and possibly provide canned views or stored procedures so they could get the data they needed with minimal impact on the source systems.

This was all fine and well if the data required was only in one system, however this was rarely the case, and there were also problems in getting historical data for trend analysis as this might not be in the line of business systems but in separate archives. The answer to this was the the data warehouse and tools to provide a business friendly view of this data so end users didn’t need to understand SQL or how the data warehouse itself was constructed. IT created the data warehouse and maintained that business friendly view (aka the semantic layer), and the users had various tools to report and analyse its contents.

However there was still a problem, the time and effort need to do this meant there was always a lag behind what users wanted and what IT could deliver. For example there are sets of data on the internet like data.gov.uk and social media metrics that are external to a business. Not only that the rate of change in modern business is more rapid than ever in response to unforeseen external factors like the economy, natural disasters, and the on-demand existence that consumers expect .

This has seen the rise of in-memory analysis tools that can handle sizable chunks of data on even a modest laptop. These tools have simple to use import procedures and can consume data from web services via Odata, from xml as well as traditional data sources, and because they capture where the data comes from it is a simple matter to refresh the data from source, on demand. Coupled to this they have built in intelligence to mash-up disparate sets of data into sophisticated analytics. So does this mean that IT is no longer involved in the process?

No, that data warehouse still has its place as a central trusted repository of internal information. Strategic scorecards and dashboards will still have their place too. However when exceptions occur or there is an external factor which could have a major impact (positive or negative) on the business then the end user BI tools will provide the analysis needed to make the necessary correction to the decision being made.

So what does IT provide in the self-service BI world?

It may be stating the obvious but any end-user BI tool is only as good as the data that is fed into it, and so IT has a key role to play in maintaining data quality. This is partly about cleansing the data and partly about augmenting it for more meaningful analysis. Cleansing means de-duplication, correcting keying errors while augmentation processes will add in missing data. In both cases IT will be working closely with users to do this, to establish the rules and provide interfaces for the users to enter missing data if automated processes detect this but cannot fix it.

In addition to maintaining the data warehouse I can see the need for IT to offer a data market of Odata services to the end user from which they can self-select sets of data they need to make timely decisions. This would to some extent replace the need to produce piles of reports, but in either case it’s important to track usage of reports to weed out those no longer in use.

If some of the users self-service BI analytics move from being tactical to strategic; from being just for their team/department to being enterprise wide, then IT will pick these up and scale them using server rather than PC based technologies.

As Ralph Kimball noted many years ago in his seminal work the Data Warehouse Lifecycle Toolkit, collaboration between IT and business is a critical success factor in any BI project and this is as true today with our modern tools as it was when he first started in BI.

.

If you didn’t manage to fight your fight your way through the Everton & Liverpool fans to SQL Bits I thought you might like to see some of the random questions I got asked..

How do I manage SQL Azure?

I think the key thing about developing for the cloud is to ensure that your application can handle retries as session disconnect and reconnect.  You will also want to ensure your code is well optimised and ideally your application that’s using SQL Azure is in Azure itself.

Having done that you should be checking your application is running and the best way to do this is to use the  built in management views (DMVs) that Azure has. These track such diverse information as:

• session information
• bandwidth usage
• configuration like firewall rules
• database copy information

Evaluation & Testing

There are time-bombed evaluation copies of most of the Microsoft platform, but in order to look at something like the new Crescent reporting tool in SQL Server Denali, on a virtual machine you’ll need a copy of windows server, Office , SharePoint as well as the Denali beta itself. So abetter way to this is to use either a TechNet or MSDN subscription.  Apart form the fact that MSDN is more expensive they are there for different purposes and have different uses as this grid from MSDN shows:

So although licenses included in both subscriptions aren’t time bombed, only MSDN subscription licenses are allowed to be used for testing, as well as evaluation.

When will Denali be released

There is no specific release date for SQL Server Denali, maybe there will be some announcements at PASS this week but I doubt the date will be fixed. Suppose I could tell you this would you really plan your project around that given that I still talk to customers on Windows XP with SQL Server 2005.

OLAP & Tabular BISM 

The shiny new Tabular based analysis services has not replaced OLAP.  In the current beta (ctp3) Crescent only works against a tabular BISM model, and I am hoping this will be fixed fore the release of Denali.  However OLAP offers a more sophisticated language (MDX), the ability to model many to many relationships to deliver more complex analytics, and has data mining built in.  There are also enhancements to OLAP in Denali, so you need to decide how and when to use each technology in your organisation.

I could go on but I am about to jump in my car to head off to Birmingham for tonight’s SQL Relay in Birmingham, so if you are there tonight or in Leeds, Edinburgh, Newcastle later this week then come along and say hi and bring your questions.

Further to my last post, I also see a lot of confusion around Microsoft’s server virtualisation engine, Hyper-V, in part because it is so easy to setup. The easiest way to start using Hyper-V is by adding the Hyper-V role in Windows Server (2008 & later). Roles are the services that a server provides and features are what a server does and by default all roles and features are disabled when  you  do a clean install of Windows Server.

Having done that (which requires a reboot) you get the hyper-V manager when you expand the role and then connect to you server and you are ready to create or import virtual machines (VMs).  The problem with this approach is the appearance of a whole copy of windows on which you then put your virtual machines and this is not strictly true, what you have is a copy of of Windows from which you are managing Hyper-V as per this old diagram:

so Hyper_V sits under all of the operating systems including what is referred to as the parent partition on this diagram (which is nothing to do with parent partitions on disks)

This is not the only way to run Hyper-V, there is free version of Hyper-v called Hyper-V server which you can download here.  This is based on server core, an install time option in Windows Server (2008 & later) that is the bare minimum operating system with no real graphical interface, and is either managed from the command line (e.g. with PowerShell, DOS, netsh, diskpart etc.)or remotely.  Hyper-V server is even more cut down, it has all of the roles and features removed except hyper-V and so you need be pretty good at command line stuff or know who to connect and setup the remote administration tools for Windows.

So having got a hyper-V environment setup you can then use the create new virtual machine wizard in the virtual machine manager to create new virtual machines.  This process is similar to the way you would specify your requirements for a physical server except that you are telling the wizard which resources you are using on the physical server the VM will run on.   You can also import a VM from an export created on a another Hyper-V environment or complete the wizard but use a VHD that you have got from somewhere e.g. there are some on  Microsoft to save you having to install and configure a Microsoft application in order to evaluate it.

If you are creating a new virtual machine, then the virtual hard disk will be empty and will need an operating system.  Theoretically This can be anything that runs on x86 x64 hardware form DOS 3.3 to Windows 8, or even other OS’s like Unix, and Linux.  However Microsoft will only support it’s operating systems and applications that are supported to run on physical hardware e.g. Windows 7 and not Windows 95 which is out of support.  They word support here means you can get support from Microsoft and not a vague statement along the lines of  “we got it to work but you’re on your own if you get stuck”. When it comes to Linux the latest versions of Red Hat SUSE and Centos are also supported because those distros have been made the subject of support arrangements between those Linux vendors and Microsoft so you can get support from Microsoft for them.

I have posted a step by step PowerPoint deck to help you do all of this on SkyDrive (if you view it in the browser expand the notes at the bottom of the screen). If you want to try this all you’ll need is an old machine* and an evaluation copy of Windows Server  2008 R2 sp1

*The old machine will need to have either an Intel-V or AMD-VT cpu and you may need to enable virtualisation in the bios to get hyper-V to install cleanly

This might seem an odd post to write, given that server virtualisation has been around for years, however at a couple of recent events I was at it became obvious that there still loads of intelligent, reasonable IT professionals who don’t understand what it is.  If that’s you please read on if not I won’t be offended if you click away now.

So what is server virtualisation and why should you care?

It started off as a simple recognition that most servers and PCs are largely idle , running at between 10-15% of capacity.  This is waste the capital asset – the hardware itself and it also consumes a lot of unnecessary power. 

The concept is a simple one: run multiple virtual machines (VMs) on one physical server, each with its own operating system and applications. Each disk in a given VM shows up as a single  file on the physical server and other resources such as networking memory and cpus are then assigned to each VM through some sort of console (depending on the vendor). 

So implementing this approach means that one server is now doing the work of 6 –10 servers, and this gives all the benefits of less power less cooling and less space in needed in a data centre.  There is also now the possibility to move VMs from server to server to balance workloads, to copy them for testing e.g. applying patches etc.  and development.

Desktop operating systems like Windows 7 can also be run as VMs on servers and end users can then connect to one of these from a thin client device or any old desktop to create a Virtual Desktop Infrastructure (VDI)

Of course there are downsides to server virtualisation:

• The failure of any of few physical servers left will have a bigger business impact so steps should be taken to make them highly available.
• Each VM still needs to be maintained patched etc.
• VM sprawl can be an issue now that it is so easy create new VMs to management and control are essential.
• It’s not obvious which VMs are mission critical and who owns them so management and control critical.

However these have not prevented many organisations adopting this on a wholesale basis for their production environments and there is a healthy job market for  IT Professionals who are experts in that field. Many of the skills required are simply an extension of what most of us do today in configuring physical servers, however the setting are now done in management tools or via scripts rather than, fiddling with actual hardware.

To find out more about virtualisation Microsoft has the Microsoft Virtual Academy , a collection of online resources to help  people new to virtualisation get up to speed complete with simple tests to make sure it’s all sinking in.

Normally the term sprawl carries an implied criticism, in the case of SQL server that DBAs have lost control of their infrastructure and users are install it willy nilly without regard for data protection or disaster recovery.  However when it comes to SQL Server user group meeting then sprawl is a good thing, as it means there’s probably regular user group meetings somewhere near you on a regular basis.  In October for example there is SQL Relay a UK wide series of events , I am doing three of these and Itzak ben Gan, SQL rockstar form SQL Skills has got a gig in London on the 6th.

Monday 3rd October

Manchester SQL Server User Group

SQL Relay: An Evening With Allan Mitchell(MVP) on Data Quality Services and Master Data Services in SQL Server Denali

Manchester

http://sqlserverfaq.com/?eid=319

 

SQL Midlands User Group

SQL Relay: High Availability with Tony Rogerson(MVP) and SQL Denali with Andrew Fryer from Microsoft

Birmingham - Midlands

http://sqlserverfaq.com/?eid=296

 

Surrey SQL Server User Group

SQL Relay: A packed evening with amongst others Christian Bolton MCA/MCM/MVP

Farnham - Surrey

http://sqlserverfaq.com/?eid=321

Kent SQL Server and .NET User Group

SQL Relay: This wisdom of James Rowland-Jones(MVP) & Replication with Neil Hambly

Ashford - Kent

http://sqlserverfaq.com/?eid=322

Tuesday 4th October

Leeds Area SQL Server User Group

SQL Relay: Mobile Business Intelligence in Action with Jen Stirrup (MVP) and Andrew Fryer (Microsoft Evangelist) on BISM and Crescent

Leeds - West Yorkshire

http://sqlserverfaq.com/?eid=299

 

London SQL Server User Group

SQL Relay: On Guitar Mr Chris Testa-O'Neill (MVP) and Neil Hambly on XQuery

London

http://sqlserverfaq.com/?eid=320

 

SQL Server Club

SQL Relay: An evening with Tony Rogerson (MVP)

Bristol

http://sqlserverfaq.com/?eid=325

 

SQL Herts User Group

SQL Relay: An evening with Chris Webb (MVP)

Hatfield - Hertfordshire

http://sqlserverfaq.com/?eid=281

 

Maidenhead SQL Server User Group

SQL Relay: Inaugural Meeting of the Maidenhead SQL Server User Group

Maidenhead - Berkshire

http://sqlserverfaq.com/?eid=328

Wednesday 5th October

Scottish Area SQL Server User Group

SQL Relay: Andrew Fryer (Microsoft Evangelist) on BISM and Crescent and Martin Bell(MVP) talking about What's new for developers in SQL Server Denali

Edinburgh - Scotland

http://sqlserverfaq.com/?eid=311

 

Cardiff SQL Server User Group

SQL Relay: SQL Server upgrade 2000/2005 to 2008/R2: best practices with Satya SK Jayanty (MVP)

Cardiff - Wales

http://sqlserverfaq.com/?eid=295

 

SQL South West User Group

SQL Relay: High Availability with Tony Rogerson (MVP)

Exeter - Devon

http://sqlserverfaq.com/?eid=323

 

Southampton SQL Server User Group

SQL Relay: Justin Langford on SQL Server Denali and Nicholas Colebourn talking about designing tables for scale.

Southampton - Hampshire

http://sqlserverfaq.com/?eid=309

 

Cambridgeshire SQL Server User Group

SQL Relay: Inaugural Meeting of the Cambridge SQL Server User Group

Cambridge

http://sqlserverfaq.com/?eid=326

 

Essex SQL Server User Group

SQL Relay: Inaugural Meeting of the Essex SQL Server User Group with Chris Testa-O’Neill and Dave Ballantyne

Basildon - Essex

http://sqlserverfaq.com/?eid=327

Thursday 6th October

UK SQL Server User Group

SQL Relay: SQL Relay Finale with Itzik Ben Gan

London

http://sqlserverfaq.com/?eid=324

We are all looking forward to this with great excitement.

and I’ll be in Newcastle on the 6th of October presenting on SQL server to NEBytes.

If this isn’t enough then SQL Bits “Query cross the Mersey” 29th Sep – 1st October is also happening if you can spare a day or more out of the office and/or away from the family.

Semantic Search new for SQL Server Denali adds extra intelligence to the FullText search capability that has existed in SQL Server for several releases, by looking at the relationships between words and in and across each document.  For example you can find which documents are similar to a specified document, and this could help with matching candidates’ CVs to job adverts, or you can find the key words and phrases in a given set of documents. 

This statistical analysis information  is stored in a special database (which is supplied but not installed in SQL Server Denali) and is refreshed as part of the process that update the FullText indexes it depends on.

Once it is setup you have access to three tabular functions:

• SEMANTICKEYPHRASETABLE is used to find the key phrases in a table or the documents which contain a key phrase.
• SEMANTICSIMILARITYDETAILSTABLE find documents that are similar to a document you specify
• SEMANTICSIMILARITYDETAILSTABLE shows the key phrase that two specified documents have in common.

Note that in CTP3 only words not phrases work in these functions and that currently doesn’t understand word stems e.g. that cloudy and clouds are derivations of the term cloud and treats each as separate words.  This could be fixed as Denali gets nearer to release but there is no guarantee of this.

There are examples of this in the adventure works database which you can try, but I wanted to see what it would do with my FileTable of PowerPoint presentations I created in my last three blog posts, and have a short screen cast on this ….

and the 4 FileTable screencasts in this series are now linked (hyperlinks at the end of each screencast) so you can hop through the series.

Here are some notes if you want to install semantic search:

• You’ll need to have installed SQL Server Denali ctp3 with the FullText option. 
• Having done that you’ll need to install the SemanticLanguageDatabase.msi in the SQL install media (there are x86 and x64 versions). 
• This database then needs to be attached to SQL Server 
• It is then registered with this statement:

EXEC sp_fulltext_semantic_language_statistics_db @dbname = N'Semanticsdb'

•   You’ll need to add FullText indexing on the FileTables (or any other table)  the extra semantic_statistics clause:

CREATE FULLTEXT INDEX ON dbo.MyDecks
    (name 
        LANGUAGE 2057 
        STATISTICAL_SEMANTICS,
    file_stream 
        TYPE COLUMN file_type 
        LANGUAGE 2057
        STATISTICAL_SEMANTICS)
    KEY INDEX PK_MyDecks_5A5B77D56E0F5D8F
    ON DeepFat_Catalog
    WITH
        CHANGE_TRACKING AUTO,
        STOPLIST = SYSTEM;

In my part 3 of my series on FileTable I wanted to search the documents I had saved in parts 1 & 2. This is pretty straightforward, as Full text indexing has been in several versions of SQL Server and is SQL Server 2008 R2 the FullText indexes are part of the databases they refer to. 

What caught me out was that I sort of assumed that SQL Server Denali would be aware of the Office 2010 document types and it isn’t without doing some extra steps.  To explain:  If you store documents in SQL Server be that in the varbinary(max) column of a table or using the new FileStream or really new FileTable, SQL Server needs to understand what the document is so it can open it to index it.  This is done using ifilters and this works by having another column in the table that contains your varbinary(max)  data which refers to the relevant ifilter for each document, and note: you also need to have a single column, non computed, unique, clustered index on the table in order to create a FullText index.

There is a standard management view to see what ifilters are in operation:

select * from sys.fulltext_document_types

You can add more ifilters to the database server from Microsoft and other vendors e.g. from Firefox or Adobe for pdf.  I wanted the Office 2010 ifilters as I am using PowerPoint 2010 in my demos, but when I tried to install them they were already there, however they weren’t referenced by SQL Server.  So after installing an ifilter you need to go into SQL Server and run these commands:

EXEC sp_fulltext_service @action='load_os_resources', @value=1;
EXEC sp_fulltext_service 'restart_all_fdhosts';

to reference the new ifilters and restart the FulllText service. you’ll also then need to restart SQL Server itself.

Anyway back to my FileTable.  FileTables have a fixed schema but do meet the requirements for FullText indexing:

• file_stream is the varbinary(max) column that references the document itself.
• file_type contains the file extension of the file which will correspond to the ifilter.
• there is a unique index that meets the FullText criteria for a unique clustered  index.

So you can right click on a FileTable and run the FullText wizard as I have done in this short screen cast…

..or you can use T-SQL:

CREATE FULLTEXT CATALOG DeepFat_CATALOG AS DEFAULT;

CREATE FULLTEXT INDEX ON dbo.MyDecks
    (name LANGUAGE 2057,
    file_stream TYPE COLUMN file_type LANGUAGE 2057)
    KEY INDEX PK_MyDecks_5A5B77D56E0F5D8F
    ON DeepFat_Catalog
    WITH
        CHANGE_TRACKING AUTO,
        STOPLIST = SYSTEM;

Note: the language code 2057 is British English,  and  the index name will be different as it’s a random guid created by SQL Server for each FileTable

The FullText index will be populated automatically unless you specify otherwise, and I didn’t so I can use it immediately like this to test that it worked:

SELECT name
FROM dbo.MyDecks
WHERE
FREETEXT (file_stream, 'nist')
and creation_time > '2011/01/01';

and this returns the decks I created this year that have the term nist in (the National Institute for Science & Technology) . 

Next time I am going to hook up the new Semantic Search, capability in SQL Server Denali to my FileTable. In the meantime if you want to try this yourself then you’ll need SQL Server Denali ctp 3

In my last post I setup FileTable in SQL Server Denali, and now I ma ready to start using it. Just to recap before I do that FileTable is a way of storing files in what appears to be a normal set of folders under a UNC share but in such a way that  these files are under the control of SQL Server.  The instance is the share name, and then the database has a folder associated with it underneath which there is a folder associated with each FileTable as you can see see from this short screencast..

This all works because under the covers you are actually using Filestream - when you create a database that you want to use for FileTable the database must have a special Filestream file group, and this has a directory associated with it which is actually where the files reside on disk.  However as with previous version of SQL Server which have the Filestream feature, the files themselves have random GUID filenames and live in folders with GUID names (as you can see in the screencast).

Another interesting thing about FileTable is the interlay between relational command on the FileTable and what happens to the files – in my screencast I deleted all of the files that weren’t 2007 or later PowerPoint decks ( file type <> pptx) and while the rows in the table were instantly deleted, it took a couple of minutes for them to disappear from the FileTable folder.  However this doesn’t affect referential integrity because if I issued a backup command after the delete only the files that correspond to rows that remained in the table would be backed up.  This is exactly the same as happens with Filestream it’s just that in Filestream you weren’t exposed to the files (with the GUID filenames in the directory specified for the FileStream File Group).

So hopefully this has helped get your head around what FileTable is all about but how useful will it be? Although you can control the files in FileTable from SQL Server you can’t really interact with the contents, you can’t look inside those files. Actually you can and in my next screencast I’ll hook up full text search which has also been getting better and better in each release of SQL Server.

In the meantime if you want to try this yourself you’ll need to pull down SQL Server Denali ctp 3

A long time ago in a land far far away, somebody had the bright idea of using a database as part of a file system and the Windows Future System (WinFS) was born – but this is not that story.  However the idea and some of the team moved to the SQL Server engineering team and FileStream was the result in SQL Server 2008.   I have several posts on FileStream (here) and in essence it allows a win32 call to create read update and delete files form within the context of a SQL Server transaction.  This preserves the referential integrity between the data about the objects in the database and the objects themselves and backing up and restoring the database also picks up the objects as well.  The most interesting use case of FileStream is SQLRace developed by McLaren Electronics to monitor the performance of F1 cars not just for them but for the FIA and other manufacturers.

In the next release of SQL Server (Project Denali) there is a new feature built on top of this called FileTable.  This provides the ability to read write and update the FileStream objects directly for example from explorer via a UNC share.  The way this works is that you declare a share for the database instance and when you create or modify a database for use with FileTable you associate a directory name with it.  Underneath that directory there is then a directory for each FileTable.  FileTables are like ordinary database tables except that their schema is predefined and cannot be changed.

I have a short video here on exactly how to do this ..

In subsequent posts I’ll show you what you can do with FileTable data both from inside and outside of SQL Server.

Notes:

If want to try this yourself all you’ll need SQL Server Denali ctp 3

I created the database with this script..

CREATE DATABASE [Denali_FileTable]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Denali_FileTable', 

   FILENAME = N'C:\Program Files\Microsoft SQL Server\

   MSSQL11.MSSQLSERVER\MSSQL\DATA\Denali_FileTable.mdf' , 

   SIZE = 102400KB , FILEGROWTH = 1024KB ),
FILEGROUP FilestreamFG CONTAINS 

FILESTREAM 

   (name= FilestreamFG, FILENAME= 'C:\FileStremFG')
LOG ON 
   ( NAME = N'Denali_FileTable_log', 

    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.

    MSSQLSERVER\MSSQL\DATA\Denali_FileTable_log.ldf' , 

    SIZE = 1024KB , FILEGROWTH = 10%)
WITH FILESTREAM 

   ( NON_TRANSACTED_ACCESS = FULL, 

     DIRECTORY_NAME = N'FileTable')

at the time of writing books online has doesn’t mention the need to create a FileStream file group (in my case FilestreamFG) – and this will get changed as we get nearer to the release of Denali

The script to create my FileTable looks like this..

USE Denali_FileTable
CREATE TABLE MyDecks AS FileTable
    WITH ( 
          FileTable_Directory = 'My Decks',
          FileTable_Collate_Filename = database_default
         );

Before I can commit funds to a new project be it at work or at home I need to know I can afford it; specifically is there enough money in the budget and to find that out I need to run a report.  This is what’s meant by operational reporting and this distinction means that this report can be considered to be mission critical.  Whole rafts or reports could be considered to be mission critical and this begs the questions how do I make them highly available if they are so important.

The answer depends on how you are using reporting services and which version you have:

SQL Server 20005⁽¹⁾, 2008, 2008 R2 Reporting Services in standalone mode

In this type of deployment there are two moving parts, the web service and the databases and both can be configured to make reporting services highly available.The databases can be clustered but as there two of them:

• The metadata database (typically called ReportServer) that stores the reports, security, schedules etc..
• There is also a special temporary database called ReportServerTempDB for calculations and snapshotted reports.

mirroring won’t work properly, so clustering is your best option here.  The web service of SQL Server can then be network load balanced (aka scale out deployment) where multiple reporting services servers reference the same databases, giving you redundancy if one fails.  The complete how to on this is on TechNet, but note there is nothing actually in Reporting Services that does the Load balancing bit itself.

SQL Server Denali in standalone mode

As above but you cold use the new AlwaysOn feature to protect the databases in an availability Group.  This uses clustering services but not shared storage, to continuously replicate the databases multiple locations and keep them in synch.  I have a short post on AlwaysOn here and the definitive word is on TechNet

SQL Server 2008, 2008 R2 Reporting Services in SharePoint mode

Here there is an extra layer , SharePoint, but otherwise things remain the same, there’s still the databases to cluster and the reporting services servers can be load balanced.  However there is a raft of options to increase scalability and availability in Reporting Services Books Online:

Note that the reporting services add-in referred to here provides the report viewer web parts and integration components to make reports behave like any other document in SharePoint.

SQL Server Denali Reporting Services in SharePoint mode. 

What is different here is that Reporting Services is now just like any other application web services in SharePoint. so the  The difference is the way your configuration is done – it’s all done through SharePoint administration apart from selecting the installation options when you actually install SQL Server, as per my last post. 

So a quick 101 run through of your high availability options for reporting services, and just to be clear there is nothing in reporting services itself to support high availability (clustering or network load balancing), but the service can be configure to use these features.

(1) SQL Server 2005 is now in extended support, so unless you are paying for that service you aren’t really in a highly available environment as support is part of the equation

l forget sometimes that installing SQL Server isn’t that straightforward, in part because it is now so much more than the database engine, that was SQL Server 7 when I started.  I also think its important that you understand the choices you are making when installing it although many of these can be undone by reconfiguration later on.  I was wondering how to make this easier and I decided that my usual approach of recording an installation might not be that helpful so I have made a PowerPoint deck instead and stuck it on my SkyDrive account.

The deck covers four separate tasks to help you put together a complete Denali based  BI demo on one server:

Part 1.  the install everything to the default instance, so the database engine plus all the BI stuff, specifically:

• Analysis services in its new tabular mode
• Reporting Services in SharePoint integrated mode

Part 2. Install SharePoint and then install SQL Server Analysis Services in SharePoint integrated mode to its named instance of PowerPivot

Part 3. Another installation of Analysis Services this time in multidimensional (OLAP) mode to another instance of SQL Server.

Part 4. Setup reporting services in SharePoint integrated mode. This is done in a very different way to previous version of SQL Server; With Denali Reporting Services is an application service in SharePoint

I have included screenshots for every step of the process and annotated these in the notes at the bottom of each slide and you should end up with this in management studio..

plus having PowerPivot working in SharePoint as well as integrated reporting Services.

If you are trying that out for yourself then you’ll need:

• An evaluation copy of Windows Server 2008 R2 (this has sp1 included) for this if you haven’t got a TechNet subscription.
•  SQL Server Denali ctp 3
• An evaluation copy SharePoint 2010 if you haven’t got that TechNet subscription plus SharePoint 2010  service pack 1

Finally you might need some data , and reports to see all of this working and that’s on Codeplex.

Installing SQL Server can be tedious and boring, and now that we live in a virtual world surely there’s a better way to give developers, application managers and even tech savvy business users theSQL love without the putting your self through installation hell each time?

I imagine some of you have resorted to copying, renaming the server and then hacking around SQL Server to get it recognise the change of name, but there is a cleaner and supported way of doing this called image preparation. Essentially you do a pre install of the features of SQL Server you want, sysprep the machine and then use this as a template for as many installations as you need.  For each copy you then fire it up join it whatever domain and infrastructure you need and then run a script to complete the SQL Server installation. 

If you aren’t familiar with sysprep, it makes an installation of windows anonymous, it is no longer has a name, or more importantly an SSID ,( the way each machine is uniquely identified to a domain). So sysprep allows virtual machines to be copied and then each copy can be joined to the same domain.  

Creating a template like this makes even more sense if you use windows server core as it will take up less space, and the original template will need less patching, (as I covered in part 1 of this series).

In the accompanying video demo,

• I have installed SQL Server with the prepare image installation action:

SETUP

/QS

/ACTION=PrepareImage

/FEATURES=SQLENGINE,FULLTEXT

/INSTANCEID=MSSQLServer

/IAcceptSQLServerLicenseTerms

• sysprepped the virtual machine
• restarted it and joined it to  a domain (part 2 of this series covers this in more detail)
• finished the SQL Server installation with the complete image installation action:

SETUP

/QS

/ACTION=CompleteImage

/INSTANCEID=MSSQLServer

/INSTANCENAME=MSSQLServer

/SQLSVCACCOUNT=[domain\account]

/SQLSVCPASSWORD=[password]

/SQLSYSADMINACCOUNTS=[domain\account]

/AGTSVCACCOUNT="NT AUTHORITY\Network Service"

/IAcceptSQLServerLicenseTerms

/TCPENABLED=1

Image preparation does look like a lot of extra work the way I have shown it here,but in reality the process of configuring it post sysprep would be fully scripted, and handled by System Center, specifically Virtual Machine Manager, possibly in combination with Service Manager to handle the request for a new SQL Server and Orchestrator to handle the workflow. This final step is exactly what the private cloud is all about, and so I do want to make a final video to show this in action, but I want to wait until the System Center 2102 lineup is all released to beta in the autmn.

Notes:

• Sysprep doesn’t remove settings or patches, so the SQL Server firewall rule (to open TCP port 1433) I configured in part 2 is still there so I can connect to it remotely.
• You’ll need an evaluation copy of Windows Server 2008 R2 (this has sp1 included) for this if you haven’t got a TechNet subscription.
• You'll also need SQL Server Denali ctp 3
• The video is part 4 of a series and I have put links on them so you can watch them in sequence to get SQL Server working core as quickly as possible. However there will be accompanying posts and notes to help as well .

In my previous two posts in this series I have installed and configured Win Server Core, and now I am ready to install SQL Server.  There are a number of things to be aware of here:

• Only SQL Server Denali is supported for server core.
• SQL Server depends on the .Net framework
• SQL Server needs the the .Net 4 framework.
• You can only install SQL Server on Server Core using the command line.
• Only certain parts of SQL Server will run on Windows Server Core:
  • the database engine
  • full text search
  • analysis services
  • replication
  • client tools connectivity
  • integration services server

Turning that lot into a step by step guide works like this:

• Install server core as per part 1 of this series
• Install service pack 1 for Windows Server 2008 R2, if it’s not part already included in the media you used to install Server Core (known as a slipstream install).
• Install the .Net Framework 4.  There’s a special download for Serve Core for this.
• Join the server to you domain.
• Install SQL Server from the command line:

SETUP

/QS

/ACTION=Install

/FEATURES=SQLENGINE,FULLTEXT

/INSTANCENAME=MSSQLServer

/SQLSVCACCOUNT=[domain\account]

/SQLSVCPASSWORD=[password]

/SQLSYSADMINACCOUNTS=[domain\account]

/AGTSVCACCOUNT=”NT AUTHORITY\Network Service”

/IAcceptSQLServerLicenseTerms

/TCPENABLED=1

Note:  the last switch enables TCP for you for remote access to SQ Server

added 22 August 2011: SQL Server Denali will add in the .Net features automatically if they aren't already there, although in the video I did all this up front in part 2

• Open the firewall to allow you to connect to SQL server remotely..

netsh [enter]

advfirewall firewall [enter]

add rule name=”SQL Server default port 1433” dir=in action=allow protocol=TCP localport=1433 [enter]

Here’s my video of doing all of that

Notes:

• You’ll need an evaluation copy of Windows Server 2008 R2  (this has sp1 included) for this if you haven’t got a TechNet subscription.
• Service Pack 1 for Windows server 2008 R2 is available here if you need it.
• .Net Framework 4 for Server Core is available here 
• You'll also need SQL Server Denali ctp 3
• The video is part 3 of a series and I have put links on them so you can watch them in sequence to get SQL Server working core as quickly as possible. However there will be accompanying posts and notes to help as well .

Windows Server Core is a lightweight installation of any edition of Windows Server, and because it is so lightweight initial setup can be a bit tricky, so here’s what you need to know:

• Adding roles & features. There’s no Server Manager in Server Core and in any case you might want to do some configuration from the command line.  The modern (new for Windows 7 way to do this) is with DISM (Disk Image Service Management) which can also be used to do this to a virtual machine without turning it on.  This command adds in everything you need for SQL Server:

DISM /online /enable-feature
/featurename:NetFx2-ServerCore
/featurename:ServerCore-WOW64
/featurename:NetFx2-ServerCore
/featurename:NetFx2-ServerCore-WOW64
/featurename:NetFx3-ServerCore
/featurename:NetFx3-ServerCore-WOW64
/featurename:MicrosoftWindowsPowerShell

• Joining a domain.  NetDom used to be the way to do that but now there are PowerShell commands you can use.  Also there is a built in shell SConfig which enable you to do this:

• General configuration.  There’s a Core Configurator application on Codeplex which is a simple GUI fronting lots of clever PowerShell and so doesn’t add to the weight of Server core but will turn on the .Net framework and enable PowerShell as these are needed to make this work.

• Remote Management.  This can be done the hard way in netsh, or in either of the utilities above.  I couldn’t see a way of just opening a port with these tools, so if you want to install SQWL Server and access it remotely you’ll need to open a port for it (the default of 1433 in my case).

netsh [enter]

advfirewall firewall [enter]

add rule name=”SQL Server default port 1433” dir=in action=allow protocol=TCP localport=1433 [enter]

I have gone over some of this in this 5 minute video…

Notes:

• You’ll need an evaluation copy of Windows Server 2008 R2 for this if you haven’t got a TechNet subscription.
• I used the The Core Configurator on Codeplex 
• The video is part 2 of a series and I have put links on it so you can watch navigate through them as you need. However there will be accompanying posts and notes to help as well

A while ago I got SQL Server (only the database engine) running on Server core and then found out that this wasn’t going to  be a supported scenario for SQL Server 2008 R2.  However with the launch of SQL Server Denali this will be supported scenario but now it’s a little more complicated to do.

Before I go into that what is Windows Server Core and why does this evangelist consider it important?

It is a lightweight version of windows with the bare minimum of components needed to run infrastructure services like domains, file and print, and IIS.  Originally only asp sites could be supported but with the launch of Windows Server 2008 R2 there was enough of the .Net 3.5 framework in there to support asp.Net websites and to run SQL Server.  In fact it is so lightweight there are only a few interfaces in there:

• Registry Editor
• Notepad
• Task Manager
• Command line
• PowerShell (Note:you have to do some post setup installation to enable this)

While this cripples the server if you are trying to work on it directly it gives a number of advantages:

• Maintenance. Less than half of the patches needed for Windows Server will be applicable to a server core installation
• Availability. Windows Server 2008 R2 is the most secure Microsoft operating system anyway but, less operating system means less to go wrong and less to attack, for example there’s no browser in here.
• Footprint.  Server Core will happily run in 256Mb and takes less disk space than it’s full fat sibling so you can pack more virtual machines like this onto a physical server.   

Server Core is also pretty fast to install just over 5 minutes from a clean virtual machine on my admittedly awesome Dell Precision M6500 and I hope this equally short video (under 4 mins) will help if you want to try it yourself:

Notes:

• You’ll need an evaluation copy of Windows Server 2008 R2 for this if you haven’t got a TechNet subscription.
• The video is part 1 of  a series and I have put links on them so you can watch them in sequence to get SQL Server working core as quickly as possible.  However there will be accompanying posts and notes to help as well

Getting time, and resources to stay current with technology is always hard for IT professionals even when that training applies directly to your role.  If you are thinking of cross training because for whatever reason you can’t see a future in doing what you are currently doing then you’re probably going to end up investing your own time and money to reskill.  It can also be hard to work out what to learn about, and if it’s the Microsoft stack you’re interested in there’s just so much stuff it can be hard to know where to begin. 

The Microsoft Virtual Academy is designed to help with this by providing introductory training in a variety of cloud related technologies, but is it any good?  I wasn’t totally sure so I decided to give the course in the private cloud track a spin and see if they are any good.

The private cloud track has four separate courses:

• Enhancing you Business and Career with the private cloud
• Microsoft virtualization for Vmware professionals  - the platform
• Microsoft virtualization for Vmware professionals - management
• Planning building and managing a private-cloud

Each course is made up of a number of modules and  each module has various videos and technical articles in it plus an assessment test.  The videos are often on other Microsoft channels such as TechNet Edge, and ShowCase and you get points by following the links , and points for completing the assessment and all module in the course.

Note: don’t watch all the videos by clicking in the TechNet Edge portal, go back to MVA and click on the links there to get credit for each module

So I have registered (you’ll need a LiveID ) logged in and have got going..

To be honest I cheated on the Success with the Private Cloud course and tested out as I am pretty comfortable with our cloud messaging and was able to get good scores without watching the videos again..

The other three courses are well worth watching in their entirety even though that’s going to tack a day to do. There’s lots of anecdotal stories from the field along with the high level stuff and the demos really get the concepts across and there’s not too much PowerPoint in there either.  When it came to the assessment questions the level jumped into considerable detail- for example on the Hyper-V deployment module in the Hyper-V for Vmware professionals – the platform course I confess that I did have to rely on what I just learned to get past some of the networking questions.

Finally There are several other track in MVA , around Azure , Office 365 etc. and that  content is continually being added to the MVA platform and I will be looking forward to deeper exploration of the new cloud stuff coming in the next wave of System Center (2012) that is now in beta.

SQL Bits 9, Adelphi Hotel Liverpool 29th September to 1 October 2011, what else do you need to know?

Well possibly a few things if you are now reading the second line of this post or you haven’t been to them before..

1. It’s the biggest and best SQL event in Europe, I know this because we get people coming from around europe to it , not to mention the Microsoft engineers who come over from Seattle to attend and present.
2. It’s actually several different types of event all tied together
• a training day of deep dives which you have to pay for
• a paid for conference with subject matter experts including MVPs and Microsoft technical experts form the engineering team and SQL CAT, sharing their best practices and experiences.
• a free community day where anyone can post an abstract for a session and then attendees vote on which ones get accepted and are part of the day.  It’s split into 4 tracks DBA, Developer, BI and Denali so the other challenge you can face is which session to go to.
3. It’s a massive networking opportunity,  you might want some technical help you might be in the market for a career move or you need a good DBA yourself. 
4. There’s some interesting sponsors there including all the major tools vendors (Attunity , Idera, Quest, SQL Sentry) so you can contrast and compare in one visit.  Experts like Coeo also attend plus the game changing hardware specialists FusionIO with their solid state storage solutions, that almost make it pointless to worry about any other kind of performance tuning.
5. We’ll be there, when I say we, I mean some hardcore technical experts form Microsoft from our customer and support teams, who also have share a passion for all things SQL Server, plus of course this evangelist, who still likes to keep his hand in.

I could mention the good food, prizes, games etc. but I have hopefully given you incentive enough to be there if you possibly can.

One of the best parts of my job is helping people, and although I am not a Microsoft support guy, I will respond if I can to requests on my blog , twitter etc. a particularly if it’s from people new to a particular technology.   This is partly out of a recognition that we’re all experts in something and even when it comes to the IT department not everyone is deep into SQL Server, System Center or Windows.  SQL Server actually presents it’s own problems as  it often comes with a business application which hasn’t ben procured through IT, and somewhat naturally when the business asks for help IT will push back.  However application like this can be mission critical and in one case (I am not allowed to name names) this resulted in a user running into trouble with a 2TB (yes that is 2 Terrabytes) database filling up his desktop machine.

It was clear from the tone of the e-mail that although this chap is highly technical and has some basic knowledge of IT he hadn’t really done anything with SQL Server before, and was feeling his way.  His database turned out to be so large because it was mostly Filestream, a new data type in SQL Server 2008 and later for storing large unstructured data files in such a way as to keep them inside SQL Server’s control while still essentially being separate files capable of being accessed individually through the win32api.  So if you looked the disk you would see the files for the database itself, the log file and then a folder with these Filestream files in in a set of SQL Server generated folders and file names i.e. they won’t have the same names as when they were added to the database.

There wasn’t time or space on this machine to backup this database but fortunately the user was using a test database to test how to move it and reuse it elsewhere.  I recommended detach –> copy –> attach without realising that Filestream doesn’t like that if you just do it as is,  the attach left the database in an unusable state as it was still looking for the Filestream data in its original location.  Fortunately running detach form T-SQL:

USE [master]
GO
ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'MyDatabase'
GO

where MyDatabase is the name of  my database.  If you are new to SQL Server those file will be in something like

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\

and that’s part of the problem, a default install of SQL Server will drop these data files on the C Drive along with the SQL Server program and the operating system. Anyway you would then copy the database files to a new larger hard disk and then run this..

USE [master]
GO
CREATE DATABASE [MyDatabase] ON
( FILENAME = N'D:\DATA\MyDatabase.mdf' ),
( FILENAME = N'D:\\DATA\MyDatabase_log.ldf' ),
FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM DEFAULT
( NAME = N'MyDatabase_FSData', FILENAME = N'D:\DATA\FileStreamData' )
FOR ATTACH
GO

where D:\data  is where you have copied the files to in my case D:\Data

The next problem we hit that his new server came back with an error,

Msg 1813, Level 16, State 2, Line 1

Could not open new database 'MyDatabase'. CREATE DATABASE is aborted.

Msg 948, Level 20, State 1, Line 1

The database 'MyDatabase' cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported.

This is pretty simple – the new server was running an older version of SQL Server and while you can take on old database detach it copy and attach it to a newer server (which is a supported way of upgrading a database)  you can’t go backward to an older version.  In this case the original database is SQL Server 2008 R2 and the second server was running SQL Server 2008.  Given this is an end user scenario it’s not surprising that he had differing version around, and my final tip is to run this to find out what you’ve got on a server by server basis:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The version is 10 for SQL Server 2008 , 10.5… for SQL Server 2008 R2. 

If you have lots of servers to perform SQL Server discovery on then you’ll be much better off using the free Microsoft Assessment & Planning Toolkit, which will also return operating system and server information plus a host of other information as you need.

So imminent disaster averted, user fairly happy, but all I have done is put some gaffer tape over the problem in reality this data need to be migrated to proper server so that as the database grows it will perform and be available to the whole team and hopefully be properly protected by the IT guys.

With the launch of SQL Server 2008 R2 and SQL Server Denali ctp3 there has been ever more BI functionality built in but nothing has been mentioned about the strange world of data mining. Data mining has been in SQL Server since analysis services was launched but I have rarely seen it being used - it doesn’t seem to have entered mainstream consciousness in the way that some of the cool reporting front end tools have possibly because it smacks a little of black magic, or is seen as unnecessary or hard. 

So here’s a quick post to get you thinking and hopefully encourage you to take it for a spin.

What exactly is Data Mining? 

The simple answer is the ability to derive insights from your data that you didn’t know were there.  Classic examples of use are customers who bought this also bought that (what is called basket analysis in the retail world) and trying to understand what factors influence a purchase.

How does it work?

There are a number of approaches, possibly the most famous being neural networking.  In fact there are six different techniques built into analysis services each designed to do a particular job:

• Association  is used for that basket analysis I mention and recommending additional items customers may want to purchase.
• Clustering  groups such things as customers by detecting what is not obvious form casual observation.  You may have heard a story a few years ago of the banks segregating customers into cherries (good customers) through to lemons (bad customers) and this might been done better using clustering rather than a trivial exercise around what revenue they bring in.
• Decision Trees try and predict the outcome of an attribute by understanding what factors determine the value of that attribute. The way the prediction works depends on what is being predicted and whether this is discrete (values such as yes or no, red green or blue and so on) or continuous (e.g. a number like value or price).  So this could be used to work out what factors make a up a decision to purchase e.g. age gender, income, homeowner and so on.
• Linear Regression.  You may remember this one form matches at school, the process of trying to draw a line through a series of dots to best represent the trend of those dots on a graph. This is used by the decision trees algorithm where the predicted column is continuous.
• Naive Bayes  is the method for determine which customers would most likely respond to a marketing campaign, the naive term means that no relationship is assumed between any of the factors affecting the outcome.
• Logistic Regression. Every week you’ll hear something like “women drivers over 40  are 50% less likely to have a car accident” and this would be the algorithm that would be used to discover that relationship. It can also be used to discover which are the most important factors in determining an outcome.
• Sequence Clustering is similar to clustering but the data contains a sequence of events and this enables you to explore such things as a users navigation of a web site (known as click stream analysis).
• Time Series is used for forecasting a continuous value such as sales  or profit.
• Neural Network is the non of the above option for really complicated input data.  The mechanism for determining the outcome mimics the way neurons work in the brain and doesn’t actually show you how its results are derived at.  Text mining is one example that uses this technique.

In all of these techniques you identify a sample set of data which has the values of the object you are trying to predict, from which you create a model.  You then apply this model to live data to do your prediction and analysis. 

How do I do this?

There are three ways of creating models:

1.  You can create mining models in BI development studio as part of an analysis services project in SQL Server 2008 R2 or in the beta of the new version Project Denali and there are examples mining models in the sample adventure works analysis service project from Codeplex:

2.  You can code a model directly in Data Mining Expressions (DMX) which might be appropriate for embedding models into an application such as an ecommerce site.

3. There’s an Excel add-in for data mining :

This works by taking the data in a spreadsheet and then posting it to an instance of analysis services for processing and then returning the results back to Excel.  Originally designed for Excel 2007 it does work in Excel 2010 but only for 32bit installations, and while it can’t use data in the PowerPivot window in  PowerPivot for Excel it can consume data in Excel pivot tables derived from PowerPivot data. Note that when I mention analysis services here, I mean classic analysis services in olap mode not the new shiny tabular based analysis services that also exists in SQL Server Denali.

Why Bother?

First of all you are probably licensed for all of this now, you’ll have SQL Server standard or higher around somewhere you can use this as the mining engine and a copy of Excel for the front end (the excel add-in for data mining isn’t a separate license). However this might all seem a bit abstract and you might have a hard time convincing yourself never mind your users that this is worth the effort. One idea I have is  what if you took the data out of some of the data you have access to see how this stuff works, for example data from System Center, or whatever data centre management tools you have (dare I mention VSphere?) your helpdesk system or asset management tools to get insight on what is really going on.  This might be a useful project in it’s own right but as the data is not sensitive you can share it as an example of what’s possible and possibly impress your manager as well as the business owners.

What on earth is consumerisation and what has it got to do with Business Intelligence?  Consumerisation is the recognition that our users are tech savvy and want to have access to the tools and way of working that they have outside of work.  Self Service is the result of applying these ideas to BI:

Our users know what they want

• They want it now;
• they don’t want the IT department getting in the way, and they want to see the results of their work as quickly as possible
• They want to share there work in the way they share their digital lives with their friends and family

My way of looking at the end user BI tools look today is …

the users thinks it’s all green and only the BI IT community worry about the red stuff.  On the other hand the more traditional approach to BI might be summed up as..

It’s strategic but can be hard to change and while no one can deny that the MDX query language is very powerful it isn’t easy for business users or for IT (outside the BI industry) to understand.   So a third way is need and that would look like this..

i.e. Take the work done in the self service tools and then scale it up put in control and security.  Having done that we then want to put it into production and then hook up all the end users familiar tools to interrogate it. 

In Denali ctp3 this is now doable using the BI Sematic Model (BISM):

• Your users create PowerPivots in Excel
• You can then open that in the new BI Development Studio in Denali as a .. project
• Add in the security roles and if necessary partition it clean it and refine it.  The original business user who designed the PowerPivot can be involved in this process and they will recognise what’s going on as the designer is virtually the same as it is in Excel.
• Deploy to a table based instance of analysis services

I would stress that this approach is in addition to the traditional approach that has been in SQL Server with analysis services since 2005, and you can verify that and try this approach by pulling down SQL Server Denali ctp 3 , SharePoint 2010 with sp1  and the matching sample databases, reports and tutorials for BISM on codeplex.

Some sort of semantic model is needed in every BI solution but what is it and why do I need one? 

It’s a view of the data store(s) you want to work on for business intelligence which adds additional information that can be stored in the individual tables themselves.  For example how are the tables joined together, friendly names for the  columns in those tables that make sense, and security to define which parts of the data each users can work with .   This semantic model is then used as the source for the reports and analytics that comprise a BI solution.  

This layer of abstraction may seem unnecessary in some cases for example if there is only one source of data such as a data warehouse which already has this functionality all in place. However the sematic model in many solutions including Microsoft  provides the means to group and sum data across hierarchies such as summing by year down to month down to day, or from enterprise to division to department. 

The other advantage of this approach is that it can mask changes to the underlying schema form the reports that depend on it; the report will reference an object in the model which  can have it’s definition changed to reflect the change without having to touch the report.  Multiply this in a BI solution that has hundreds of reports and this can be a reason to use the model in its own right. Models can also span different sources of data e.g. database like SQL Server, Oracle DB2, to spreadsheets flat files and more recently data feeds from internal web services and the cloud.

Up until now Microsoft has had two semantic models in place one to reflect the relational world in reporting services, the report model and the Unified Dimensional Model (UDM) built into analysis services to model the complexities of olap cubes.  The problem I have with this is that you can end up having to do everything twice; setup security, add business logic and so  on in each model making change control difficult especially as by their very nature they don’t work in the same way.  To get around this one workaround I have used before is to lay a report model with no logic in it at all directly on top  of  a UDM and then the reports inherit the same security hierarchies as exist in the analysis services world.  That is a bit of fudge to be honest and so I am pleased to see the problem is going to go away with the introduction of a new model the BI Semantic Model in SQL Server Denali ctp3.  

Actually BISM v1 is in SQL Server 2008R2 as part of PowerPivot as it is the mechanism to describe how the sheets (tables) in the PowerPivot were related and also where the business logic you specified (in DAX) was stored.  as I mentioned in my last post,  PowerPivot in SQL Server Denali ctp3 now lets you use a table design view to create these relationships add calculations and now allows you to also create hierarchies, either in the PowerPivot viewer in Excel..

..or in the BI Development studio.

You can also see that some of the columns are greyed out, which indicates I have hidden them.

Once you have created a BISM, you can then use it much as you would analysis services:

• in SharePoint Dashboard designer to create scorecards and  dashboards
• in reporting services as a source for reports, and as a source for the new Crescent reporting tool
• in any Excel version as a source of a pivot table (without using the PowerPivot add-in)

So why all this change apart from the need to just have one model?

For me the key thing that BISM provides is that it enables BI professionals to work with end users to scale up the self service tactical BI that they have built in PowerPivot. I think this is important because if the business have gone to the trouble of building a tactical BI solution it should be protected and possibly scaled.

This is available to try now you’ll need SQL Server Denali ctp 3 and SharePoint 2010 with sp1 installed and there’s matching sample databases, reports and tutorials for BISM on codeplex.

I have now managed to install analysis services (SSAS) in SQL Server Denali ctp3 three times to reflect the three installation options that now exist ..

• The top instance is the table based installation that’s completely new for SQL Server Denali
• Then I have olap based SSAS instance that I have deliberately called OLAP.  This is essentially the same as was introduced in SQL Server 2005 and performance enhanced in SQL Server 2008. It has had some minor improvements in SQL Server Denali, specifically scalable string storage to allow more than 4G for storing strings in dimension tables, but will support existing tools such as the data mining add-ins for Excel (Note: you’ll need the SQL Server 2008 add-in and this must be installed to 32-bit excel).
• Finally I have a PowerPivot for SharePoint integration instance (the instance name is hard coded as PowerPivot during the installation), and as you can see I have one PowerPivot in SharePoint

Anyway back to table based or VertiPaq analysis services.  Essentially this is server side PowerPivot:

• Is uses the same query language, DAX
• It uses the same VertiPaq column based engine to cache data, and this can be likened to MOLAP storage in SSAS.
• you can import a PowerPivot into BI Development studio (BIDS) and then deploy an instance of tables based SSAS.
• You’ll see that the design experience in BIDS is very similar to what there is in PowerPivot for Excel today with SQL Server 2008 R2. 

However like PowerPivot itself in SQL Server Denali there are also improvements in BIDS such as a visual view of the relationship between the tables ..

What you are actually looking at here is the new BI Semantic Model (BISM).  This is replacing the report model in previous versions of SQL Server and will also replace the unified dimension model (UDM) that underpins analysis services, so that there is a simple three layer structure to do BI:

• Data sources, be that structured data that exists in house or Odata feeds from the cloud like the Azure data market
• BISM to add business logic, and store how the data sets are joined and where they came from.
• Analysis & Reporting tools both from Microsoft and from third parties to suite different users and differing needs.  For example I can point straight Excel (with no add-ins)  directly at Tabular based SSAS model like this or the Dashboard Designer in SharePoint and both will treat this as a conventional olap cube. 

At this point I probably need to do a series of posts on why BISM is important as well as contrast and comparison posts on Vertipaq v OLAP analysis services, so I’ll stop here. In the meantime if you want to try any of this, then SQL Server Denali ctp 3 is available for download here and there’s matching sample databases, reports and tutorials for it on codeplex.

The installation experience for SQL Server Denali is very similar to SQL Server 2008 R2 but there are some important differences because of the new services.  I would also add that this is the install of the beta and I daresay I’ll need to do another post when it is released. The options for the database itself are largely unchanged, the biggest differences are for reporting and analysis services, because of the cool new things you can do now and in this post I want to look specifically at reporting services.

Reporting Services can still be installed natively, and I couldn’t see that this had changed at all even to the point that Report Builder 3 is supplied to design the reports.  If you have seen anything around Denali you may well have heard of project Crescent, but there are two other key things to know about this new version of reporting services in SharePoint mode:

• End users can setup alerts when numbers on reports go out of band.
• Reporting service is now a part of SharePoint, (it requires SharePoint 2010 sp1) and is configured through PowerShell. This has been done to remove the performance degradation in previous versions of reporting services when in integrated mode which although reduced in SQL Server 2008 R2 also mean you have to configure SharePoint and the reporting services service using it’s own configuration tool.

In order to setup Reporting Services in SharePoint:

• Check this dialog in the installation of SQL Server :

• Follow this guide to configure SharePoint for Reporting Service
• Having done that you can set up the alerting functionality using this link. Note this uses SQL Agent so make sure you have that running. 
• You’ll also want to setup e-mail in reporting services by defining your smtp server once the reporting service is setup.  This can be done from SharePoint administration..

Once this has been setup you can then run any report that has stored credentials (i.e. its not running against the user who’s running the report) and set up an alert..

and then complete this template to be alerted on any condition you can describe here..

If you want to try any of this, then SQL Server Denali ctp 3 is available for download here and there’s matching  sample databases , reports and tutorials for it on codeplex.

I have spent the last couple of days setting up a demo environment for SQL Server “Denali”, and I thought it might be useful to share some of my experiences over the next few days. Before I get into that a short post about the environment I have which will comprise of 7 x virtual machines (VMs) running on my Dell Precision M6500 aka the Orange Data Centre:

Those VMs work as follows:

• DC -  my domain controller, also running DHCP and DNS
• BIClient - a windows 7 VM  with all of the SQL Server 2008 R2 tools on such as BI development Studio(BIDS), SQL Server Management Studio (SSMS), PowerPivot, and SharePoint Dashboard Designer.
• Tangerine - a second Windows 7 VM for  Denali BI, BIDS, SSMS, PowerPivot, SharePoint Dashboard Designer as well as Visual Studio 2010 Ultimate
• BI2010 - a SQL Server 2008 R2 BI server with SharePoint and all of the BI tools like PerformancePoint, PowerPivot, Reporting Services, Analysis Services and FAST search.
• Denali 1  - a SQL Server Denali BI Server with everything BI in Denali plus SharePoint 2010 sp1, for Crescent, PowerPivot, BISM  and the new integrated Reporting Services
• Denali 2 & Denali 3  - two more SQL Server Denali VMs to show the high availability ‘Always On’ capability.

But why not have a VM for SQL Server 2008 R2 and another for Denali?

Speed and Flexibility:

Speed If any of you have tried to use or create an “all in one” VM, complete with a domain controller, Silverlight,  SQL Client tools installed it won’t work too well even if you throw 16GB RAM and all your cores at it.  You could argue my approach means multiple copies of the operating system (one in each VM) but windows 7 is designed for client stuff like Silverlight and Office , while the domain controller does slow down SQL Server fi they are co-located.I could take this a stage further and put SQL Server in its own VM but that would really only benefit lots of concurrent usage.

Flexibility   I have to use my demo laptop for other demos e.g. System Center which requires another 6 VMs (virtual machine manger, operations manager, orchestrator and 2 for service manager).  These are joined to the same domain as my BI environment so I can use BI to show System Center or manage BI with System Center,  although even my 16Gb SSD only Dell can’t run all of this even with dynamic memory turned on in Windows Server 2008 R2 sp1 Hyper-V.

I wanted to mention this because I see a lot of monolithic VMs which end up being huge and can be very difficult to manage and basically don’t run as well.  Of course you might choose not to believe a random Microsoft evangelist but if you look at this graph:

you can see the number of supported users goes up as we add more VMs to the same hardware while response time (the dotted line) drops slightly  (Research by the Enterprise Services Group).

However for my demo I am sort of breaking my own guidance as I will just have the one VM for SharePoint and SQL Server Denali.  Before I  go through my experience of doing that (some of which is whirring away as I type this) I want to use my next post to describe some of the moving parts of SQL Server Denali ctp3 which is available for download here

Many of us would associate the the term critical with the Register, so when I was asked by Tim Phillips their broadcast and commercial editor to join a discussion titled “Going Critical” I was a little nervous.  The title refers to some Register commissioned research by Andrew Buss at Freeform Dynamics on what Register subscribers are doing about mission critical application in a virtual world.  This indicates that now the initial rush to virtualise all the simple stuff like file and print and simple applications that there is shift in two directions:

• A move to embrace the sorts of techniques associated with private clouds, such as process management, resource pooling, and building scalability.
• Starting to virtualise mission critical applications.

Also on the panel was Alun Rogers one of the founders of Microsoft gold partner Risual, as he’s in the real world making all this actually happen.  

The webcast was recorded and is available on the Register whitepapers website.

I contributed a couple of slides to the deck based on some other research by the Enterprise Server Group on Hyper-V performance with the sort of workloads we were discussing.The other resource I always mention in this conversations is the free Microsoft Assessment and Planning Toolkit (MAPT) which you should use to plan and baseline any  major change to your infrastructure such as physical or virtual to virtual migrations  and version upgrades.