Martyn

Martyn Bullerwell's Blog

SQL 2008 - what's changed, and what's new?

As I am sure you can all Guess SQL 2008 is the next release of SQL Server, and does not redesign the wheel, and is more an enhancement to 2005.   Microsoft claim that they have made it more secure, more reliable, more scalable etc... etc... 

Business Intelligence:

  • Analysis Scale and Performance: Allows more complex computations and aggregations and brings new cube design tools.
  • Block Computations: Provides a significant improvement in processing performance enabling users to increase the depth of their hierarchies and complexity of the computations.
  • Writeback: New MOLAP enabled writeback capabilities in SQL Server 2008 Analysis Services removes the need to query ROLAP partitions. This provides users with enhanced writeback scenarios from within analytical applications without sacrificing the traditional OLAP performance.
  • Enterprise Reporting Engine: Reports can easily be delivered both internally and externally with easier deployment and configuration.
  • Internet Report Deployment: Allows deploying of reports over the internet. 
  • Report Builder Enhancements: Easily build ad-hoc and author reports with any structure through Report Designer.
  • Report Server Application Embedding: Report Server application embedding enables the URLs in reports and subscriptions to point back to frontend applications.
  • Microsoft Office Integration: SQL Server 2008 provides new Word rendering that enables users to consume reports directly from within Microsoft Office Word. In addition, the existing Excel renderer has been greatly enhanced to accommodate the support of features like nested data regions, sub-reports as well as merged cell improvements. This lets users maintain layout fidelity and improves the overall consumption of reports from Microsoft Office applications.

Development:

  • Language Integrated Query (LINQ) See previos Blog Entries on LINQ: Enable developers to issue queries against data using a managed programming language such as C# or VB.NET, instead of SQL statements. Enable seamless, strongly-typed, set-oriented queries written in.NET languages to run against ADO.Net (LINQ to SQL), ADO.Net DataSets (LINQ to DataSets), the ADO.NET Entity Framework (LINQ to Entities) and to the Entity Data Service Mapping provider. Use the new LINQ to SQL provider that enables developers to use LINQ directly on SQL Server 2008 tables and columns.
  • ADO.Net Object Services: The Object services layer of ADO.NET enables the materialization, change tracking, and persistence of data as CLR objects. Developers using the ADO.NET framework can program against a database using CLR objects that are managed by ADO.NET. SQL Server 2008 introduces more efficient, optimized support that improves performance and simplifies development.
  • DATE/TIME: SQL Server 2008 introduces new date and time data types:
    • DATE – a date only type
    • TIME – a time only type
    • DATETIMEOFFSET – a time zone aware datetime type
    • DATETIME2 – a datetime type w/ larger fractional seconds and year range than the existing DATETIME type
  • HIERARCHY ID: Enable database applications to model tree structures in a more efficient way than currently possible. New system type, HierarchyId, can store values that represent nodes in a hierarchy tree. This new type will be implemented as a CLR UDT, and will expose several efficient and useful built-in methods for creating and operating on hierarchy nodes with a flexible programming model.
  • FILESTREAM Data: Allow large binary data to be stored directly in an NTFS file system while preserving an integral part of the database and maintaining transactional consistency. Enable the scale-out of large binary data traditionally managed by the database to be stored outside the database on more cost-effective storage without comprise.
  • Integrated Full Text Search: The Integrated Full Text search makes the transition between Text Search and relational data seamless while enabling users to use the Text Indexes to perform high-speed text searches on large text columns.
  • Sparse Columns: NULL data consumes no physical space which provides a highly efficient way of managing empty data in a database. For example, Sparse Columns allows object models that typically have numerous null values to be stored in a SQL Server 2005 database without experiencing large space costs.
  • Large User Defined Types: SQL Server 2008 eliminates the 8KB limit for User Defined Types (UDTs) allowing users to dramatically expand the size of their UDTs.
  • Spatial Data Types: Build spatial capabilities into your applications by using the support for spatial data.
    • Implement Round Earth solutions with the geography data type. Use latitude and longitude coordinates to define areas on the Earth's surface.
    • Implement Flat Earth solutions with the geometry data type. Store polygons, points, and lines that are associated with projected planar surfaces and naturally planar data, such as interior spaces

