Kasper de Jonge BI Blog

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.

Having to de-duplicate rows from source tables is a very common task in the data integration world, we were trying to do all different of Tsql to filter out the duplicate values. We needed to pick the row based upon a certain criteria. On the internet i found the TSQL function Rank()

It works like this:

No matter where the source data is stored, as a part of the ETL workflow, we will stage source data in a SQL Server 2005 table.

This is the staging table:

create table SourceWithDuplicates
(SurrogateKey int identity(1,1),
Code varchar(12),
Description varchar(20),
OtherAttribute1 int,
OtherAttribute2 Varchar(10),
TieBreakerColumn Varchar(12))

As a general rule I always add a surrogate key column to my staging tables (that makes some DBAs happy); an in this case it would actually help in the de-duplication process.

Some sample data:

Insert into SourceWithDuplicates values(‘Abc’, ‘Row Description 1′, 1, ‘Other1′,’1′)
Insert into SourceWithDuplicates values(‘Abc’, ‘Row Description 1′, 1, ‘Other1′, ‘1′)
Insert into SourceWithDuplicates values(‘Abc’, ‘Row Description 1′, 1, ‘Other2′,’2′)\
Insert into SourceWithDuplicates values(‘Def’, ‘Row Description 2′, 1, ‘Other4′,’a')
Insert into SourceWithDuplicates values(‘Def’, ‘Row Description 2′, 1, ‘Other5′,’a')
Insert into SourceWithDuplicates values(‘Ghi’, ‘Row Description 3′, 1, ‘Other5′,’a')
Insert into SourceWithDuplicates values(‘Jkl’, ‘Row Description 4′, 1, ‘Other5′,’a')

The requirements:

Code and description values need to be unique in the target table; in case they are duplicates, choose the row with the highest value in the tie breaker column. If the value in the tiebreaker column is also duplicated; then choose any of the rows.

So the data looks like:

SurrogateKey Code Description OtherAttribute1 OtherAttribute2 TieBreaker ———— ———— ——————– ————— ————— ———-
1 Abc Row Description 1 1 Other1 1
2 Abc Row Description 1 1 Other1 1
3 Abc Row Description 1 1 Other2 2
4 Def Row Description 2 1 Other4 a
5 Def Row Description 2 1 Other5 a
6 Ghi Row Description 3 1 Other5 a
7 Jkl Row Description 4 1 Other5 a

Following the requirements, only 4 rows kept should be chosen; those with surrogate keys:2 3, 4 or 5, 6 and 7
This is the query using the Rank() function that resolve our issue:

Select *,
rank() over (Partition by Code, description order by TieBreakerColumn desc, SurrogateKey) MyRank
from SourceWithDuplicates

and this is its output:

Surrogatekey Code Description MyRank
———— ———— ——————– ——————–
3 Abc Row Description 1 1
1 Abc Row Description 1 2
2 Abc Row Description 1 3
4 Def Row Description 2 1
5 Def Row Description 2 2
6 Ghi Row Description 3 1
7 Jkl Row Description 4 1

Notice that if we filter the result from the previous query using WHERE MyRank=1 we will get the rows to be kept.

Very nice ! I  found this at:

http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html

Today two colleagues wanted to schedule the output of a SQL command to a text file so they could check some values on a daily base. After some clicking in SQL Server i found a very simple solution,

I created a SQL server Job Step which executed the SQL command

Then went to the Advanced tab and selected an output file to where the data can be written (even appended):

Very simple way to log query output to a file at a regular interval.

Today we are excited to announce the availability of the first community technology preview (CTP) of Microsoft SQL Server 2008 R2 for MSDN and TechNet subscribers, with the CTP generally available on Wednesday, August 12th.

Building on the momentum of SQL Server 2008, “R2” improves IT efficiency by reducing the time and cost of developing and managing applications; empowers end users to make better decisions through Self-Service Business Intelligence and enables organizations to scale with confidence by providing high levels of reliability, security and scalability for business critical applications.

This public preview offers the opportunity to experience early, pre-release feature capabilities including:

• Application and Multi-server Management
• SMP scale up with support for up to 256 logical processors
• Report Builder 3.0 with support for geospatial visualization

This CTP provides the first opportunity to explore some of the features of SQL Server 2008 R2 and see how it all comes together to enhance performance and scalability, enable self-service BI and improve IT and developer efficiency. The CTP process is also a great way for you to give us feedback on the new technologies.

Read more at: http://blogs.technet.com/dataplatforminsider/archive/2009/08/10/download-sql-server-2008-r2-august-ctp-today.aspx