Welcome to my Blog!

After contemplating my contribution to the Business Intelligence community for several years, at last here it is.

I am new to writing for a blog but have certainly been inspired by the countless thousands of friends and colleagues who have dedicated their time to the community by blogging. Please enjoy my somewhat random thoughts of useful information and anecdotal sidebars.

Why Can’t We Use Our Existing Systems Databases for Business Intelligence

In an effort to start framing the many considerations for data souring of a Business Intelligence Solution, first we must look at some of the reasons it is not desirable to simply use existing solution databases.

As a simple starting point, the assumption is that the business intelligence solution that is being sought is envisioned to permit the business users a simple way to analyze, explore and report on information. The information is expected to improve business performance by delivering better decision making throughout your entire organization. Lastly, this improvement assumes that decisions made within the organization will be made as a result of careful consideration of the facts being presented by the information that our business intelligence solution will provide.

With the goals of a business intelligence solution briefly defined, let’s look at the common characteristics of existing systems database designs. These systems are most often storing information within On Line Transaction Processing (OLTP) databases or even flat files generated by business systems. These types of data structures have the following characteristics:

1. Manages complex transactions designed to enforce complex systems business process and rules
2. Key business logic is located within the systems application rather than the database
3. Another software development effort or a third party has created the systems database and as such both its meaning and structure may change over time
4. Often it is not permissible to make changes or add structures to the systems database
5. The design of the systems database may not have a well formed relational design that normalizes data and enforces integrity both at individual attribute levels and at relationship levels.
6. Contains historical data that does not match current business rules
7. Lacks significant meaningful documentation
8. Optimized for fast narrow query performance (e.g. order details for order number 123)
9. Many don’t contain information on historical changes to the information instead only reflecting the current state of the information
10. Usually have been developed in a sandbox with no consideration for consistency and sharing of business logic, entities, and attributes with other systems

 Clearly, with some or all of these characteristics involved, it is important that we find a more suitable source for the information we will present as part of a business intelligence solution. The best option would be to source data from these systems and extract, transform, combine, clean, normalize, denormalized, and otherwise make suitable the data for the business intelligence solutions use. Ultimately, this newly processed data would then be loaded into a data warehouse or data mart. It is the data warehouse or data mart that has been purposefully designed to overcome many of the OLTP data structures limitations for business intelligence usage.

Changing Service Account of SQL Server™ Report Server

The following is adapted from Microsoft KB Article 958999

In SQL Server™ 2005 Reporting Services, you can configure the report server to use the Service Credentials type for the database connection. You should not try to change the service account by using the Services.msc management console, as the operation may corrupt the encryption key that is used to protect sensitive information that is stored in the report server database. Microsoft recommends that you change the service account for the report server by using one of the following methods:

Method 1

Use Reporting Services Configuration Manager to change the service account for the report server.

Method 2

Use the Rsconfig.exe utility to change the service account for the report server. To do this, run the following command:

Rsconfig –c –s Server Name -d Database Name -u User Name -p Password -a Authentication Method

Note If the instance of SQL Server that hosts the report server database is a named instance, add the –i switch to specify the instance name.

Method 3

If method 1 and method 2 do not work, use the rskeymgmt utility. When you use this utility, you must back up the encrypted keys before you change the user account that is used to run the Report Server Windows service or the Report Server Web service, and then you must apply the keys that were backed up. To do this, follow these steps on the computer that is running the service:

1.       Start the Report Server Windows service and the Report Server Web service by using the user account that the service was running successfully for.

2.       Use the rskeymgmt command-line utility to back up the encryption keys. To do this, run the following command at the command prompt:

RSKeyMgmt -e -f FileName -p StrongPassword

3.       Use the rskeymgmt command-line utility to remove the reference to the existing keys. To do this, run the following command at the command prompt:

rskeymgmt -r InstallationID

Note Replace the InstallationID placeholder by using the installation ID that is provided in the InstallationID setting of the RSReportServer.config file. By default, the RSReportServer.config file is stored in the InstallationDrive :\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer folder.