Security:

  • Transparent Data Encryption: Enable encryption of an entire database, data files or log files, without the need for application changes.
  • Extensible Key Management: Comprehensive solution for encryption and key management. Supports third-party key management and HSM products.
  • Auditing: Create and manage auditing via DDL enabling answers common questions such as "What data was retrieved?"
  • Built-in Forms Authentication: Built-in forms authentication enables users to easily switch between Windows and Forms.

Redundancy and Performance:

  • Enhanced Database Mirroring: Eliminates the need to specify the failover partner which enables existing applications to take advantage of mirroring.
  • Resource Governor: Provide a consistent and predictable response to end users with the introduction of Resource Governor.
  • Predictable Query Performance: Enable greater query performance stability and predictability by providing functionality to lock down query plans.
  • Data Compression: Enable data to be stored more effectively and reduce the storage requirements for your data. Data Compression also provides significant performance improvements for large I/O bound workloads like data warehousing.
  • Hot Add CPU: Dynamically scale a database on demand by allowing CPU resources to be added.
  • Declarative Management Framework (DMF):  Use DMF with SQL Server Management Studio to create policies that manage entities on the server, such as the instance of SQL Server, databases, and other SQL Server objects.
  • Performance Data Collection: More extensive performance data collection, a new centralized data repository for storing performance data and new tools for reporting and monitoring.
  • Backup Compression: Keeping disk-based backups online is expensive and time consuming. With SQL Server 2008 backup compression, less storage is required to keep backups online and backups run significantly faster since less disk I/O is required.
  • Partitioned Table Parallelism: Partitions enable organizations to manage large growing tables more effectively by transparently breaking them into manageable blocks of data. SQL Server 2008 builds on the advances of partitioning in SQL Server 2005 by improving the performance on large partitioned tables.
  • Star Join Query Optimizations: SQL Server 2008 provides improved query performance for common data warehouse scenarios. Star Join Query optimizations reduce query response time by recognizing data warehouse join patterns.
  • Grouping Sets: Grouping Sets is an extension to the GROUP BY clause that lets users define multiple groupings in the same query. Grouping Sets produces a single result set that is equivalent to a UNION ALL of differently grouped rows making aggregation querying and reporting easier and faster.
  • Change Data Capture: With Change Data Capture, changes are captured and placed in change tables. It captures complete content of changes and maintains cross table consistency and even works across schema changes. This enables organizations to integrate the latest information into the data warehouse.
  • MERGE SQL Statement: With the introduction of the MERGE SQL Statement, developers can more effectively handle common data warehousing scenarios like checking whether a row exists and then executing an insert or update.
  • SQL Server Integration Services (SSIS) Pipeline Improvements: Data Integration packages can now scale more effectively making use of available resources and managing the largest enterprise scale workloads. The new design improves the scalability of runtime into multiple processors.
  • SQL Server Integration Services (SSIS) Persistent Lookups: The need to perform lookups is one of the most common ETL operations. This is especially prevalent in data warehousing where fact records need to use lookups to transform business keys to their corresponding surrogates. SSIS increases the performance of lookups to support the largest tables.

Install Performancepoint Server on SQL Server 2008

Testing the latest technology on the latest platforms is always very important to us, in order to ensure we are up to date, give our clients an unbiased view of what's out there and what's coming up, and how it will affect them.  We have been involved with PerformancePoint for sometime now and have had good success, however with the impending release of SQL Server 2008 and Windows Server 2008, we thought it time to get up to date with PPS on a Server 2008 box, running SQL 2008. 

With Windows Server 2008 in RC1 and SQL Server 2008 in RC1, we know we are now close to testing the fully fledged version.  So, time to install it all.  Sever Installs with no dramas at all, quite hastily in-fact.  Following that is SQL 2008, which also installs without any hicup this time, however does take a very long time to install, and interestingly installs Visual Studio 2005, not 2008 (which is released the same time as SQL and Windows Server).  So we are up and running, we have a perfect install of SQL 2008 on a Windows server 2008 sever, time to install PPS.

