Adatis BI Blogs

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.

Planned Performance Improvements for PerformancePoint Server 2007 Planning

There are a few performance improvements planned for SP1 and earlier in an interim hotfix.  The details I have are high-level but enough to get at least a little excited that development is on going and pertinent to 'in-the-field' issues. In an upcoming hot fix (KB946222) expected release prior to SP1 is currently being worked on. Issue:When a system contains a large number of assignments, retrieving the list of available assignments for a user has a significant performance issue. Resolution:Optimised stored procedure for retrieving assignments to dramatically cut time to retrieve list of available assignments. Performance issues targeted for SP1 include: Slow performance when re-opening assignment after save/submit. No option to disable what-if calculation on assignment or matrix level which leads to unnecessary what-if queries that slow form performance Successfully submitted change-lists are not automatically from the assignment in the V1 design which leads to unnecessarily large what-if queries MDX query construction optimization Client-Server data volume These are targeted issues and hence they may not make the cut but it's encouraging all the same.

Book: Rational Guide to Monitoring and Analyzing with PerformancePoint Server 2007

Christmas came early last week when a copy of the First PPS book to be published dropped through our letter box - Thanks Nick and Adrian :)   For those of you aren't familiar with Rational Guides the format is designed to get you up and running with a product rather than be an in-depth technical reference and this is exactly what the authors achieve with this book.  I suspect that Nick wrote the majority of this book whilst Adrian will have more focus on the upcoming companion book Following the introduction the book is split into: Installation and Configuration; The elements (a chapter on each of the core dashboard elements); and Implementation and Management (Deploying and securing PPSM) and flows nicely from one chapter to the next with writing at a suitable level - not ridiculously technical and not condescending.  The KPI chapter I found particularly useful as this is one of the deeper areas of PPSM. Whilst not wanting to sound like a free advert, this is an excellent book for getting started and reaching a good level with PPS M&A.  If you are looking for a hardcore technical guide to PerformancePoint then: a) other than what's on technet, it doesn't exist yet!; and b) this isn't it. Having said that, the authors point out a number of quirks, bugs and pitfalls of PPS M&A that you might never find out otherwise.  Definitely a recommended read for anyone who wants to get up and running with PPSM One thing to note is that although ProClarity is marketed as the "Analyze" component of PerformancePoint it is not covered in this book - that's easily a book in itself!    

Reporting Services features not supported in Sharepoint Integration Mode

The latest version of SQL Books Online details the features of Reporting Services that are available in Sharepoint Integration mode and, more importantly, the features that aren't!!  Be careful if you plan to migrate as there are some surprising omissions: URL addressing is different in SharePoint integrated mode. SharePoint URLs are used to reference reports, report models, shared data sources, and resources. The report server folder hierarchy is not used. (This basically means no querystring parameters!!!) Reporting Services custom security extensions cannot be deployed or used on the report server. The report server includes a special-purpose security extension that is used whenever you configure a report server to run in SharePoint integrated mode. This security extension is an internal component, and it is required for integrated operations. Report Manager or Management Studio cannot be used to manage a report server instance that is configured for SharePoint integration. Data-driven subscriptions are not available. This applies to all editions. The rs.exe command line utility is not supported. The utility does not support the SOAP endpoint used for programmatic access to a report server that runs in SharePoint integrated mode. Linked reports are not supported. My Reports is not supported. Job management features that allow you to stop a long-running report process are not supported. Batching methods will not be supported. I haven't tested it yet but I have a feeling this might mean that the PerformancePoint Planning operational reports may not work in this mode The online (!) Books Online article is here

PerformancePoint Planning Assignment Web Part for SharePoint

 Update: Version 1.1 is now available  So the Adatis dev team have been busy! As I mentioned in a previous post, one of our clients wanted to be able to show a user's active PPS Planning assignments in their Sharepoint Portal in the same way you can see them in the assignments pane within Excel when you have the add-in installed.  "No Problem!" we said naively, assuming it wouldn't be too tricky as everything is passed back and forth using the web service.   Anyone who has developed .Net Web Parts will know it's a little convoluted, particularly if you want incorporate a decent user interface.  Calling the  Planning Web Service itself was relatively straight-forward though a couple of things were far from obvious and required work-arounds. One of the development requirements was that none of the PPS dll's should need to be installed on the server as it is purely a SharePoint box.  This made things a little more tricky as there is a bit more going on in the PPS excel add-in than just calling the web service!  It also needed to be configurable using the web part property editor as well using allowing users with the PPS Add-in installed to click on the hyperlink and open the assignment in Excel Anyway a few weeks (a few late nights and a few more grey hairs) later it's ready for a beta release: There is currently no documentation around the planning web service so we took a good look round the dll's that get installed with PPS using a great freeware product called Reflector and managed to piece together what was going in Excel when you connected to the PPS Server.  If anyone would like to try the web part please drop us an e-mail and we'll be happy to send you a copy (with the usual disclaimers in case your server falls over in a heap!) in return for some testing and feedback.  We've only had a limited amount of time to test it so any help is much appreciated.  Once it's stable we'll put it on our Web site for download. Thanks to Bully for just a little bit of help with the code ;).  Thanks also to Kevin White and Scott Heimendinger at Microsoft for some pointers in the right direction.

