Configuration Files in Reporting Services 2008

SQL Server Reporting Services 2008 has a number of useful configuration files. These are listed here. For clarity, I’ve missed the ‘.config’ extension from the following files information:

Configuration File

File Description

RSReportServer

Main configuration file for Report Server web part and Report Manager.

RSSrvPolicy

Policy Settings for the Report Server web service

RsMgrPolicy

Policy Settings for Report Manager application

RSPreviewPolicy

Policy settings for server extensions

ReportingServiceServices

Trace and Logging settings for the Report Server service

RSReportDesigner

Configuration settings for rendering devices for Report Designer

Web

ASP.NET settings for Report Server and Report Manager

The main configuration file for SSRS 2008 is the RSReportServer file. The settings in the Configuration Manager are stored in this file. This should not be regarded as a replacement for the Configuration Manager, however, because the Configuration Manager performs steps such as creating virtual directories, which are not covered in the configuration file. The security settings for SSRS 2008 can be found in RSSrvPolicy and RsMgrPolicy.

Note that this file has changed from SQL Server 2005, which used RSWebApplication and is deprecated for SQL Server 2008. To add this post to Twitter, please click here

Command Line Utilities in SSRS 2008

Although, in SSRS 2008, the Report Server configuration Manager configures the majority of settings, it is also possible to use command-line utilities. This can be useful for configuring SSRS 2008 scale-out deployments, sharing encryption keys and so on. The three main command lines are listed here, and this is then followed by details of the available parameters for each command.

RSConfig.exe

Configuration of connection properties between the Report Server to the repository database.

RSKeyMgmt.exe

Management of encryption keys via command-line

RS.exe

Scripting of report deployment

RSConfig Details

Here are the parameters for RSConfig.exe. Note that you will need to use quotation marks if any of the parameters e.g. database name contain a space:

Parameter

Description

/m

Remote server name; default is localhost

/i

Instance name, if a named instance is used

/c

Connection

/s

Database server name

/d

Database name

/a

Authentication method; either Windows or SQL authentication

/u

Username

/p

Password

/t

Trace log output goes to SSRS

/e

Unattended report execution; also needs /u and /p

RSKeyMgmt Details

Here are the parameters for RSKeyMgmt.exe:

Parameter

Description

/a

Restored and overwritten

/d

Deletes the key and encrypted data

/e

Extract key for backup to a file

/f

Filepath parameter for /a or /e parameter

/i

Named instance

/p

Password

/t

Trace log output goes to SSRS

/j

Adds a remote SSRS instance to the local Report Server database. /m (report server) and /n (instance name) are used with this parameter; /i refers to the local named instance

/r

Removes an SSRS instance from the scale-out deployment; use the GUID Installation ID to specify the instance

/u

Account name of the remote SSRS instance (optional)

/v

Password for the local admin of the remote SSRS instance (optional)

/m

Remote server name for /j

/n

Remote instance name for /j

RS Details

Here are the parameters for RS.exe, which is used for deploying reports:

Parameter

Description

/i

Input .rss file for execution

/s

URL to SSRS virtual directory

/u

Username

/p

Password

/l

Timeout in seconds; default is 60 seconds

/b

Batch command execution

/e

SOAP endpoint used; default is mgmt2005

/v

Global Variable mapping

/t

Trace log output goes to SSRS

To add this post to Twitter, please click here.

Item Level Role permissions in SSRS 2008

SSRS 2008 provides a number of default item-level roles, listed here:

Browser

May view folders, reports and subscribe to reports.

Content Manager

May manage content in the Report Server. This includes folders, reports and resources.

My Reports

May publish reports and linked reports; manage folders, reports and resources in a users ‘My Reports’ folder.

Publisher

May publish reports and linked reports to the Report Server.

Report Builder

May view report definitions.

The individual permissions of each role can be found in the following table. Hopefully this will help to see the increasing progression of permissions ascribed to each role.

Browser

Publisher

Report Builder

My Reports

Content Manager

Consume Reports

Yes

Yes

Create Linked Reports

Yes

Yes

Yes

Manage All Subscriptions

Yes

Manage data sources

Yes

Yes

Yes

Manage folders

Yes

Yes

Yes

Manage individual subscriptions

Yes

Yes

Yes

Yes

Manage Models

Yes

Yes

Manage Report History

Yes

Yes

Manage Reports

Yes

Yes

Yes

Manage Resources

Yes

Yes

Yes

Set Security for Individual Items

Yes

View Data Sources

Yes

Yes

View Folders

Yes

Yes

Yes

Yes

View Models

Yes

Yes

View Reports

Yes

Yes

Yes

Yes

View Resources

Yes

Yes

Yes

Yes

It is possible to create a custom role by copying an existing role, and modifying it. Note, however, that if you make amendments to the original role, this will not be cascaded to any amended copies of the role. To add this post to Twitter, please click here.

SSIS 2008 Default Role Security Summary Table

I sometimes have trouble remembering the details of the individual default roles in SSIS 2008. These can be found in the msdb database of SQL Server 2008, and are summarised in this table, which will hopefully help a bit:

SSIS 2008 msdb Role Name

View All Packages