4.       Stop Internet Information Services (IIS).

5.       Stop the Report Server Windows service.

6.       Change the user account that is used to run the Report Server Windows service or the Report Server Web service to the user account that you want.

7.       Start IIS.

8.       Start the Report Server Windows service.

9.       Use the rskeymgmt command-line utility to apply the encryption keys that were backed up in step 2. To do this, run the following command at the command prompt:

rskeymgmt -a -f FileName -p StrongPassword

Note Replace the FileName placeholder and the StrongPassword placeholder with the file name and the password that you used to back up the symmetric encryption keys in step 1.

Field Notes: Configuring Email in Reporting Services

The following needs to be in place:

1. Collaboration Data Objects (CDO) libraries (cdosys.dll) that are provided by the operating system
2. Local or remote Simple Mail Transfer Protocol (SMTP) server or SMTP forwarder (Not supported on Itanium Windows Server 2008)
3. Report Server service account must have Send As permission on the SMTP server to send mail
4. SMTP Port Opened (usually port 25)

 Limitations:

1. HTML rendering extension only supports UTF-8 encoding for email attachments
2. Default e-mail delivery extension does not provide support for digitally signing or encrypting outgoing mail messages
3. User-defined subscriptions that are created according to the permissions granted by the Manage individual subscriptions task contain a pre-set user name that is based on the domain user account. Workaround: modify RSReportServer.config setting SendEmailToUserAlias to False and setting DefaultHostName to the Domain Name System (DNS) name or IP address of the SMTP server or forwarder.

 Remote SMTP Server RSReportServer.config Options:

1. Connection (report server and an SMTP server or forwarder)
   • SendUsing method for sending messages
     • Network SMTP service or a local SMTP service pickup directory
     • For remote SMTP set to 2
2. SMTPServer the remote SMTP server or forwarder (required for remote SMTP)
3. From value that appears in the From: line (required for remote SMTP)
4. SMTPServerPort usually port 25
5. SMTPAuthenticate how report server connects to the remote SMTP
   • Default is 0 (or no authentication – Anonymous access)
   • Depending on your domain configuration, the report server and the SMTP server may need to be members of the same domain.
   • Set SMTPAuthenticate to 2 to send e-mail to restricted distribution lists

 Local SMTP Server RSReportServer.config Options:

1. Enable local SMTP service (not enabled by default)
2. SendUsing is set to 1.
3. SMTPServerPickupDirectory is set to a folder on the local drive.
4. Do not set SMTPServer
5. From value that appears in the From: line (required for local SMTP)

 To configure use the Reporting Services Configuration tool to minimally configure the extension.

To set advanced properties, you must edit the RSReportServer.config file.

If there is no SMTP server available or if you subsequently encounter report delivery errors that can be attributed to computer connection failures, you should switch to using a local SMTP service.

 See http://msdn.microsoft.com/en-us/library/ms345234.aspx for detailed configuration steps.

Reporting Services SharePoint Integrated Mode Overview

What is integrated mode? 

In SharePoint integrated mode, a report server must run within a SharePoint server farm. A SharePoint site provides the front-end access to report server content and operations. The report server provides all report processing and rendering. The advantage of integrated mode is a rich level of integration that allows you to access and manage report server content types using the application pages and data stores of a SharePoint Web application.

Users publish or upload reports, models, and data sources to a SharePoint library.

Reports, data sources, and data models in a SharePoint document library are stored as files in the SharePoint content database for that SharePoint document library. Files are stored in the .rdl, .rsds, and .smdl extensions. When a user requests to view or manage a report, data source, or data model, Reporting Services synchronizes its contents with the SharePoint content database.

Processing and Synchronization 

