Agile BI

I’ve been playing around with the SQL Azure CTP for a little bit, and generally, it’s going well. However, as with any new technology, there are plenty of things to learn.  I’m planning a series of posts around SQL Azure to share what I’m learning about it. And yes, there will be some SSIS thrown in there, too – what good is a database in the cloud if you can’t get your data in and out?

One of the first things I did was create a new database and some tables (rather obvious, I suppose – you can’t really do much in SQL without that). Something that you will likely encounter immediately when creating tables is the difference between what you can do in SQL Server and what SQL Azure supports. Primarily, it’s related to physical options affecting the storage. As a comparison, here’s the script that SQL Server Management Studio generates if you right-click on a table and choose Script Table As…Create To.

USE [MyDatabase]

GO 

SET ANSI_NULLS ON

GO 

SET QUOTED_IDENTIFIER ON

GO 

SET ANSI_PADDING ON

GO 

CREATE TABLE [dbo].[MyTable](

    [MyKey] [int] IDENTITY(1,1) NOT NULL,

    [MyString] [varchar](30) NOT NULL,

    [UpdateID] [int] NOT NULL,

    [UpdateDate] [datetime] NOT NULL,

 CONSTRAINT [pkMyTable] PRIMARY KEY CLUSTERED (

    [MyKey] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],

 CONSTRAINT [akMyTable] UNIQUE NONCLUSTERED (

    [MyString] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY])

 ON [PRIMARY] 

GO 

SET ANSI_PADDING OFF

GO 

ALTER TABLE [dbo].[MyTable] ADD

  CONSTRAINT [dfUpdateDate]  DEFAULT (getdate()) FOR [UpdateDate]

GO

Here’s the same CREATE TABLE script, but trimmed down to just the items SQL Azure supports:

SET QUOTED_IDENTIFIER ON

GO 

CREATE TABLE [dbo].[MyTable](

    [MyKey] [int] IDENTITY(1,1) NOT NULL,

    [MyString] [varchar](30) NOT NULL,

    [UpdateID] [int] NOT NULL,

    [UpdateDate] [datetime] NOT NULL,

 CONSTRAINT [pkMyTable] PRIMARY KEY CLUSTERED (

    [MyKey] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF),

 CONSTRAINT [akMyTable] UNIQUE NONCLUSTERED (

    [MyString] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF))

GO

ALTER TABLE [dbo].[MyTable] ADD

  CONSTRAINT [dfUpdateDate]  DEFAULT (getdate()) FOR [UpdateDate]

GO

This is all documented in the SQL Azure documentation on MSDN, under the Transaction SQL Reference. And, as expected, most of the options that aren’t supported are related to physical storage.

One item that does stand out a bit, though, is USE. The USE statement is supported, but only if it references the current database, as in USE MyDatabase when you are connected to MyDatabase. Executing USE MyOtherDatabase when you are connected to MyDatabase will result in an error. Instead, you have to disconnect from MyDatabase and connect to MyOtherDatabase. It does make some sense not to allow users to switch databases in a multi-tenant model (I can picture all sorts of interesting hacks being created if that were possible). I do wonder, though, why it was included at all, as it is fairly useless in it’s current form. Maybe a future enhancement?

Anyway, if you are interested in SQL Azure, what this space for more updates as I continue working with it.

I’m going to be presenting two sessions at the SQL Server Best Practices Conference on August 24-26 in Washington, DC. This conference has a different focus than many of the technical conferences that I’ve presented at in the past. Instead of technical deep dives, the focus is on providing attendees with information on the decision points they are likely to encounter in their projects, and guidance on how to choose the best course of action at those points. It promises to be a very interesting conference.

I’ll be presenting on two topics. The first, Unit Testing SSIS Packages, is a topic I’ve presented on before, but not with this focus. I’m a big fan of unit testing and the test driven development (TDD) model, but I’ve found that there are numerous challenges in applying this approach to SSIS. So my session will focus on the best practices you can use to make this successful.