Export

Execute All Packages

Execute All in SQL Agent

Write Features

db_ssisadmin

Yes

Yes

Yes

Yes

Delete/Change all packages

Import Packages

db_ssisoperator

Yes

Yes

Yes

Yes

None

db_ssisltduser

Own packages only

Own packages only

Own Packages only

Delete/change own packages

Import packages

The Windows Admin role can see the execution progress of packages that are currently executing, and can also stop these packages. However, note that the package execution will stop at the point of the next task, so the package execution abort activity may not occur immediately. To add this post to Twitter, please click here.

Dimensional Modelling Definitions

Normally I write about stuff I know about… but this blog is an exception! I am writing about this to try and teach myself something. I’m not normally a risk-taker so you’ll have to be patient if you correct me. I was reading an article, which discussed quantum mechanics, and its application to Oracle and data warehousing. This blog hopes to explore some of the points raised there, and apply the principles instead to Microsoft SQL Server 2008.

As we discussed in an earlier blog, data about facts are held in dimension tables. The ‘look up’ data is held in dimensional tables, and the actual facts themselves are held in a fact table. How can dimensional tables be configured to cope with data or ‘real life’ changes?

Dimensional modeling implements a methodology called ‘Slowly Changing Dimensions’ or SCD in order to accommodate ‘real life’ changes about facts. It is possible to correlate SCD methodology with other areas of knowledge or data representation in other philosophical realms such as quantum mechanics. There are three main types of commonly used SCD: 1, 2, and 3. Each type of SCD tries to allow the dimensional designer to cope with changes. The relationship between SCD types, quantum mechanics and related implementation in Microsoft can be found next.

The Type 1 methodology simply overwrites old data with new data. Historical data is not tracked at all, and can be used when data errors are being corrected, for example, if an attribute was mis-spelled. According to the Copenhagen interpretation of quantum mechanics, this would mean that every measurement causes a fundamental change in the state; a waveform collapse. One criticism of this rationale for SCD and the Copenhagen interpretation is that it does not allow the state change reversal to the original state. Applied to dimensional modelling, this means that the original information is lost. This destructive behaviour on the original record may not be suitable for many business environments, which may need to preserve the history of the state over time. In Microsoft SSIS, the Type 1 implementation of the Slowly Changing Dimension transformation directs these rows to an output named Changing Attributes Updates Output.

In a Type 2 methodology, in terms of data warehouses, existing records cannot be changed. However, it is possible to allow an update to show that the record is current or expired. A change in an attribute results in the creation of new records, which have a primary key. Thus, a new record is created as a result of an attribute change as opposed to the update of an existing record. Since a new record is inserted each time a change is made, then the history is preserved.

In dimensional modelling, Type 2 corresponds to the Many Worlds interpretation of quantum mechanics. It allows events to reside next to one another by catering for many possibilities or worlds, which are not correlated with one another. This is similar to the Type 2 SCD since it serves as an analogy to the situation where a new situation or record is created to correspond to the new state. The analogy breaks down, however, since the ‘Many Worlds’ interpretation holds that the scenarios are unrelated, but in dimensional modeling the records are related to one another since the records share the same Business Key, even though they have a different Surrogate Key. In terms of future ‘worlds’, future events against a dimension record will be owned by the latest dimension record that changed via the Business Key, which ties the multiple instances together; so a relation will exist.

In SSIS, the Type 2 implementation of Slowly Changing Dimension transformation directs these rows to two outputs: Historical Attribute Inserts Output and New Output. Fixed attribute changes indicate the column value must not change. The Slowly Changing Dimension transformation detects changes and can direct the rows with changes to an output named Fixed Attribute Output.

In a Type 3 methodology, this involves a combination of the previous two interpretations. The Type 3 SCD can be expanded to include Type 2 functionality. At its lowest level, it is a case of duplicating attributes to have a ‘Current’ and ‘Previous’ version, and then using logic to set values where needed. In quantum mechanics, an analogous interpretation exists which is known as Consistent Histories interpretation of quantum mechanics. This interpretation holds that various worlds or states are possible, but it does not hold that an unending variation of potential multiple worlds can be produced from these states. In other words, the only outcome that is considered is the outcome that actually took place; this can be called the only true or real outcome.

Microsoft SSIS does not support Type 3 changes easily because it involves changes to the dimension table. However, it is possible to capture the data values that are candidates for Type 3 changes by identifying columns with a fixed attribute update type.

Microsoft SSIS does permit the implementation of Type 1 and Type 2 SCD, but care needs to be taken with the implementation of Type 3 methodology. Microsoft SQL Server 2005 Service Pack 2 (SP2), via package logging, does permit the additional logging of calls to the SCD transformation, which allows increased capacity to troubleshoot connections, commands and queries.

To summarise, the SCD types don’t correlate exactly to the various quantum mechanics interpretation of events; but I learned something along the way and perhaps it might spark your interest too?

Improving Data Flow Performance in SSIS 2008

This information on improving Data Flow originally came from the Microsoft site, but it is intended here to distill the main points to make the Data Flow review more easy to conduct. There are two main ways of improving performance in Data Flows; one is amending the Data Flow property itself, and the other is to review the properties of individual components of the Data Flow.