The report server continues to provide all data processing, rendering, and delivery. It also supports all scheduled report processing for snapshots and report history. 

 When you open a report from a SharePoint site, the ReportServer2006 endpoint connects to a report server, creates a session, prepares the report for processing, retrieves data, merges the report into the report layout, and displays it in the Report Viewer Web part. While the report is open, you can export it to different application formats, or interact with data by drilling into underlying numbers or clicking through to a related report. Export and report interaction operations are performed on the report server. 

 The report server synchronizes operations and data with Windows SharePoint Services and tracks information about the files it processes. The synchronization is performed because the report server database also maintains internal copies of the content files that are stored in the SharePoint content database. Storing internal copies improves performance by minimizing the number of times a file has to be sent to the report server for processing. Synchronization and verification checks ensure that the reports, models, and data sources are the same. When you modify properties or settings for any report server item, the change is stored in a SharePoint database and then copied to a report server database that provides internal storage to a report server. 

 This is all provided by the Reporting Services Add-in for SharePoint Technologies which:

• On Windows SharePoint Services, provides the ReportServer2006 proxy endpoint, a Report Viewer Web part, and application pages so that you can view, store, and manage report server content on a SharePoint site or farm.
• On Reporting Services, provides updated program files, a SOAP endpoint, and custom security and delivery extensions. The report server must be configured to run in SharePoint integrated mode, dedicated exclusively to supporting report access and delivery through your SharePoint site.

Integrated Mode Limitations:

• No support for linked reports 
• No use of Report Manager to manage reports
• Cannot integrate two or more report server instances that share the same report server database with different stand-alone server deployments of Windows SharePoint Services 3.0 or Office SharePoint Server 2007

Integrated Mode Installation Overview

1. Configure a report server to run in SharePoint integrated mode
2. Install a Reporting Services Add-in that adds infrastructure and application pages to a SharePoint Web application

Report Server Requirements:

• Requires that you dedicate a report server instance for integrated operations
• Server must satisfy the hardware and software requirements for SQL Server installations
• Report server database must be created for SharePoint integrated mode
• Report server must be installed on a computer that has an instance of a SharePoint product or technology (SSRS can be installed before or after installing the SharePoint product or technology instance)
• Same version of the SharePoint product or technology as installed on the report server computer

SharePoint Product and Technology Requirements:

• Windows SharePoint Services 3.0 or Office SharePoint Server 2007
• Reporting Services Add-in for SharePoint Technologies must be installed on each Web front-end in the server farm through which users will access reports
• 2 gigabytes of RAM on the Web front-end computer
• Anonymous access cannot be enabled on the SharePoint Web application

Database Requirements:

• SQL Server 2008 or SQL Server 2005 SP2
• Reporting Services cannot use the Embedded Edition (default SharePoint installed database) for its database
• The SQL Server 2008 Reporting Services Add-in for SharePoint Technologies requires a SQL Server 2008 Reporting Services (SSRS) database

Deployment Architectures:

Standalone Server Deployment

This deployment is the simplest to configure. Because all the components are installed on the same computer, it also uses the least amount of licenses. Specifically, Reporting Services and the Database Engine can be installed as a single licensed copy of SQL Server. In addition, Reporting Services can connect directly to the local instance of the SharePoint product or technology.

 

Two Server Distributed Deployment

The first computer hosts an instance of a SharePoint product or technology. The Reporting Services Add-in is installed on this computer.

The second computer hosts SQL Server Reporting Services and the Database Engine. Installing Reporting Services and the Database Engine on the same computer is useful if the benefits of running both components as a single licensed installation outweigh performance considerations.

Notice that when you run Reporting Services and the SharePoint product or technology on different computers, the report server must have a minimal installation of the same SharePoint product or technology. This means that if you installed Microsoft Office SharePoint Server on the first computer, and you must install a Web front-end of Office SharePoint Server on the second computer. The minimal installation provides a Web front-end that allows you to join a report server instance to the instance of the SharePoint product or technology. 

 

Three Server Distributed Deployment

The first computer hosts an instance of a SharePoint product or technology. The second computer hosts SQL Server Reporting Services. The third computer hosts an instance of the Database Engine.

In the illustration, notice that the Database Engine does not require a local installation of a report server or an instance of a SharePoint product or technology.

To use a remote database server with a deployment of a SharePoint product or technology, choose the Advanced installation option when installing an instance of a SharePoint product or technology, or use Central Administration to modify the database server setting. To select a remote database server for Reporting Services, use the Reporting Services Configuration tool. 

 

Three Server Mixed SQL Version Distributed Deployment 

The first computer hosts an instance of a SharePoint product or technology. The second computer hosts SQL Server Reporting Services using SQL Server 2008. The third computer hosts an instance of the SQL Server 2005 Database Engine used for the SharePoint Configuration and Content databases (and alternatively the Reporting Services 2008 Report Server database as this may be hosted in a SQL Server 2005 database).

In the illustration, notice that the Database Engine does not require a local installation of a report server or an instance of a SharePoint product or technology.

To use a remote database server with a deployment of a SharePoint product or technology, choose the Advanced installation option when installing an instance of a SharePoint product or technology, or use Central Administration to modify the database server setting. To select a remote database server for Reporting Services, use the Reporting Services Configuration tool.

 Key points regarding mixed SQL Versions: 

1.     The SSRS 2008 database can be deployed to the same or another database (not required to reside wherever the SharePoint databases are located)

2.     You can install the SSRS 2008 Add-Ins to SharePoint even though it’s databases are on a SQL 2005 (confirmed by Lukasz Pawlowski – Program Manager SSRS)

3.     You can host the SSRS 2008 content catalog (ReportServer and ReportServerTempDB databases) under either a 2005 or 2008 SQL Server instance

4.     For Reporting Services, if you have SSRS deployed to one server and SharePoint on another, regardless of the SSRS add-in, only the Reporting Services machine will require a SQL Server license

5.     The SharePoint Configuration and Content databases will require a separate SQL Server license if stored on a different server than the Reporting Services Report Server databases

 

Database Usage:

 SharePoint Configuration Database

Every SharePoint Web application has a configuration database that stores application settings. When you set up an instance of Windows SharePoint Services or Office SharePoint Server to work with a report server, you specify configuration settings in SharePoint Central Administration. The settings that you specify are stored in this database. Settings include the URL of the report server, account information for the Report Server service, information about the authentication provider used on the server, and site-level settings that limit or enable report history and logging.

SharePoint Content Database

Each SharePoint Web application has one or more content databases that store the documents and properties that are accessed and managed through the server. For a report server that is configured for SharePoint integrated mode, the SharePoint content database provides the primary storage for published reports, report models, shared data sources, resources, properties, and permissions.

Report Server Database and Report Server Temporary Database

Each report server instance or scale-out deployment uses a single report server database to store persistent data. Temporary data is isolated in a secondary database. This enables you to create different backup schedules and set different growth properties for each one. There is one temporary database for each report server database. It stores session data and temporary snapshots that are created only for subscription processing, interactive reporting, or report caching as a performance enhancement.

The data that is kept in a report server database includes schedules, subscriptions, and snapshots for report history or report execution. The report server database also maintains internal copies of the content files that are stored in the SharePoint content database. Storing internal copies improves performance by minimizing the number of times a file has to be sent to the report server for processing. Synchronization and verification checks ensure that the reports, models, and data sources are the same.

TSQL Handling Runtime Comparison Type Support

 

I thought I’d share a rediscovery of an old technique I have used to permit more flexible queries. Enjoy! You can extend this concept to permit combinations of from/thru ranges as well. Note that I am also using some SQL 2008 syntax for inline declaration and setting of variables

  (DECLARE @Var AS {DATATYPE} = ‘Value’)

Runtime Comparison Type Support

In order to support providing the user with the ability to determine what comparison is needed along with the value to be used for the comparison, two parameters will be required.

The first parameter allows the user to specify the comparison that they want. For numeric values this would include greater than, less than, equal to comparisons. For character values this would include equal to and like comparisons.

The second parameter allows the user to specify the value to be used for the comparison selected.

LIKE Usage Notes:

The LIKE comparison operator is primarily useful for character string comparisons. LIKE Determines whether or not a given character string matches a specified pattern. A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character string; wildcard characters, however, can be matched with arbitrary fragments of the character string.