PPS is a small install, just 60MB download, and 4 different installations: Planning Server, Business Modeller, Excel Add-In, and Monitoring Server.  For the Server Install we just want to install the Planning Server, and the Monitoring Server for the time being.  We will start with the Planning Server. PPS Prerequisits

On Checking the prerequisites for this we instantly come up with a problem.  PPS Planning, looks for a specific version of SQL, SQL server 2005, and therefore does not recognise SQL 2008.   This causes somewhat of a problem.  

Changing the Registry also doesn't seem to help, while trying to trick the install of the correct version.  All the other components seem to install fine, and therefor the only issue currently is Planning.  An obvious solution would be to install SQL 2005 Side By Side, and simply use the SQL 2008 Database.  However this is not an ideal solution, and I will be continuing work with this in the new year, as well as raising this issue with Microsoft.

I will post a follow up to this when we have resolved the installation issues.  In the meantime, have a great new year!

     

Hyper-V - What is it?

Hyper-V is in essence the new Virtual Machine software that is introduced with Windows Server 2008.  Windows Server 2008 includes Hyper-V (codename viridian), a new virtualisation technology that allows VM's to work better without 3rd Party Software, and supports 64Bit systems.

The most anticipated benefit of Hyper-V is server consolidation, enabling one server to take on the workloads of multiple servers. E.g. Web Server, Fax Server, Exchange Server, and SQL Server 2008 all on one piece of Hardware. However, Hyper-V enables more than server consolidation improving network reliability, scalability, security, and flexibility. Servers that slow down due to peak usage at the start of the workday, for example, can offload some of their workloads to other servers, which increase their capabilities, or move to more powerful servers, improving network availability.

Hyper-V is a key feature of Windows Server 2008, integrating with familiar, Windows-based server management tools. Businesses don’t have to purchase additional software to take advantage of powerful virtualisation features like live backup and quick migration.

Applications can also be efficiently virtualised using Windows Server 2008 centralized application access technologies. Terminal Services Gateway and Terminal Services RemoteApp allow easy remote access to standard Windows-based programs from anywhere by running them on a terminal server instead of directly on a client computer—without the need for a complicated VPN.

With testing the latest technology from Microsoft this Hyper-V should help developers test SQL 2008, for example, in parallel against SQL 2005. For example, a development team can test the latest version of an application on multiple platforms with a variety of virtual hardware capabilities.  

Embedding Images into DLL's

I am sure this is already well documented, however, its something that is really very straight forward with .NET 2.0, and not clearly written about so far on the web, so here is(hopefully) a good example:

 

Step 1:  Embed the Resource -

 

Step 2:  Reference the resource with the exact name and Namespace.

Step 3: Retrieve the Embedded resource

Image.ImageUrl = Page.ClientScript.GetWebResourceUrl(typeof(_Default), "[YourNamespace].Contributor.png");

 It is that easy!!

The PerformancePoint WebServices - Retrieving Data

In a previous post, I have discussed retrieving information from the PPS WebServices, namely, retrieving the system information, that returns information on the Applications stored within PPS.  To retrieve any further data from PPS WebServices, we need to know the application ID, which is returned from the GetSystem() method.

Once you have retrieved the applications, you may notice that there are not a huge amount of WebServices methods available for use.  This is because one WebServices is used to retrieve most data from PPS.  Again we are going to use the MetaDataManager.asmx, WebServices, and the method we can use is the Request method, that expects an object called a PerformancePointRequest, and in turn returns a PerformancePointResponse.  A PerformancePointRequest can be made to retrieve all sorts of information from PPS, and this is always returned to a PerformancePointResponse.  Here is an example of code that returns Cycle instance information.

MetaDataManager.QueryFilter ppQueryFilter = new MetaDataManager.QueryFilter();
ppQueryFilter.ResultDataType = MetaDataManager.ReferenceDataType.CycleInstances;