Configuring Data Flow properties

Item

Definition

BufferTempStoragePath

locations for temporary storage of buffer data; can make this faster by overriding the default directories (TEMP/TMP) so it points to a faster hard drive, for example

BLOBTempStoragePath

columns that contain binary large object (BLOB) data; can make this faster by overriding the default directories (TEMP/TMP) so it points to a faster hard drive, for example

DefaultBufferSize

Default buffer size is 10Mb; this could be increased to 100Mb

DefaultBufferMaxRows

maximum number of rows in each buffer; the default is 10000, but could be increased

EngineThreads

This setting is a property of the Data Flow Task; simultaneous threads running in parallel. General rule is not to run more threads in parallel than the number of available processors

Parallel Execution

Use MaxConcurrentExecutables and EngineThreads

Run Tasks Simultaneously

MaxConcurrentExcecutables is a property of the package

Default = -1 (Physical or Logical Processors + 2)

RunInOptimizedMode

This actively removes unused components such as columns, output in order to increase performance.

Adjusting Buffer Size

Initial buffer size = (estimated the size of a single row of data x DefaultBufferMaxRows )

Initial buffer size > DefaultBufferSize

Number of rows reduced

Initial buffer size < Minimum Buffer Size

Number of rows increased

(Initial buffer size > Minimum buffer size) & (Initial buffer size < default buffer size)

(estimated row size x the value of DefaultBufferMaxRows)

  • Reduce row size by removing unnecessary columns and use appropriate data types
  • Use a smaller number of large buffers, rather than a larger number of small buffers; review DefaultBufferSize and DefaultBufferMaxRows while reviewing performance and the BufferSizeTuning event.
  • Be careful of increasing buffer size too much, in case paging starts to occur. Check the Buffers Spooled performance counter

Configuring the Data Flow Components

The following settings refer to the components of the Data Flow task, since it is also possible to optimise these individual elements.

Item

Description

Optimise Queries

Revise queries to retrieve only the required columns i.e. revise select * from … note that RunInOptimizedMode can remove unnecessary columns if required.

Avoid unnecessary Sort operations

Could perhaps revise Sort upstream

  • Set the IsSorted property on the output of an upstream data flow component to True.
  • Specify the sort key columns on which the data is sorted.
  • Reduce sort operations in number e.g. use multicast rather than two Sorts later on

Source

SQL statement performs better than ‘table or view’ retrieval

Transformation

Aggregate Transformations

Keys, KeysScale, CountDistinctKeys, and CountDistinctScale
properties can help in memory preallocation

One Aggregation is better than multiple transformations.

Fuzzy Lookup and Fuzzy Grouping

Lookup

Select only the required columns, not select *

Merge Join Transformations

MaxBuffersPerInput – fine-tune memory by indicating the max number of active buffers for each input; default is 5 but SSIS may temporarily increase this value

SCD

OLE DB that uses UPDATES are slower; it’s better to use Execute SQL with updates

Destinations

SQL Server destinations are better if you are loading to the same computer

Check whether the destination is slower, or the package – use Row Count as a temporary substitute to see what happens

To add this post to Twitter, please click here.Hope that helps a bit!
Jen x

A Table Summary of Key SSIS 2008 Data Transformations

Data Transformations change data between data sources input and data destination output. In SSIS 2008, there are quite a few to choose from, so the choice of transformation is not always immediately clear. Further, the presence or absence of an error output may affect the choice of transformation. Here, some of the key transformations are provided, along with the input they will take (single or multiple) and whether an error output is part of the transformation or not.

Row Level Transformations

The following transformations act on at the row level, without requiring other rows to operate.

Data Transformation Name

Input

When to use it…

Support error output?

Character Map

One input

String data only; case change, character width

Yes

Copy

One input

Copy of an existing column

No

Data Conversion

One input

Changing data type for columns

Yes

Derived Column

One input

More complex transformations using variables and expressions

Yes

Multiple Input and Output Transformations

Other Transformations operate with more than multiple data inputs or multiple data outputs. Note that Merge and Merge join require sorted inputs, and can match more than one row over join columns. The sort position is retained after the transformation is completed.

Union All stacks similarly-structured rows together, rather than knitting rows together over a join as Merge does. Further, it does not require sorted inputs.

Data Transformation Name

Input

When to use it…

Support error output?

Conditional Split

One data stream

Divides the data stream into separate pieces dependent on some condition;

sends the output row to a distinct output stream

Yes

Multicast Transform

One data stream

Replicates the data stream;

sends the output row to every output stream

No

Union All

Multiple data stream

Unite unsorted multiple data streams that have similar structures

No

Merge

Multiple data stream

Unite sorted two data streams that have similar structures and matching metadata; easier for ordering output

No

Merge Join

Multiple data stream

Unites sorted two data streams; this can do left, full or inner joins. Merges rows based on common columns.

No

Lookup

Multiple data stream

This is useful for transforming a source code to something user-friendly. Match/No Match output

Yes

To add this post to Twitter, please click here.
Hopefully that will help someone, somewhere to remember the details for the exam J