My other topic, SQL Server BI in the Cloud, is a new one for me (and for pretty much everyone, considering that SQL Azure is still pre-CTP). However, I’ve been doing some work with it and other cloud based models for BI, and there are some clear key decision points that you need to consider when embarking on this type of project. I’m really looking forward to this one, as it’s a new area, and that always prompts good discussions.

If you are attending, please consider dropping by for my sessions. If you’re not registered for the conference, there’s still time, and there’s a great lineup of speakers.

Setting up security for SSRS in SharePoint integrated mode can be a bit tricky, particularly if you want to set up some of your users to only be able to run reports, but not to be able to modify or change them. If you give the users the standard Contribute permission level in SharePoint, they have the ability to view and execute reports, but they can also delete existing reports or add new ones. If you assign only the Read permission level to the users, they won’t even be able to see the reports in the document library.

Based on my experiences with it, there seems to be a requirement that users have the Edit Items permission, even if you want them to only view reports.  Till that’s resolved, this will get you to the minimal set of permissions needed. To enable the desired behavior, you have to create a new permission that has a specific set of privileges, including the View Item and Edit Item permissions. Note that not all of the permissions listed below are 100% required, but they are recommended for correct SharePoint operation.

To set it up so users can see and run reports, but not add or delete them, do the following:

1. Go to the site that contains the Reports Library that you want to secure.
2. Select Site Actions..Site Settings..Modify All Site Settings.
3. Under Users and Permissions, select Advanced Permissions.
4. Choose Settings..Permission Levels.
5. Choose Add a Permission Level.
6. Give the new permission level a name like "Report Reader", a description like "Minimal permissions to view reports.", and select the following permissions:
   1. List Permissions:
      1. Edit Items (hopefully, the requirement for this will be removed soon, as it gives the user the ability to edit the name and description of the report)
      2. View Items
      3. Open Items
      4. View Versions
      5. View Application Pages
   2. Site Permissions:
      1. View Pages
      2. Browse User Information
      3. Use Remote Interfaces
      4. Open
7. Click Create to create the new permission level.

Now, you can create a new SharePoint group that uses the Report Reader permission level, or assign it directly to a user.

This seems to be either a bug in the product or a configuration problem on my part. However, if it’s a configuration problem, it’s occurring on all the SharePoint boxes I have access to, and I can’t find any information on how to fix it. If you do have any information on how to avoid giving the Edit Items permission, please post it here in the comments.

It’s generally accepted* that when you are using an OLE DB Source or an ADO.NET Source, you should specify all the columns, rather than selecting the table from the dropdown, or using a SELECT * SQL statement.

If you’re like me, then you probably don’t have the columns in all your tables memorized, and you don’t like typing, so getting a complete list of columns is a bit painful. You either have to use the query designer to create the whole query, or go to SSMS and create the SELECT statement from there. However, there is a simpler way. Just type “SELECT * FROM [your table here]” into the source component’s SQL Command box, and then click the Build Query button. The query designer will automatically expand the * into the specific column names. Just click OK as soon as the designer opens, and you end up with a fully populated column list.

*For some of the reasons why this is generally accepted from an SSIS perspective, see these posts from Jamie Thomson: http://blogs.conchango.com/jamiethomson/archive/2006/02/21/SSIS_3A00_-SELECT-_2A002E002E002E00_-or-select-from-a-dropdown-in-an-OLE-DB-Source-component_3F00_.aspx and https://blogs.conchango.com/jamiethomson/archive/2006/01/05/2554.aspx (point #4). If you want reasons from a general SQL standpoint, search for “never use ”SELECT *” in SQL” in your favorite search engine.

I’ll be presenting at the Columbia, SC PASS user group on July 7. I’ll be speaking on “Getting Started with Analysis Services 2008”, which is a demo heavy introduction to using SQL Server Analysis Services 2008. If you want to use SSAS, but aren’t sure quite where to start, this session should help.

If you are in the Columbia area on July 7, please drop by. You can find more information here: