Denali CTP 3 is out!

Not exactly a big fanfare but a story in The Register reveals that the SQL Server Denali Community Technology Preview 3 is available for download here: (it weighs in at about 2GB). More details from Microsoft here and what is included here. From what I understand this is the last CTP/beta before it goes RTM.

MDS - Access Denied

We're having a play with SQL Server Master Data Services at MITIE with a view to using them to manage some of our analytical Master Data - currently done via scripting. One of the first challenges was security. There is a set of instructions here but we still managed to get stuck. We configured a user as an administrator for all models as described here but still got an "Access is Denied" message when trying to log in with that user. Note that the system administator has to be a user and not a group. The answer is elsewhere in the MSDN documentation;

Step one
Under User and Group Permissions click the down arrow > Edit > Functions. Here I'm showing the group page, but the user page works the same way:

Step two
Click the pencil icon to allow for editing

Step three
Add the functions as appropriate. These map onto the 'buttons' that are rendered in the MDS home-page, and should be self-explanatory. To add all of them simply click the button highlighted below:

Your user or group should now be able to log into MDS, with no "Access is Denied" message. Other permissions can be established as per the MSDN documentation.

'Invalid characters' in Analysis Services cube

We ran into the following error whilst trying to query our development cube today;

The server sent an unrecognizable response.
' ', hexadecimal value 0x19, is an invalid character.

Rather peculiar I think you'd agree! Turns out the data in question was a Unicode field with a ASCII control character in it. 0x19 is ASCII character 25 (19 in hexadecimal) - which stands for EM or End of Medium apparently.

Analysis Services quite happily processed and stored the data - however any attempt to query it was defeated despite the fact that the associated dimension attribute was also Unicode (ie a WChar). This, I realised, was due to the fact that clients communicate with Analysis Services using the XML for Analysis (XMLA) protocol. As the name suggests its based on XML which cannot encode some unicode characters - including ASCII 25!

How on earth it got into our source system was a mystery - but we had to do something about it.

Fortunately there's a very useful option in Analysis Services for dealing with this sort of issue; the InvalidXMLCharacters property. You can find it by right-clicking on an attribute and selecting Properties:

Then expand the 'NameColumn' property and under that you'll find the InvalidXMLCharacters property:

Essentially it tells Analysis Services what to do with characters that aren't supported by XML

It has three settings:

Preserve	Keep invalid characters. The default
Replace	Replace with a '?'
Remove	Remove the character from the query results

We opted for 'Remove' which resolved our problem. Its worth considering setting this on all unicode and non-unicode text attributes as potentially any off these could include invalid XML characters. It really depends on how well you know - and trust - your data source...

Configure Reporting Services to use proxy for Bing maps

If you work somewhere that uses a proxy server to connect you and your servers to the internet you will need to configure Reporting Services to use said proxy in order to use the Bing maps layer. Otherwise you will see something along the lines of the following message in your report, instead of a map:

The remote name could not be resolved: 'dev.virtualearth.net'

The solution is easy;

Step one
Navigate to the webconfig file for the instance of Reporting Services you want to proxy out to the internet. Typically for 2008 R2 this will be:

<drive name>\Program Files\Microsoft SQL Server\MSRS10_50.<instance name>\Reporting Services\ReportServer\web.config

Step two
Add the following xml fragment to the file - I usually add it in-between the</runtime> tag and the </configuration> tag, at the end of the file:

<system.net>

        <defaultProxy enabled="true" useDefaultCredentials="true">

            <proxy bypassonlocal="True" proxyaddress="http://<proxy server>"  />

        </defaultProxy>
    </system.net>

Save the file.

This of course assumes your server is allowed to proxy out in the first place (which it will need for Bing maps integration). You can test this by logging onto the server and firing up a browser or by asking your friendly local sysadmin...

SQL Server Denali: SSIS Configuration and Package Execution

One of the first things you note from the release notes are the changes to SSIS configurations in Denali. This is excellent news because the use of configurations in previous versions of SSIS weren't without their problems. The inimitable Jamie Thompson has a series of excellent posts about it which I won't repeat here, plus there is additional information on the MSDN Wiki.

Briefly however, the new deployment model involves deployment to a specially constructed SQL DB otherwise known as 'SSISDB' (at least in this CTP) which appears under its own node in SSMS - with its own GUI options. The SSISDB database is the SSIS 'Catalog' and includes the concepts of environments and parameters to allow run-time configuration of packages. Therefore SSIS is now 'environment aware' and there is no need to have to store configuration locations outside of SSIS in Windows environment variables and so on - which was always a bit awkward. Parameters can now be created on a project level removing the need to create Parent package configurations. All of this can be administered by using views and stored procedures, the API or the aforementioned SSMS.

