Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for May, 2009

2009 Download Catch-up List

Posted by denglishbi on May 31, 2009

Over the first part of the 2009 year I have downloaded numerous training items, whitepapers, applications, and patches and just wanted to take a little time this weekend to put out a list of items to share that I believe will be useful if you haven’t come across them yet.  Some of these maybe a little older, I might have posted about them already, or you might already know about them, but just want to put them out there just in case.  Not all of these are strictly SQL Server or Business Intelligence related, so just want to warn you.  You can share the information with someone else that might benefit from reviewing the material though.

Training Items

  • Hands-On Labs for Enterprise Library 4.1 – Use this set of Hands-on Labs as a guide to learn about the application blocks included with Enterprise Library 4.1 and practice how to leverage their capabilities in various application contexts. It includes Caching, Cryptography, Data Access, Exception Handling, Interception, Logging, Security, Validation, and Unity Hands-on Labs.
  • Azure Services Training Kit – April UpdateThe Azure Services Training Kit includes a comprehensive set of technical content including hands-on labs, presentations, and demos that are designed to help you learn how to use the Azure Services Platform.
  • Office SharePoint Server 2007 Technical Library in Compiled Help format – This downloadable CHM is a copy of content in the Microsoft Office SharePoint Server 2007 technical library. The CHM is current as of the date above. For the latest information, see the technical library on the Web.
  • SQL Server 2008 Developer Training Kit (SQL Server 2008 Developer Toolkit feedback)- SQL Server 2008 offers an impressive array of capabilities for developers that build upon key innovations introduced in SQL Server 2005. The SQL Server 2008 Developer Training Kit will help you understand how to build web applications which deeply exploit the rich data types, programming models and new development paradigms in SQL Server 2008.
  • SQL Server 2008 R2 Information and New Site – also sign up for CTP notifications on the new site.
  • IT Operations Scorecards and Dashboards – PerformancePoint Server monitoring sample available.  There is a video, whitepaper, and code sample files for download.
  • Microsoft SQL Server 2008 Books Online (May 2009) – SQL Server 2008, the latest release of Microsoft SQL Server, provides a comprehensive data platform. Books Online is the primary documentation for SQL Server 2008.
  • Demo Showcase 2008 Application Platform Optimization Demos – The Demo Showcase 2008 APO Demos are a set of 4 comprehensive click through demonstrations designed for partners to use with customers. These customer scenario demos feature many different Microsoft technologies working together to showcase the power of the App Plat Optimization capabilities. Included in the scenarios are the following products: Windows Server 2008, SQL Server 2008, Office Enterprise 2007, Visual Studio 2008, Windows Vista, Silverlight, Performance Point Server 2007 and more.


Applications (all FREE)

  • SharePoint Designer 2007 – SHAREPOINT DESIGNER 2007 IS NOW FREE! Learn more about these changes and future direction (watch the FAQ video link below). Office SharePoint Designer 2007 provides the powerful tools you need to deliver compelling and attractive SharePoint sites and quickly build workflow-enabled applications and reporting tools on the SharePoint platform, all in an IT-managed environment. Video of SharePoint Designer 2007 FAQ (16.8MB)
  • BIDS Helper – A Visual Studio .Net add-in with features that extend and enhance the functionality of the SQL Server 2005 and SQL Server 2008 BI Development Studio (BIDS).  This is a must have tool if you are doing any BI development with BIDS (SSAS, SSIS, or SSRS).
  • ZoomIt – ZoomIt is a screen zoom and annotation tool for technical presentations that include application demonstrations. ZoomIt runs unobtrusively in the tray and activates with customizable hotkeys to zoom in on an area of the screen, move around while zoomed, and draw on the zoomed image.
  • Report Builder 2.0 with SP1 (stand-alone) – Features specific to Report Builder 2.0 are focused on simplifying the process of creating and editing reports and queries and include the following: Easy to use wizards for creating table, matrix and chart data regions; Support for directly opening and editing reports stored on the report server; Support for using server resources such as shared data sources; Query designers for multiple data sources including a Microsoft SQL Server-specific query designer. (this tool does require a SQL Server 2008 license – Report Builder Licensing – you do need to so just be aware of this)
  • Data Mining Add-ins for Microsoft Office 2007 – Microsoft SQL Server 2005 or Microsoft SQL Server 2008 enables you to take advantage of SQL Server predictive analytics in Office Excel 2007 and Office Visio 2007.
  • BI Report Automation Publisher – for moving PerformancePoint Server items between environments and will work with Workspace files. The BI Report Automation Publisher is a command line tool + .net library which provides a more automated way to move data sources and reports between servers/environments.
  • Collecting Analysis Services Performance Data for Performance Analysis – ability to collect and gather information from Analysis Services using trace files, performance monitoring tools, and DMVs.  Includes all documentation, scripts, SSIS packages, trace files, XMLA scripts, SSRS reports, etc. to get started.


