Kasper de Jonge PowerPivot Blog

How cool is that .. a free SQL Server 2008 R2 ebook!

The book contains 10 chapters and 216 pages, like so:

PART I   Database Administration

CHAPTER 1   SQL Server 2008 R2 Editions and Enhancements 3
CHAPTER 2   Multi-Server Administration 21
CHAPTER 3   Data-Tier Applications 41
CHAPTER 4   High Availability and Virtualization Enhancements 63
CHAPTER 5   Consolidation and Monitoring 85

PART II   Business Intelligence Development

CHAPTER 6   Scalable Data Warehousing 109
CHAPTER 7   Master Data Services 125
CHAPTER 8   Complex Event Processing with StreamInsight 145
CHAPTER 9   Reporting Services Enhancements 165
CHAPTER 10   Self-Service Analysis with PowerPivot 189

You can download the ebook in XPS format here and in PDF format here.

The release date will probably be May 2010:

http://blogs.technet.com/dataplatforminsider/archive/2010/01/19/sql-server-2008-r2-gets-an-official-date.aspx

Can’t wait !

This is something I have used many times, and always had to look up on internet. I’m designing a new datawarehouse and have to populate my date dimension. In SQL server 2008 we have common table expressions (CTE),  We can use it to run a sequence from to a date. This blog post will explain how you can generate dates: http://smehrozalam.wordpress.com/2009/06/09/t-sql-using-common-table-expressions-cte-to-generate-sequences/

You can set you regional date / time by using the Set language dutch (where dutch is your regional setting).

I was searching for some query to get information from my DWH without me having actually access, i found a set of pretty heavily commented queries that are very useful for detecting and diagnosing many common performance issues with SQL Server 2008 created by Glenn Berry

Check them here:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!2015.entry

Chris Webb is at the SQL Pass and blogging live from John McAllister’s session on Master Data Services at the PASS Summit, and here are some notes…

• The first public beta is due next week
• MDS is will be packaged with 2008 R2 (rather than Sharepoint), be on the DVD but not part of the main install
• Will have an API – everything you can do in the UI, you can do in the API
• Although it’s part of SQL Server it will still rely on Sharepoint for workflow; the main, web-based UI is not Sharepoint-based though
• Has simple business rule validation capabilities, eg make sure that the list price of a product is greater than its cost
• Includes basic documentation features
• Also has auditing features – you can see every transaction ever made in the system, reverse changes made and so on
• Models are containers for different types of data (products, customers); every model can have a version, and versions can be locked, open for editing etc; models can also be secured
• Also has basic notification features, so users/groups can get emails when something changes
• No direct SSAS integration at the moment, but they hope to have some in the future

Great stuff ! Essentially the SSAS integration great stuff ! Thx Chris for sharing!

While Virtualization of hardware is getting hotter by the day, Ms teched created a blog post about virtualization:

Should I virtualize SQL Server? What benefits will I see?  What do I need to consider when making this decision?  These are some of the questions we are frequently asked as Virtualization continues to be a hot topic.  The short answer is yes – Microsoft Virtualization (Windows Server 2008 R2 Hyper-V + Microsoft System Center) delivers significant benefits including reduced costs through server consolidation and power and space savings, improved server utilization, greater agility for responding to dynamic business needs, rapid server provisioning, reduced management complexity and reduced downtime during failover with Hyper-V Live Migration.

Read the rest at: http://blogs.technet.com/dataplatforminsider/archive/2009/09/02/microsoft-virtualization-best-choices-for-sql-server.aspx

We recently released the first version of an operational, near real time, BI platform for monitoring packages in a package sorter for a dutch company. This blog post will describe how we solved their problem using Reporting Services, Integration Services, ASP .Net, hosted in the cloud using Amazon Elastic Compute Cloud.

The scenario

There are 4 sorting centers in the Netherlands where parcels are collected from the region where it is in. Parcels receives a unique code (Bar code) on collection.  The sorting centre then has to determine by postal code the destination of a parcel, parcels are eventually delivered by regional delivery stations. The Parcels are grouped by the sorting centre and during the night shipped to the delivery stations, the delivery station that is the farthest away gets its parcels first. When a parcel destination is closer then another sorting center it is send to that sorting center first, so parcel not within the range of the sorting center are coarsely sorted by region and parcels that are in range are finely sorted by street. Parcels are loaded upon a assembly line of a sorter machine in the sorting center, the postal code is scanned and translated to a digital postal code that will be attached to the bar code. The sorter then determines what the destination assembly line is, called chutes.