MetaDataManager.QueryWorkflowData performancePointRequest = new MetaDataManager.QueryWorkflowData();
performancePointRequest.TargetObjectId = ApplicationID;
performancePointRequest.QueryFilter = ppQueryFilter;
MetaDataManager.PerformancePointResponse ppsResp = ppsMDM.Request(performancePointRequest);
return ppsResp;

So the PerformancePointResponse will return a standard object that can be successfully cast to a number of different objects, I have not used all objects as yet, however we have most experience with the 'WorkflowDataResponse' object.  So casting the PerformancePointResponse object, to a 'WorkflowDataResponse'  object, we know have a Workflow Data Object that is a set of data tables that re compressed.  So.. To get the data we need to de-compress this.  The following shows how I have gone about decompressing this:

First we need to get the Table Schema, that is compressed:

public static DataTable UnpackTableSchema(byte[] packedTableSchema)
{
  DataTable table2 = new DataTable();
  XmlSerializer dataTableSerializer = new XmlSerializer(typeof(DataTable));
  if (packedTableSchema == null)
    {
      return null;
    }
      DataTable table = null;
      using (MemoryStream stream = new MemoryStream(packedTableSchema))
      {
        using (GZipStream stream2 = new GZipStream(stream, CompressionMode.Decompress, true))
        {
          table = dataTableSerializer.Deserialize(stream2) as DataTable;
        }
      }
      table2 = table;
    }
return table2;
}

Once we have the unpacked schema, we can unpack the data and load it against the schema:

There are quite a few methods to actually unpacking the data, so if you are interested please do not hesitate to contact me.  Also, once we have finished the product, it will be available for download at http://www.adatis.co.uk.  Should you like to take a look at our BETA please email us at devteam@adatis.co.uk

The PerformancePoint Planning Web Service

So.... you want to use the PerformancePoint web service so you can extend PerformancePoint, and integrate it into bespoke applications and SharePoint.  We needed to create a Web part that could show a users PerformancePoint assignments in SharePoint.  So, this should be simple we know that Excel uses the webservices to get all of its PerformancePoint  information, so lets just tap into that and away we go... OR not!

We were after creating a WebPart that did not require any further references and was self contained.  This is where life got tricky.  We needed to get all the applications within Performance Point, and then get all the Assignments for each application that are related to the requesting user. 

After looking through available web services, it is not clear how to retrieve the amount of data that is required to run PPS, so that was a complication.  However for now lets look at how to get the applications:

We can get the whole Biz system from the Web service using this: ( where ppsMDM is the MetaDataManager web service!) 

//Get the System Data
MetaDataManager.BizSystem bizSystem = ppsMDM.GetSystem(false);

This should return the whole system to a Biz system object, however, as we found the webservice attempts to cast the array of application into a dataset, and then fails to do so, meaning that we can not get the application data.  After some degree of head scratching it was decided to change the Webservice Reference.cs, so the Application list was returned as an object.  This enables us to then manually deserialize the XML, into our own object and get access to the entire Application object, as we should have had.  It does seem that if you are willing, and able, to use the PPS Client Dll's, that you can hook straight into them to get this to work less painfully (thanks to Casper and his post here) you need to reference this (Microsoft.PerformancePoint.Planning.Client.Common) and any other dependant DLL's.  This is how we have deserialize this, once creating the object ArrayOfBizApplication, and BizApplication:

sb.Append("<ArrayOfBizApplication>");

foreach (XmlNode xmlNode in xmlNodes)
{
  if (xmlNode.Name.ToLower() == "arrayofbizapplication")

    {
       sb.Append(xmlNode.InnerXml.ToString());
    }
}


sb.Append("</ArrayOfBizApplication>");

XmlSerializer serializer;

System.IO.StringReader sr = new System.IO.StringReader(sb.ToString());
XmlTextReader xmlTReader = new XmlTextReader(sr); 