However the one thing I was left wondering was: 'how do I execute a package under the new configuration model without using the SSMS GUI?'. DTExec still exists but does not appear to have options to use the new environment variables (at least in the current CTP). Happily perusal of the new MSDN documentation reveals the answer.

Firstly create and environment called 'env1', deploy an SSIS project called 'Project' with a package called 'Package1' to a folder called 'Test'. See Jamie's blog to set these up. Then do the following. First of all you need to retrieve the appropriate reference id for the project and environment:

DECLARE @execution_id BIGINT

DECLARE @reference_id BIGINT



SELECT @reference_id = e.reference_id

FROM   catalog.project_environments e

       INNER JOIN catalog.projects p

         ON e.project_id = p.project_id

WHERE  e.environment_name = 'env1'

       AND p.name = 'Project'

Then we need to tell the SSIS Catalog that we want to execute the package with the particular reference:

EXECUTE catalog.create_execution

  @folder_name = 'Test',

  @project_name = 'Project',

  @package_name = 'Package1.dtsx',

  @reference_id = @reference_id,

  @execution_id = @execution_id OUTPUT

Finally tell SSIS to execute using the execution id created above:

EXECUTE catalog.start_execution @execution_id = @execution_id

A couple of notes:

1. The package referenced by 'create_execution' has to have the 'Entry-point package' property set in Visual Studio:

2. There is an unused parameter in the above call to 'create_execution' - '@use32bitruntime' - use this on 64-bit environments if you want to force SSIS to execute in 32-bit mode.

This leaves two overall configuration options as I see it. You could either:

1. Have one SSIS 'environment' for every application you have. This means that the SSIS environment referenced at runtime would be the same across Dev, Test, Live and so on. Obviously the parameters themselves would be different!

2. Have all of your application/network environments (ie Dev, Test, Live) created in every instance of the SSIS Catalog and reference the appropriate one at runtime. This is obviously more flexible but probably carries more risk of running the wrong configuration on the wrong environment - with potentially serious consequences!

SQL Server Denali: First Impressions

So I, along with a great many others, have been playing around with Denali CTP1 over the last few days and reading about the many announcements made at SQL PASS. Its become clear that its probably the biggest release since SQL Server 2005 in terms of new features, with possibly the biggest splash being made by 'Project Crescent' (the reporting tool I mentioned in my previous Denali post) and the Business Intelligence Semantic Model (BISM).

They're both described in quite a bit of detail here, and the introduction of BISM has provoked some misgivings in the developer community. However the introduction of the Powerpivot engine (otherwise known as Vertipaq) to developer tools, has to be a good thing even if they haven't managed to integrate it with Analysis Services. Potentially this could even mean the end of data warehousing and ETL, although this has been tried before...

Project Crescent is Silverlight-based and looks like the sort of end-user BI tool those of us working with MS BI software have been waiting for since Reporting Services came out. There's a very brief demo of it here.

On a more detailed note as I said in my previous post you won't be able to use the new Denali CTP1 projects if you have 2008 or 2008 R2 installed and I found that I had to uninstall the 2008 Team Foundation Client as well. However if you're using Windows 7 PowerShell 2.0 is already installed.

Additionally on starting SQL Server Management Studio (SSMS) I found the Create Catalog on the Integration Services folder (of which more in a later post) greyed out as follows:

When I tried to connect to the (now superceded) Integration Services Server I got the following 'Access denied' error:

On Windows 7 both problems were solved by simply running SSMS in Administrator mode by right-clicking on the SSMS icon and selecting 'Run as Administrator':

More soon...

Denali available for download

As promised by MS the first CTP of the next version of SQL Server is available for download now. What's new? Check here and here.

One thing that immediately jumps out at me is the following;

Business users will gain greater agility and empowerment with a new highly interactive, web-based data exploration and visualization solution. Introducing new and engaging ways to discover insights through a drag-and-drop interface, smart and powerful data querying and interactive storyboarding to allow users to quickly create and share visual presentations of large datasets.

Sounds intriguing - but I'm not sure its in the CTP. Needless to say I'm downloading now...

Update:

Its worth checking the release notes here. There is a dependency on Powershell 2.0 - and more importantly no support for side-by-side installations with 2008/R2. There is also no support for XP regardless of SP version.