Using wildcard characters makes the LIKE operator more flexible than using the = and != string comparison operators. If any of the arguments are not of character string data type, Microsoft® SQL Server™ converts them to character string data type, if possible.

 LIKE Wildcard Characters:

% = Any string of zero or more characters.

WHERE title LIKE ‘%computer%’ finds all book titles with the word ‘computer’ anywhere in the book title.

_ (underscore) = Any single character.

WHERE au_fname LIKE ‘_ean’ finds all four-letter first names that end with ean (Dean, Sean, and so on).

[ ] = Any single character within the specified range ([a-f]) or set ([abcdef]).

WHERE au_lname LIKE ‘[C-P]arsen’ finds author last names ending with arsen and beginning with any single character between C and P, for example Carsen, Larsen, Karsen, and so on.

[^] = Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

WHERE au_lname LIKE ‘de[^l]%’ all author last names beginning with de and where the following letter is not l.

*/

– NOTES:

– Using SQL 2008 DECLARE and SET inline syntax change to add separate SET statements for pre SQL 2008

– Using AdventureWorks2008 sample database

*/

– Numeric Comparison Supports >, <, and =

DECLARE @Operator AS VARCHAR(4) = ‘<’

DECLARE @Value AS NUMERIC(38,6) = 20000.00

 

– Character Comparison Supports = and LIKE

–DECLARE @Operator2 AS VARCHAR(4) = ‘=’

–DECLARE @Value2 AS NVARCHAR(25) = ‘CE1F-4E31-89′

DECLARE @Operator2 AS VARCHAR(4) = ‘Like’

DECLARE @Value2 AS NVARCHAR(25) = ‘C%’

 

SELECT [SalesOrderID]

      ,[SalesOrderDetailID]

      ,[CarrierTrackingNumber]

      ,[OrderQty]

      ,[ProductID]

      ,[SpecialOfferID]

      ,[UnitPrice]

      ,[UnitPriceDiscount]

      ,[LineTotal]

      ,[rowguid]

      ,[ModifiedDate]

  FROM [AdventureWorks2008].[Sales].[SalesOrderDetail]

  WHERE

    ((@Operator = ‘>’ AND [LineTotal] > @Value)

    OR (@Operator = ‘<’ AND [LineTotal] < @Value)

    OR (@Operator = ‘=’ AND [LineTotal] = @Value))

    AND

    ((@Operator2 = ‘=’ AND [CarrierTrackingNumber] = @Value2)

    OR (@Operator2 = ‘Like’ AND [CarrierTrackingNumber] LIKE @Value2))

GO

SSIS 2008 Logging and Package Configuration Objects FAQ

SSIS 2008 Logging to SQL Server

1. Can I change the dbo.sp_ssis_addlogentry system stored procedure to non-system stored procedure and if so how is this done?
 Yes – you must drop and recreate the procedure (and don’t run [sp_MS_marksystemobject] N’[dbo].[sp_ssis_addlogentry]‘)

2. Can I change the dbo.sysssislog system table to non-system table and if so how is this done?
 Yes – you must drop and recreate the table (and don’t run execute [sp_MS_marksystemobject] N’[sysssislog]‘)
 
3. Is the new SSIS 2008 logging stored procedure dbo.sp_ssis_addlogentry different than the SSIS 2005 logging table dbo.sp_dts_addlogentry?
 No

4. Is the new SSIS 2008 logging table dbo.sysssislog different than the SSIS 2005 logging table dbo.sysdtslog90?
 No, the schema is the same

5. Can I change the dbo.sp_ssis_addlogentry procedure to reference a different named table other than dbo.sysssislog?
 Yes – you must drop and recreate the procedure changing the referenced table name found after INSERT INTO
  I often name this table as Audit.ETLAuditSSISLog

6. Can I change the name of the dbo.sysdtslog90 table?
 Yes – you must drop and recreate the table
  Additionally, you must drop and recreate the SSIS 2008 logging stored procedure dbo.sp_ssis_addlogentry
  in order to change the referenced table name found after INSERT INTO
  I often name this table as Audit.ETLAuditSSISLog