serializer = new XmlSerializer(typeof(ArrayOfBizApplication));
arrayOfBizApplicaion = (ArrayOfBizApplication)serializer.Deserialize(xmlTReader);

Now we have the array of Applications we should be able to get the Application ID's and then the Assignements from PPS, without the need for referencing any Microsoft PPS Dll's. 

The Difference Between MOSS 2007 and WSS 3.0

It always astounds me what Microsoft are willing to bundle with their software, Analysis Services or SSIS (SQL Server Integration Services) with SQL Server for example!  They haven't stopped, they bundle Windows Sharepoint Services with Windows Server 2003, with is basically a fully functional SharePoint Server, however they still have SharePoint Server 2007  as a product.  So what are the differences? At first glance it doesn't appear to be much, however for tight integration into the enterprise, it seems that MOSS 2007 (Microsoft Office SharePoint Server) is a must! I will highlight the most interesting bits that MOSS 2007 has over and above WSS 3.0 (Windows Sharepoint Services) :

  • User Profiles support - Allows each user to store profile information
  • Site Manager - Manage Navigation, Security and look and feel with drag an drop functionality
  • Enterprise Search Tools - numerous tools to search Sharepoint Sites and Portals across entire enterprises
  • Business Data Catalog - The Business Data Catalog (BDC) tightly integrates external data, providing access to external data residing within other business applications, and enabling the display of, and interaction with external data
  • Business data search - Search data residing in your business applications using the BDC (See Above)
  • Business Data Web Parts - Used for viewing lists, entities, and related information retrieved through the Business Data Catalog
  • Business document workflow support - Automate document review, approval, signature collection, and issue tracking using workflow applications
  • Retention and auditing policies - Allows customized information management policies to control retention period, expiration, and auditing
  • Browser-based forms - Integration with InfoPath, allows integration on to SharePoint of created forms and surveys.
  • Integrated, flexible spreadsheet publishing - Allows information workers to easily choose what they want to share with others and determine how others can interact with published spreadsheets.
  • Share, manage, and control spreadsheets - Provides access to spreadsheet data and analysis through server-calculated, interactive spreadsheets from a Web browser. Can help to protect any sensitive or proprietary information embedded in documents, such as financial models, and audits their usage.
  • Web-based business intelligence using Excel Services - Allows spreadsheets to be broadly and easily shared. Fully interactive, data-bound spreadsheets including charts, tables, and PivotTable views can be created as part of a portal, dashboard, or business scorecard.
  • Data Connection Libraries - Document Libraries storing ODCs (Office Data Connections), Making one single location for all data connections.
  • Business Data actions -  Easily create actions that open Web pages, display the user interfaces of other business applications, launch InfoPath forms, and perform other common tasks.
  • Integrated business intelligence dashboards - Rich, interactive BI dashboards that assemble and display business information from disparate sources by using built-in Web parts, Excel spreadsheets, Reporting Services, or a collection of business data connectivity Web Parts.
  • Report Center - Provides consistent management of reports, spreadsheets, and data connections.
  • Key performance indicators - A KPI web Part can connect to Analysis Services, Excel Spreadsheets, SharePoint Lists, or manual entered data.
  • Notification service - Improved allowing workflow users to receive emails by default, and improved triggering and filtering
  • Single Sign-On (SSO) - Allows the User to log onto a variety of applications with a single user name and password, therefore integrating back office applications, and helps pre-population with integration to the Profile part of MOSS 2007.
  • Social Networking Part - Connect to Public My Site pages to help establish connections between colleagues with common interests
  • Personal Site Support - Allows Users to create Personal Web Sites  
  • Content syndication - Use RSS feeds to syndicate content managed in a portal site.

 

This is just a few of the extras you get in MOSS 2007, and I have highlighted those that are more angled to Business Intelligence! As for the cost, you can find that here: (In USD$ I am afraid) http://office.microsoft.com/en-us/sharepointserver... 

MOSS 2007 sits centre stage in Microsoft's BPIO (Business Productivity Infrastructure Optimisation) vision for the "People Ready Business"... Lets see what it can do!  