I think that covers the majority of it for now.  Lots of different items and plenty of stuff to keep you busy for a few weeks.  Enjoy! 

By the way, don’t forget to check out the Microsoft BI blog.  There have been lots of useful postings added recently with videos in regards to mobile BI, Excel 2007 Data Mining Add-in, Gemini demos, etc.

UPDATE (7/15/2009): While you can download Report Builder 2.0 this product does require a SQL Server 2008 license – Report Builder Licensing – you do need to.

Posted in Downloads | Tagged: | Leave a Comment »

Windows Server 2008 and Vista SP2 is now available

Posted by denglishbi on May 26, 2009

Just a follow-up from my previous posting, Windows Server 2008 and Vista SP2 coming soon, looks like SP2 is now available.  SP1 is a prerequisite, but Windows Server 2008 was shipped with SP1.

Windows Server 2008 Service Pack 2 and Windows Vista Service Pack 2 – Five Language Standalone (KB948465)

Windows Server 2008 Service Pack 2 and Windows Vista Service Pack 2 – Five Language Standalone for x64-based systems (KB948465)

Windows Server 2008 Service Pack 2 and Windows Vista Service Pack 2 – Five Language Standalone DVD ISO (KB948465)


Service Pack 2 for Windows Server 2008 and Windows Vista (SP2) is an update to Windows Vista and Windows Server 2008 that supports new kinds of hardware and emerging hardware standards, and includes all updates delivered since SP1. SP2 simplifies administration by enabling IT administrators to deploy and support a single service pack for clients and servers. Please see the Windows Server SP2/Windows Vista SP2 page on TechNet/MSDN for additional details and documentation.

Windows Vista SP2 TechNet
Windows Server 2008 SP2 TechNet

SP2 is an update to Windows Server 2008 and Windows Vista that incorporates improvements discovered through automated feedback, as well as updates that have been delivered since SP1. By providing these fixes integrated into a single service pack for both client and server, Microsoft provides a single high-quality update that minimizes deployment and testing complexity for customers.
Service Pack 1 is a prerequisite for installing Service Pack 2. Please make sure that your system is running Service Pack 1 before you install Service Pack 2.

Note: Windows Server 2008 released with Service Pack 1 included. Windows Vista SP1 information and downloads can be found on the Windows Vista SP1 TechNet page.

Windows Server 2008 SP2 and Windows Vista Service Pack 2 – Five Language Standalone version can be installed on x64-based systems with any of the following language versions: English, French, German, Japanese, or Spanish.

If your system has additional languages please use the All Language Standalone to install SP2.

Posted in News and politics | Leave a Comment »

PASSMN May 19 SSIS Presentation Follow-up

Posted by denglishbi on May 20, 2009

I have posted this content on the PASSMN user site here – May 19, 2009 – Dan English: SSIS Team Development, Deployment and Configuration, but if you do not have access I have also uploaded the presentation and SSIS demo project files to my SkyDrive area and those are available below.  Here is a link to the PASSMN May speaker content – PASSMN May 19, 2009 Monthly Meeting.