Planning a PerformancePoint Planning Implementation

There is a useful spreadsheet (what other format would it be!?) to assist in the requirement gathering and the planning of the implementation for a PerformancePoint Planning Application. It's a strange mix of high and lower level detail but it does ensure you capture and at least think about each element of your application and the business requirements it should satisfy.  It's a useful starting point/stake in the ground although some of the questions will require much more thought and analysis behind the scenes than others. The main topics it covers are: Completing an Impact Assessment Application, Model, and Model Site Planning Considerations Model Type Planning Considerations Dimension Planning Considerations Currency Translation Planning Considerations Data Loading Planning Considerations Business and Process Planning Considerations Reporting Planning Considerations Business Rule Planning Considerations Model-to-Model Association Planning Considerations Diagramming the Application

PerformancePoint Planning Server - Upgrading from CTP*

The PPS Operations Guide has a good procedure for upgrading from a pre-release version: It's relatively straightforward to follow but if you want a quick heads up, see below: - Backup all the databases, both server databases and all application staging databases- Uninstall all the PPS binaries- Re-install the PPS binaries selecting 'Distributed Installation' to allow you to unselect the PPS Database Installation(You'll upgrade, keeping all applications/server config in place this way)- Run ppscmd upgrade /server < PlanningServer URL > from the command prompt(This upgrades the database schemas for all applications to the new version)- Connect to the Administration console and 'Take online' the applications It's a similar but simpler process for Monitoring Server too.  

PerformancePoint Server Protocol Handler

Update: The web part is now in beta test - more here When you install the PerformancePoint add-in for Excel it also installs the PerformancePoint Server protocol handler.  This allows you to send out hyperlinks for assignments in e-mails etc so that users can simply click on the link and open their assignment in Excel.  The links are in the format: PerformancePoint:http:\\PPSServer:46787&Application=MyPlanningApp&Assignment=1234 However despite the strong links between SharePoint and PerformancePoint you can't put these into PPS hyperlinks into a SharePoint links list as the links won't validate with PerformancePoint: tagged on the front.  And of course this list wouldn't be dynamic based on the users own assignments. To overcome this, we are currently working on a SharePoint custom webpart for one of our clients that will automatically pick up the users personal assignments.  If anyone is interested drop me a line and I'll be happy to send you a copy once it's a bit more complete. Happy Halloween!!

PerformancePoint Server Planning - Business Rules Debugging

In PerformancePoint Server 2007 Planning, you build centralised business rules in a new language called PerformancePoint Expression Language (PEL).  PEL is very MDX-like and relatively straight-forward to pick up, if you are even a little familiar with MDX.  The beauty of PEL is that it can generate either MDX or T-SQL scripts from the same PEL Expression.  This allows the developer/analyst to target either the cube itself, using MDX Script, or the fact table, using T-SQL, depending on which implementation approach is more suitable for the type of calculation and from a performance perspective. Within the Business Rules Editor workspace you can easily select the implementation (SQL or MdxQuery) from the Rules Detail property list       With the Rule saved (it does not have to be deployed) you use the rule context menu to debug the rule. If you come from a development background like me, you may expect a little more to happen than what actually does happen ! Depending on what implementation you have selected, and whether or not your PEL is valid, either the MDX or the T-SQL is generated from the PEL and displayed in a window.  It's important to realise that the PEL expression has not been run.  It is for information purposes only. From this point you can eye-ball the resultant query to help determine your issue or, as I tend to do, cut and paste into a New Query window inside SQL Server Management Studio.  The resultant T-SQL tends to be extremely verbose so actually debugging the problem using T-SQL will be rare ! If there is a problem with the actual PEL itself, the reason, in the form of a (normally) reasonably helpful error message is displayed in the window, instead of the resultant MDX/T-SQL. I was initially a little disappointed with the built-in debugging facilities, but considering the target audience of the Planning Business Modeler, full integration with SSMS or Visual Studio was never going to be a consideration.  But, to be fair, it is probably just enough; the debugger is primarily aimed at ensuring your PEL is correct, it stops somewhat short of helping you debug the actual business logic but this can be handled if you cut, paste and hack the resultant MDX or T-SQL into another tool and even that becomes less and less necessary as you develop your PEL writing skills.

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)  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!  