The current information is brought by reports full of numbers that are hard to interpret and an excel sheet full of VBA code that is mailed around the organisation. Each sorting centers hosts it’s own server running Reporting services 2000 and a Visual Basic service.

The challenge

The challenge the client had for us was:

• Consolidate data from different sources on one report
• Replace local machines with one central machine
• Bring the right data to the right people, data from sorting center to a manager, data from a team to a team lead
• Show the data as soon as it is available, near real time BI
• The data has to be extendable with data from other sources
• New ad-hoc reports have to be created in a short development cycle

How we did it: Project Startup

The project started with some brown paper session with different users from the organisation.  In these sessions we determined the essential data needed to manage the sorting centers. Focus of the first release was replacing the current reports and excel sheet with the new reporting  environment. The data will be delivered to our system by text files, every minute new files will be created and have to be imported and transformed by the application, estimate is 4 files per minute.

After designing reports for the different user groups, determining the MoSCow and analyzing the different input files we decided for the following architecture:

• Loading and transforming data with SSIS
• Database with SQL Server 2008, data in report tables with different aggregation levels, some data is needed at the lowest grain, but most data is over a larger time dimension.
• Reports with Reporting Services 2008
• Reporting portal with ASP.Net 3.5, reportviewer control and ASP.Net dynamic data for master data tables

While brainstorming with the client it appeared they would like to host the system outside their own infrastructure, we already had some experience with hosting at the Amazon Elastic Compute Cloud and decided to try to determine the feasibility of using Amazon for our bi system. Since the data is delivered in small files every minute a solution was quickly found. Servers hosted in the cloud files can fetch files as easily as local servers.  The server in the cloud fetches data using SFTP. The Amazon cloud is very flexible that let you quickly scale capacity, both up and down, as your computing requirements change.

Implementation

Below is a graphical representation of the architecture used:

The application consists of the following steps:

• Data from the system is enriched with describing master data, this describing data is stored in a relational datamodel that can be maintained by pages that are generated by ASP.Net dynamic data
• Files are transferred to the system by a SSIS package that calls a SFTP application that will move (copy and remove) the files to the BI server, this package is scheduled every minute.
• Files are loaded by two SSIS packages per filetype. One package fetches all files currently in a configured directory using a for loop that traverses the directory. These files are passed through to a package that will import the file into the system. The data from the file is loaded into the SSIS memory and enriched using the lookup component, when describing data is not found these rows will be written to an error table. The remaining data will be written to a table, with data on minute level. These filewatching packages are scheduled to run every minute. The current system performs excellent with 30 files in one minute.
• A lot of data is added to the lowest grain table so to keep performance acceptable some aggregation is applied. Every minute a SSIS package is scheduled that selects the last 2 hours of data from minute table into a temporary table grouped by hour and summing the measures. The data is then merged into an Hour aggregation table using the SQL Server 2008 MERGE statement,  with an update of the measure when the row existed and insert when not. This made sure the data is incrementally increased by each run.
• Reports also show live data from the last 15 minutes so a package is run every minute that fetches data from the last 15 minutes and places it into a table, this table always show 15 minutes of data.
• There are different reports, one for each user in the organisation. The sorting center manager gets a report showing all teams at work, he can then click on a team and drill down to a report with data of that team.
• Users who have opened the report want to see the new data loaded, we used the AutoRefresh function in RDL to automatically refresh the report every minute
  

Finally a screenshot of one of the SSRS reports hosted in the ASP.Net application:

Overall a great project to work on and we are very pleased with the result I hope you get a good idea of how we used the MS BI stack to create this operation BI tool.

I was at a client and they had some performance issues calling stored procedures from code. They were unable to find the sql statementfor responsible the performance degrade.

You can use the SQL profiler to show you the TSql statement within your stored procedure and their trace properties, to do this go to trace properties, check Show all events and you will get the possibility to check 2 events that will look inside the SP’s and show you alle the Tsql statements.