Thanks for everyone that showed up and I really enjoyed the great questions everyone asked and making this interactive.  Don’t forget to download and install the latest release of BIDS Helper if you don’t have it already.  As I stated at the start of the presentation this content was from a presentation that Dave Pendleton and I did back in January if you want to check out the complete presentation I have that available here – SQL Server Integration Services – Enterprise Manageability Follow-up.

I really would like to see these meetings to turn into more of a collaboration where we can discuss each others issues and work on solutions.  I think that would be very valuable to our community for us to interact more and bounce ideas off each other.  After all, we are all dealing with the same products and most likely have either experienced or read about the same types of issues.  If nothing else we might be able to share some insight that might lead to a solutionSmile


Here are a couple of other links of reference I made to the SSIS samples on CodePlex and also the Microsoft Data Warehouse Toolkit Book Website:

All of the resource links are included in the presentation download.  Plus there are a couple of SSIS videos you can watch from last year’s PASS National conference here:


Enjoy and let me know if you have any additional questions.  Thanks.

Don’t forget to check out the SQL Server 2008 R2 site and sign up to be notified when the CTP becomes available – http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx.



Posted in Integration Services | Tagged: , | 1 Comment »

Magenic Webinar May 28 – Business Dashboards

Posted by denglishbi on May 17, 2009

I will be doing a Magenic webinar in a couple of weeks following up my recent presentation at the PeopleReady BI Summit that we had here recently in the Minneapolis area – PeopleReady Business Intelligence Summit Follow-up.

Thursday, May 28th
11:00 PT, 1:00 CT, 2:00 ET
In the current economic climate, it is more important than ever for business stakeholders to have a pulse on what is happening in their organization. Implementing an Executive Dashboard allows an organization to more effectively and efficiently execute on strategy, improve business processes and manage key company metrics proactively. Register now…

Posted in Training | Leave a Comment »

Slicing Analysis Services (SSAS) Partitions and more

Posted by denglishbi on May 15, 2009

Just wanted to touch base on a couple of items with partitions referencing the ever popular Adventure Works 2008 sample Analysis Service project available to download from CodePlex here – SQL Server 2008 SR1 downloads – it appears that they have bundled the databases together now, used to be broken out by the operational and the data warehouse.  The sample project file will be part of this download for Analysis Services and will be located in the following directory if you go with the default setup – C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Analysis Services Project.

Now if you open up the Enterprise version you will see that the measures groups have multiple partitions defined for them.  One thing to note is that the counts are not properly updated and that aggregations have not been defined for all partitions in the cube.


If you have BIDS Helper installed you can go ahead and use the add-in to perform an update on the estimated counts for all of your objects.


Just a warning, if you are going against a very large database you might not want to perform this operation.


As a work around you could go ahead and simply update the estimated row count in the properties for the partition to provide Analysis Services an estimated row count when you define and generate the aggregations for the partitions (actually there is a Partition Count setting that you will see when you go through the aggregation design wizard that gets used by the algorithm when creating aggregations, so it is important to set these values).


After the estimated counts have been updated you will see that the counts are updated, but you would still need to design aggregations for the partitions where these have not been defined yet (you might need to save the cube file, close it, and reopen the file to see the updated counts).


To create the aggregations in SSAS 2008 you have to switch over to the new Aggregations tab in the cube.  You can then select the group that you want to design the aggregates for and walk through the wizard.


And you can generate the aggregates for all of the partitions at once.


Ok, now let’s get back to the partitions portion.  I am going to make a modification to the Internet Sales partition for 2004 and break this out into Q1 and then place Q2 in a separate partition.  This really doesn’t need to be done for the Adventure Works data since the volume of data is extremely small, but in a real world scenario this could definitely improve query performance (plus reduce processing time if you are just processing the latest quarter instead of the entire year).


I went ahead and modified the query of the existing 2004 partition so that the cutoff was less than 20040401 instead of 20041231 for OrderDatekey.  You need to be careful that you do not overlap the ranges, because there is no validation going on, so you could potentially include data that is already in an existing partition.  After I had modified the existing partition for 2004 and added the new partition I went ahead and updated the estimated counts.