PerformancePoint Server Planning 2007 - eh?

Despite all the hype, the recent launch, several PPS related conferences, numerous articles and the pull-no-punches Olap Report Preview ,there is still some doubt about what the Planning element actually offers, well.. To quote directly from the marketing bumf: Efficiently build budgets, forecasts, and plans in the interface everyone knows—Microsoft Office Excel. PerformancePoint Server 2007 offers auditing capability, centralized control, enhanced security, and the proven data platform of Microsoft SQL Server 2005 For a specific example, imagine, without PerformancePoint Server, trying to build an enterprise-wide solution to capture quarterly financial based budgets and/or forecasts from every budget-holding manager across the organisation, for consolidation and approval by the CFO.  The issues you would face would revolve around data-capture, workflow, security, consolidation, business rules, performance, tracking etc.  Even if you were successful, it's likely that you would eventually end up in Excel hell, or left with no confidence in the results and a huge team on full-time support. Step forward PerformancePoint Server Planning.  It removes much of the pain associated with all of the above and is not just limited to financial based scenarios.  In addition, when integrated with PerformancePoint Monitoring you can track the actuals against the captured plans/targets from Planning to ensure the business stays on track.  When the enevitable deviations occur, the Analytics element of PerformancePoint Server (Currently ProClarity) will help analyse why to allow corrective action to be taken.  For a little more detail..

PerformancePoint Server 2007 Forums - Released

The PerformancePoint Server forums are now part of the official TechNet Forums and no longer limited to Microsoft Connect Beta Forums. For those that didn't see the relatively active Beta forums on Connect, Microsoft separated the product into two groups and this is reflected in the official versions too. Planning: Monitoring and Analytics: At the time of writing the Planning forum was empty and the M&A forum had just the single topic.  It will be interesting to see how the activity picks up through the launch period and into the New Year.

SAP buys Business Objects

One of the BI/BPM world's worst kept secrets is officially announced - SAP has bought Business Objects for $6.8, billion nearly twice as much as Oracle bought Hyperion for.  There were some (aherm) who thought this was just a rumour to inflate BOBJ's share price.  It leaves Cognos as the only big "independent" in amongst the major players.  Let the rumours begin (or rather continue)... I don't think this guy is too far off the mark

Multi-dimensional modeling (ADAPT)

Everybody hates documentation but this is a really neat, free tool for modeling multi-dimensional databases and is particularly suited to MSAS 2005.  ADAPT™ (Application Design for Analytical Processing Technologies) from Symmetry Corporation is a Visio stencil.  Example of a time dimension is shown below: There's a white paper that's worth a read to get you started

Community Service

It's been a busy week combining a release in my current project with getting out and about in the community! Wednesday evening was the SQL BI user group.  Suranjan Som of IM Group gave a very good presentation on Data Mining using Microsoft's toolkit.  I have to be honest, I last looked at the data mining functionality in SQL2000 on my first AS project what seems like years ago now - it's definitely worth another look now. Mark Hill of Edenbrook gave us a summary of the features in the Katmai relational engine that will affect us in the BI world.  Having used CDC in Oracle I was most interested to see how MS have gone about implementing their version.  On first impressions it certainly looks pretty good - performance and robustness TBC! It was great to catch up with some people I haven't seen for a while and also meet some new faces.  Thanks to Chris Webb for organising and to IMGroup for hosting. The MS BI partner update was on Thursday and a chance for us to do some hob-knobbing with the rest of the UK BI community.  As a growing company we realise the importance of these type of events and appreciate it when people give us the time of day and more! Thanks in particular to Ben Tamblyn, Andrew Fryer and David Hoffs-Mallion for their advice and time In terms of the event itself, nothing too surprising to report - though the fact that ProClarity won't be properly integrated into PPS Server as the Analytics component until the next major release (2009) was little surprising.