Oracle / Hyperion Forum - 17th October 2007

So did the event "Change Business Forever"... probably not, but it was very interesting to see how Oracle have integrated (or not as the case may be) Hyperion products into Oracle's plethora of products.  A number of the presentations for the event are now available here.  Possibly the most interesting one is called "Business Performance Measurement State of the Art" presented by Andy Neely (Cranfield University), who presented a very interesting set of survey results on the current Performance Management market, with results gathered from over 220 large organisations. To briefly summarise what was mentioned (there was a lot of percentages, and facts, so I will try and highlight the most interesting ones):

  • Over 54% of those surveyed have adopted a balance scorecard approach
  • Over 61% believe that improved company performance is attributed to Business Performance Measurement
  • Over 63% (average) believe that their current BPM helps them make Strategic Decisions, Operation decisions and positively affects KPI's
  • However, it is noted that over 50% believe that their BPM does not provide insight.
  • Nearly 40% have concerns of their data quality
  • By a huge margin (Over 70%), Spreadsheets are the tool of choice, compared to custom built software, ERP software, BPM software and nothing at all!
  • A massive 74.6% of companies developed their BPM inhouse
  • Over 64% do not visualise the links in their BPM

There are many more results in the presentation that are well worth a read. 

As for the rest of the day, it was very interesting to see what Oracle and Hyperion have to offer, and they do not seem to have integrated Hyperion and the base Oracle suite that well, although it is promised to be coming.   Without being from an Oracle background, and therefore seeing a lot of this as new, there seems to be a lot of overlapping applications, and so many parts to the framework that it is very difficult to see the wood for the trees.  Saying they Hyperion Essbase is remaining, and as yet has not been renamed.  The Oracle suite makes Microsoft look very coherent and easy to understand, and judging by Oracle's candidness to answer cost questions, still a lot cheaper.  In fact Oracle are approaching the reduction in cost by adding more functionality and built in functions to make it quicker out of the box, and not reducing the product price.  One last thing worth noting is their push to integrate with Microsoft Office, we all know the Essbase Excel Add-In was, and arguably still is best in class, and Oracle fully intend to continue with these Office integration efforts.  However to be blunt, will they ever be able to integrate with Office as well as Microsoft can?

All in all certainly an interesting day, however I can not see Oracle slowing down Microsoft with their currently offerings!

Install SQL Server 2005, After 2008

So, if like me, you wanted to look at SQL 2008, and thought it best to totally remove 2005, and then install 2008 (Katmai), then realising that the Katmai BI tools did not install properly, you may want to re-install 2005. 

If have got to this stage you may have found and annoying installation bug, even after removing 2008. This is regarding the SQL Native Client.  It fails to install and therefore most of SQL 2005 fails to install.  After numerous more efforts to install this there is a simple fix.

From the SQL 2005 disc, run Sqlncli.msi

 - Choose remove, repair doesn't work!

 - Once removed install SQL server 2005 as normal - easy - when you know how!

Katmai (SQL Server 2008) CTP Installation

While installing the latest CTP release for SQL Server 2008, I came across an odd installation error.  It was one of those errors that is less than helpful, as follows:

 

 

TITLE: Microsoft SQL Server 2008 Setup

------------------------------

Upgrade Failed due to the following Error.The error code is :-2147467259.Message:Unspecified error

------------------------------

The install package had installed everything but the end client components and tools!  Most frustrating, and the only thing I could do was see what would install and what wouldn't.  The problem is, the whole installation process takes so long!  

After retrying this install numerous times, it seems that it thinks it is being upgraded, although there is no instance, and never has been an instance of SQL Server 2000, or SQL Server 2005.  However, I do have Visual Studio 2005 installed.  After removing components and re adding them, I seem to have identified the problem; it will not install with Business Intelligence Development Studio.  So Installing SQL Server 2008 with out this component included works no problem.  However this will cause a problem as I will be needing BIDS.  As and when I find out how to install it I will add it to my blog!

 

Hope this helps any of you out there who are experiencing the same problems!