Now that this is done let’s run a query against the cube and take a look at what is going on.  Here is the query that I will execute against the cube:

SELECT {Measures.[Internet Sales Amount], 
        Measures.[Internet Order Quantity]} ON 0,
NON EMPTY([Customer].[Customer Geography].[Country]*
        [Product].[Category].[Category]) ON 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Month].&[2004]&[4])


So the query executed and if we look at the Profiler trace we can see that the query actually touched multiple partitions for the measure group that we were querying information from.  The reason for this is because we have modified the basic partition from a single year.  If we would have left it at the year level it would have been fine, but since we are dividing this up into multiple parts now it does not know where to retrieve the data to satisfy the query.

Lets go back into the partitions in the cube and set the ‘Slice’ property for the partitions.  This is where you specify the tuple that defines the partition.


Now that we have this setup we will redeploy and run the query again.  You will need to define the ‘Slice’ property on each of the partitions in the measure group.


UPDATE (5/16/2009): fixed this picture (before I had highlighted the aggregation, not the partition)

Now that we have defined the ‘Slice’ for the partitions we see that our query only touches the partition that we are querying against and it was faster in response timeSmile  Granted this is a small set of data and using this doesn’t really make too much of a difference, but you can imagine what this would do to a very large dataset.  And if we switched over to reference the Fiscal hierarchy instead we would see the same results.

That is it for now, hope you enjoyed this little tip and I want to thank Siva Harinath and Howie Dickerman from Microsoft for their presentation at last year’s Microsoft BI Conference Designing High Performance Cubes in SQL Server 2008 Analysis Services where they pointed out this item.

Posted in Analysis Services | Tagged: | 3 Comments »

SQL Server 2008 Developer Toolkit feedback

Posted by denglishbi on May 14, 2009

Just wanted to post some feedback on the new SQL Server 2008 Developer Toolkit that was released yesterday on the Microsoft Downloads here – SQL Server 2008 Developer Training Kit.  I saw the demo of this I believe last year at the Microsoft 2008 Launch event.  This is a real cool demo that shows of the spatial data, filestream, and data mining capabilities in SQL Server 2008.  It also incorporates Silverlight technology for the video player.

Demo includes user login, profile information, security, mapping functionality with pinpoints for video references, Silverlight video player, and advertisements.


Ability to upload videos and specify a location on map to add pinpoint reference.  The videos are uploaded and stored in the database with the filestream functionality available in SQL Server 2008.


My video is now includedHot  This application also tracks viewing history, allows for comments to be added to videos, and for you to designate favorite videos.  You will also notice in the screenshot below that it performs calculation on the pinpoint using the geography information to determine the distance between two points.


In order to upload videos you need to install the Expression Encoder which is part of the required software detection setup (provides link to download and install during this process).  You will also need to modify the web.config file to set the UploadVideoEnabled appsetting to “True” to enable this feature.  The advertisement portion is setup to randomly pull a advertisement from the database. 

There is also a data mining exercise that uses some data from the Adventure Works database (sample Excel file provided) that you can go through to determine what demographic types would be interested in certain products.  You could then feed this information back into the application to perform some more strategic advertisements to the users.  The only thing about the exercise is that it stops at using the data mining add-ins that are available to download from the SQL Server Feature Pack.  It leaves it up to you to figure out how to push this information back into the database and leverage this in the application.

Example of data mining output where you can see that customers with some high school education and lower income levels tend to purchase helmets.


A couple of other things to point out would be that you will need to restore the SQL Server database provided as well as the Analysis Service database.  The SQL Server database is located in the following location:

C:SQL2008TrainingKitDemosAdventureWorksRacingCommunitysetup scriptstasksbakAdventureWorksRacingCommunity.bak

In order to do the data mining exercise you will need to have Analysis Services available and that is not part of SQL Express with the Advanced Services – this is only available in the Standard, Enterprise, or Developer Editions of SQL Server. 

There are more demos besides the web one that I show above, so be sure to check out everything to find out about the other cool features that you may or may not be using already if you are on SQL Server 2008 like groupingsets, merge, tablevalued parameters, etc.


So if you are interested in diving into these new features and want to walk through some demos of these new features download the SQL Server 2008 Developer Training Kit for yourself.

Posted in Downloads | Tagged: , | Leave a Comment »

BIDS Helper available

Posted by denglishbi on May 14, 2009

Just getting caught up on some of my blog readings this morning and came across a posting by Darren Gosbell stating that they have released a new version of BIDS Helper – BIDS Helper release 1.4.1.  The one cool thing is that they have included a version notification into the add-in so that you will be notified now when a new release is available.  Plus there is a feature to synch the description information from a SQL Server relational table or view into your SSAS dimensions (only works with SQL Server and not with named queries in the DSV).  They have also included smartdiff with Reporting Services now tooSmile

Here is a list of the changes that Darren points out in his posting:

This release also incorporates the following bug fixes and enhancements:

If you are not using this yet, do not wait, this is an invaluable addition into the BIDS tool and I highly recommend this for any developer using SSAS, SSIS, or SSRS.  Thanks again guys for putting this together.

One other thing to point out is that the URL references for all CodePlex items has changed so that what used to be http://codeplex.com/bidshelper is now http://bidshelper.codeplex.com.  The old URLs are still working now, but just wanted to point this out, because this might not always be the case.

Posted in Downloads | Tagged: , | 1 Comment »

SQL Server 2008 R2 Information and New Site

Posted by denglishbi on May 12, 2009

With TechEd 2009 currently going on the announcements are starting to come.  We have Windows 7 and Windows Server 2008 R2 coming out later this year and hopefully in the next couple of months the CTP of SQL Server 2008 R2 (‘Kilimanjaro’ with ‘Gemini’ and ‘Madison’ – along with other features) – actually they stated that it will be available in H2 2009.  Microsoft has created a new site with more features coming (like being able to register to receive notifications when the CTP is available) – SQL Server 2008 R2 site.

If you check out the site you can see some of the new features coming with brief descriptions and nice screenshots (click on the screenshots to enlarge them).  Features like improved central management for your multi SQL Server environment, Master Data Services (MDS – not MDM – Master Data Management – leveraging the Stratature acquisition), self-service analysis with the Gemini add-in, enhanced data model capabilities for self-service ad-hoc reporting (I believe this is for the relational and not OLAP, so interested what will be made available for OLAP and Analysis Services), new mapping capabilities with reporting, and much more.  These are just a few of the items and a couple of screenshots here.  Check out the official site for more information.


Gemini – high volume data analysis and ability to create data mash-ups to deploy to SharePoint (new data cleansing components available also that we are waiting to see – possibly brought in from Zoomix acquisition, but not sure if that is already going to be leveraged yet)


Monitor Analysis Services through SharePoint Administration


Mapping capabilities to be made available:)


Ability to create and share components in SSRS with a library of items (apparently an ability made possible with the 90 Degree acquisition – Review by Chris Webb here Radius90 from 90 Degree Software)

Stay connected with what is going on at TechEd 2009 with the following:

Posted in SQL Server | Tagged: | Leave a Comment »

Large Dimension Processing issue – File system error

Posted by denglishbi on May 11, 2009

A couple of weeks back I experienced an issue with a some what large dimension.  The dimension had around 15.5MM rows and it continued to error while processing the key attribute within the dimension in Analysis Services (SSAS) 2008.  I don’t believe this is a version issue at all and I was able to come up with a solution that resolved the processing issue which I will explain.

What was happening was that all of the other attribute hierarchies were processing fine, but every time the key attribute was being processed it would continue to error out in the same spot (around  60K rows) and it would return this error message (I swapped out a few names to protect the innocent):

File system error: While attempting to read information from disk, a read error occurred for physical file: \?D:Program FilesMicrosoft SQL ServerMSAS10.MSSQLSERVEROLAPTempMSMDCacheRowsetBadRows_1932_16629_jm313.tmp, logical file: . Errors in the OLAP storage engine: An error occurred while the ‘DimensionName Key’ attribute of the ‘DimensionName’ dimension from the ‘SSAS_DB’ database was being processed.