7. Can I change the name of the dbo.sp_ssis_addlogentry procedure?
 No – SSIS will simply recreate the procedure if it is missing while the solution/package is configured to used SQL server logging.

SSIS 2008 Package Configuration using SQL Server

1. Is the new SSIS 2008 package configuration table different than the previous SSIS 2005 package configuration table?
 No – the schema is identical

2. Can I change the name of the package configuration table?
 Yes – you just have to change the SSIS reference in the Package Configuration Wizard (Configuration table)

SQL Server™ Schemas and Data Warehouse Solution Organization

Good organization helps us to develop efficiency and, even more importantly, increases our solutions understandability. Organizing the physical implementation of a business intelligence solution’s data tier within SQL Server™ is critical for organizing the overall business intelligence solution. The use of schemas to organize the business intelligence solution’s SQL Server™ database objects (tables, views, stored procedures, functions) has many benefits including:

1.       Clearly differentiates the objects based on their purpose (e.g. Dim = Dimension, Fact = Fact)

2.       Eliminates the usage of suffixes or prefixes in object names (e.g. DimEmployee, FactRetailSales)

3.       Enables control of object visibility by applying security to the schema (e.g. end users only access Dim and Fact schemas)

As somewhat of a relational purist I object to names of many database objects such as tables and views with prefixes to indicate their purpose. For example, many business intelligence or data warehousing efforts include table names like DimDate and DimEmployee for dimension tables and FactSales for fact tables.

Since SQL Server™ 2005, schemas are not just a user based security object and now form a distinct container of objects that exists independently of database users. Additional details on this separation can be found within the SQL Server™ Books on Line topic “User-Schema Separation”.

The great data warehousing visionary, Ralph Kimball, rightly espouses the need to separate concerns in our data tier. Specifically, he addresses the separation of the data staging area and the data presentation area. His colorful analogy of the areas of a restaurant is useful. Ralph likens the data staging area of a data warehouse, “… to the kitchen of a restaurant, where raw food products are transformed into a fine meal.” He also correctly cites that this area should only be accessed by skilled professionals and should not be having to respond to customer inquiries. Lastly, Ralph states that it just is not safe for the patrons to go into our staging area as they could, “…be injured by the dangerous equipment, hot surfaces, and sharp knifes they may encounter in the kitchen.” Of course to further extend this warning, Ralph includes my favorite rationale when he states that, “Besides, things happen in the kitchen that customers just shouldn’t be privy to.”

So what has this advice to do with SQL Server™ schemas? Simple really, by our use of schemas within our data tier we are able to physically and logically separate the data staging and data presentation areas of our data warehouse.

So as a simple example of how schemas help organize our efforts, I’ll share how I use them as part of my own solution design strategy. First, the tables in the presentation area of the data warehouse would be organized into either the Dim or Fact schemas. As such you can expect to find tables named (with the schema qualifier) Dim.Date and Dim.Employee for dimension tables and Fact.Sales for fact tables. Any additional views, stored procedures, functions, etc. that are related to the dimensions, they get created in the related object’s schema.

I use the following schemas:

·       Dim — used for all objects related to the dimensions in the model

·       Fact — used for all objects related to the facts in the model

·       Admin – used for all objects related to configuration that may be accessible to technical end users (e.g. SSIS package configuration table)

·       Audit — used to hold objects related to auditing (e.g. SSIS custom logging and error handling – except for the sysdtslog90 table for default SSIS logging which is assigned to the dbo schema and cannot be changed)

·       Etl — used to hold objects directly related to the ETL process including staging data (configuration values, processing directives, etc.)

·       Report – used to hold objects specifically used by reporting applications (such as views and stored procedures used directly by SSRS)

·       Olap – used to hold objects specifically used by analysis applications (such as views used directly by SSAS

·       Test – used to hold objects specifically used for testing (such as tear down and setup data and scripts including those that may modify configurations)

·       Util – used to hold objects that can be used generically by any part of the solution (such as a uf_TableOfDate function used to create values for a date dimension or to be used to join during direct relation queries)

I hope this information helps you to consider using schemas to help organize your data warehouse solutions.