This processing was going against an Oracle data store, not that I feel that the issue was related to that, but just a disclaimer.

I started in by verifying the drive space for the Temp directory that gets utilized during the processing to ensure that enough space was allocated.  I also review the design of the dimension to make sure that options like hierarchies are not optimized and not ordered were being set properly.  I also verified the SSAS server properties to make sure that the EnternalCommandTimeout was adequate to make sure that the queries would not timeout during processing.  Unfortunately none of this seemed to make a difference and I continued to get the same error message.  I even verified the size on the asstore file for the dimension attribute thinking that maybe I was bumping up against the 4GB limit, but it was only around 355MB.

After searching the web and forums I came across an item that seemed like a potential game winner – Using ProcessingGroup Dimension property option ByTable vs. ByAttribute may error with string keys.  At this point I had nothing to lose and based on the hardware – Dell 2950s with 32GB of RAM and 2 quad core processors, plus we were running x64 software – I felt that this was a good solution.  I went ahead and switched the ProcessingGroup property on the dimension from the default ByAttribute to ByTable.  So basically a massive query gets executed and the results get cached which is then used to process all of the attribute hierarchies.  In running this process not only did the process complete successfully, but it actually shaved about 20% of the time off for processing the dimension.  You definitely need to read up on this process, make sure that you have the hardware in place, and do some proper testing.


Some of the other references that I looked at during this to fix the processing issue were the following:

As always you should refer to the whitepapers that have been provided on the SQL Server Best Practices site and a few key ones to point out are the following:

Posted in Analysis Services | Tagged: | 1 Comment »

PASSMN May 19, 2009 Monthly Meeting

Posted by denglishbi on May 6, 2009

The next PASSMN Minnesota SQL Server User Group meeting is coming up.  The meeting is on May 19 from 3:00 PM to 5:15 PM.  You can visit the local site to get registered at http://www.mnssug.org or click the link to Register Here.

This month the topics are:

SSIS – Team Development, Deployment and Configuration (Speaker: Dan English, Magenic)
SQL Server Integration Services (SSIS) provides enterprise-class scalability, advanced data-integration architecture, and high-performance processing. Many enterprise environments are developing centralized services and standards to support their SQL Server Integration Services platform. During this session you will learn considerations and solutions for team development and how to leverage the power of package configurations for deploying packages to multiple environments.

Dan English – Dan is a Principal Consultant with Magenic and has been developing with Microsoft technologies for over 12 years and has over 5 years experience with Data Warehousing and Business Intelligence. He has been working with SQL Server since version 6.5 and now with 2008 looking towards the Kilimanjaro release. Dan has screencasts of SQL Server 2008 and PerformancePoint Server on YouTube and Soapbox ( keyword search – Magenic) and is an avid blogger (http://denglishbi.spaces.live.com). Dan is fully certified with MS SQL Server 2005 and 2008 Business Intelligence. He enjoys keeping in contact with the community at large responding to forum postings on the Microsoft forums and SQL Server Central areas. Dan is also part of the PASSMN 2009 Executive Board.

Securing and Troubleshooting Service Broker (Speaker: Eric Strom, RELS)
Tired of reading “Hello World” articles about Service Broker? Looking for more information but not finding good resources on securing and troubleshooting Service Broker applications? In this presentation, I will discuss some good security practices and share some lessons I learned while implementing and troubleshooting a medium-sized Service Broker application. Expect to learn about securing and troubleshooting Service Broker. A basic understanding of the Service Broker architecture is helpful.

Eric Stromis a Senior Database Administrator at the RELS Companies and is a member of the PASSMN 2009 Executive Board. He has been a SQL Server DBA since 2001 and specializes in performance tuning. Eric studied database theory at the University of Minnesota to earn a B.S. in Computer Science. He loves exchanging ideas with peers and is always looking for a good discussion.

Just a reminder that the group meets on the 3rd Tuesday of each month.  If you are in the area and available to attend please join us and stop by and say ‘Hi’.

Posted in Training | Tagged: | Leave a Comment »