Creating a Cube and Dimension using SSAS

In my previous article, we took an Overview of SSAS and its various components. In this article, we will create a cube by using Business Intelligence Development Studio (BIDS).

We will continue with the example we discussed with SQL Server Integration Services where we created a database with the help of Import Export wizard

1. In the existing solution named DemoBeginning, add an Analysis Services Project and name it DemoCube.

2. In the Solution Explorer, right click on Data Sources and click on New Data Source. This action launches the Data Source Wizard. Connect to the database created earlier named DemoDB. Select the impersonation information as ‘Use the Service Account’. Keep the default name and click Finish.

3. In the Solution Explorer, right click on Data Source View (DSV) and select New Data Source. Select 3 tables from the Production schema and SalesOrderDetail from Sales schema. We will fetch the last table in a short while. Keep the default name for DSV and click Finish. In this DSV you will see that no tables are related to each other.

Note we did not fetch any keys or relationships from the original database to the newly created database.

4. We will create the logical relationships in the DSV. Right click on each table from the Production schema and make ProductID, ProductSubCategoryID and ProductCategoryID as logical primary keys. To create relationships, drag ProductSubCategoryID from Product table and drop on ProductSubCategory. Verify the relationship is as follows:

Similarly drag ProductCategoryID from ProductSubCategory table and drop it on ProductCategory table. Drag ProductID from SalesOrderDetail and create the relationship by dropping it on ProductID of Product table. We have now created primary keys in dimension tables and subsequent foreign keys in fact tables.

5. Dimensions for cube can be created in 2 ways. One is by right clicking on Dimension in Solution Explorer and the other when we create a cube, the dimensions automatically get created. Right click on the cube in Solution Explorer and start the cube wizard. Select existing tables from the ‘Select Creation Method’ screen. Click Next.

6. Click on the Suggest button from the next screen and you will see that the table named SalesOrderDetails is picked as the table with measures. This suggestion is based on the relationship we created in the previous step. Click Next.

7. Select Order Qty, Unit Price and Line Total as the only measures and click Next

8. Click Next, enter the name DemoCube and click Finish. The result will be as shown below.

You can now see the relationship which we created.

9. Right click on the project and select properties. Click Deployment tab and enter your server name. For default instance you can keep it as localhost; for named instance it will be <machine name>\<instance name>. Select Build, Deploy DemoCube.

10. After successful deployment ,select the Browser tab for cube. If you drag and drop Unit Price, Line Total to the data area and Product category Id to the row area, you will see the result as follows.

This result shows category id which is not very user friendly, it will be better if we see the name instead.

11. Go to solution explorer and double click on Product dimension.

Go to properties for Product Id and change NameColumn property as shown below

Similarly change the NameColumn for Product Subcategory ID and Product Category Id.

Create a hierarchy named Product Category as follows:

We need to process the dimension with the changed properties. Now the previous result is as shown below

12. We can format the Line Total by selecting Cube structure tab, select Line Total, select properties and specify FormatString as currency with the following result:

13. We get a warning for the dimension as “Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies”. To implement this best practice, select each attributes and change its AttributeHierarchyVisible property to False.

14. Let us add one more dimension for Time. Select DSV, right click on the empty area and select Add/Remove tables, add SalesOrderHeader and click. Specify SalesOrderId from SalesOrderDetails as logical primary key and establish relationship to the newly added table.

15. Right click on Dimensions in solution explorer and add a new dimension. Select ‘Generate a Time Table’ on server, and enter details as shown below:

Click Next, Next again and finally Finish. Verify that the wizard automatically creates one hierarchy with year, quarter, month and Data as attributes

16. We need to add this dimension to the cube, so select the cube structure tab, right click on the empty area for dimensions and select add a cube dimension and select Time. Process and deploy the changes.

17. Go to Browser tab, click reconnect and you will see the newly created dimension added.

18. We can filter the data in following manner

In next article, we will discuss an overview of SQL Server Reporting Services and create a report based on this cube.

Feedback:

Overview of SQL Server Analysis Services (SSAS)

In a previous article, we discussed SQL Server Integration Services, its components and how to create package by using Import Export Wizard. In this article we will take an overview of SSAS and its various components.

With SSIS, we discussed how data can be fetched and cleansed before it is put into a uniform platform. With SSAS, as the name suggests, we can use the data for analysis.

With any organization we have two kinds of data, one which stores transactional details and the other which has historical (Data Warehouse - DW) details. The data in DW will be populated periodically from the data from transactional (OLTP – OnLine Transactional Processing). The data in OLTP is optimized for faster insertion and updations, but the data in DW is meant for faster querying. With DW we can store duplicate data, computed columns, non normalized data as the storage is not important. The important aspect is that when a query is fired, it should give faster results.

When we want to take any decisions based on data, the data needs to be in an analyzed format. For e.g. we want to find out the code churn for developers in an organization. We need to have all the details for each developer; such as how many lines of code were written, modified or deleted. When we need the report, if we start calculating the numbers for each developer for a day, a week, a month or a quarter, it will be time consuming job. If I already have this aggregated data in cache, my job will be very easy. This aggregated data is put in cubes which are termed as multidimensional databases. As the normal cube has multiple dimensions, the data can be viewed with various axes. In this case one axis can be for each developer; another can be for a particular period and third can be for a particular department or for a particular project. Thus we can create different slices for looking at the same data. These slices are termed as dimensions and the actual data is the fact. The sales related data (like the sales person information, data for sales, quantity, and price) will be termed as fact and the axes (like sales person wise information, location wise details, quarterly details) will be termed as dimensions.

The thumb rule says that facts are numeric measures while dimension has primary keys. Thus primary keys in dimension tables are related to foreign keys in fact tables so as to get the details as and when required. There are 2 dimensional models namely star schema and snowflake schema. In star schema, single fact table is related to multiple dimension tables based on primary key in dimension tables (which resembles start like shape) as follows.

In snowflake schema, one of the dimension acts as a fact for another dimension thus giving snowflake like appearance as below

With SSAS, we first need to specify the source from which data is to be fetched. Later the data can be analyzed. After the source, we can create a Data Source View (DSV) to the source. If the source database has 50 tables, we need not want to work with all of them in a given situation. So we create a window to the data with required tables in it. We can also group tables from different sources and put it in the same DSV.

We can even create logical relationships amongst the table in the DSV where the source database remains intact. We are just creating a logical view to the tables.

In this article, we took an overview of SSAS and its various components. In the next article, we will create a cube by using Business Intelligence Development Studio (BIDS).

Feedback:

Creating your first SSIS package with the help of Import Export Wizard

In my previous article Overview of SQL Server Integration Services (SSIS), we had a quick overview of SSIS and the different features it provides. In this post, I will show you how to create your first SSIS package with the help of Import Export Wizard

1. Start BIDS and create a solution named ‘DemoBeginning’

2. Add a SQL Server Integration Services Project with name ‘DemoImportExport’ to the solution.

3. Delete the default package given in the solution explorer > right click on packages and select SSIS Import Export Wizard

4. We will fetch tables from the sample database AdventureWorks and put them in a newly created database named ‘DemoDB’

5. After the initial screen of the wizard, select AdventureWorks database from the database server as shown below:

6. Click Next > Click on New tab for destination database and give name DemoDB. Keep all default settings and click OK.

7. From specify table copy or query screen selec,t copy data from one or more tables or views and click Next

8. Select tables Production.Product, Production.SubCategory, Production.ProductCategory, Sales.SalesOrderDetail and Sales.SalesOrderHeader. We will keep all the default settings, hence will not do any Edit Mappings > click Next

9. On complete the Wizard screen, you will see the following screen:

Click Finish and click on Close after the required actions.

10. In solution explorer you will a see a package created with 2 tasks in control flow and 5 data sources and 5 destinations in data flow task. We can see only 2 connection managers, one used for source and one for destination.

11. We have just created the package but the data is not present in database. You can verify that the database is created without any tables in it. The ‘Prepare SQL Task’ from control flow creates the tables.

12. Execute the package by right clicking on the package in solution explorer and execute package.

You can verify the creation of tables and the data after successful execution. The components will first be shown in yellow color which will subsequently turn to green on execution or red if any error encountered. You can verify that the newly created tables do not have any primary keys and also any relationship. If we want to persist this we need to select Edit Mappings and do the necessary changes.

In next article we will continue with the same database and create a cube based on it.

Feedback:

Overview of SQL Server Integration Services (SSIS)

In one of the previous article Exploring Business Intelligence (BI) with SQL Server , I discussed an overview of Business Intelligence and the three main tools provided by Microsoft for the same. The three tools are SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS). In this article we will discuss the various components in SSIS, its architecture and how to create a small package using SSIS.

BI is a set of tools and technologies provided to make better business decisions. These tools can be used for querying and reporting, data mining, data gathering or OLAP (Online analytical processing). Every organization deals with a lot of data. Every department in any organization has its own way of data storage. Some prefer to store in excel, some use access while some departments may store the data in relational database like SQL Server. If we are creating a report for Order Processing and we get the customers’ orders in printed form we require data entry for this. Suppose this data is entered with excel sheet. We have an Inventory processing system purchased from a third party where the data is stored in Oracle and we store all our customers’ information in SQL Server. We want to create a report which uses data from all these disparate sources. We can put all these data in a common platform with the help of SSIS.

As the name suggests SSIS is used for fetching data (from disparate sources), doing some changes on it (like changing the type from excel to SQL, or creating calculated field) and put the data on a uniform format which can be subsequently used for further analyzing or reporting.

SSIS is a service so you can have only one service on a single box. We have 2 runtime engines in the form of control flow and data flow. It is commonly called as ETL (Extract, Transform and Load). With Business Intelligence Development Studio (BIDS) we have graphical tools as well as wizards for creating and debugging the packages. Packages form the intricate component in SSIS template. Each package is a basic unit of deployment. Every package has control flow and data flow element. Each package has single control flow which is like the workflow for the package. Data flow is a component of control flow but provided with a separate tab as most of the packages will use it for ETL.

With ETL, apart from extracting and loading data from various sources and destinations we can do a lot of transformations to the data. We can merge data from 2 sources into one, we can convert column data type to suit the destination data type, the data can be sorted before putting in, and we can split the data by giving a condition so as to continue with only conditional data and many more tasks.

When we start the template of SQL Server Integration services, with the help of BIDS, we get following screen

The template automatically creates a package with the name as “Package.dtsx”. The extension dtsx stands for data transformation services with XML format. Everything in the package will be stored in XML format. The following screen shows the separate tab for Data Flow.

The following screen shows some of the components from control flow tab from toolbox

We can just drag and drop the required component on the design area.

With the data flow we get three distinct components -- Data sources, Data Transformations and Data Destinations as shown here

With Data flow, we normally fetch data from various sources, do any data cleansing on it if required and finally load into destination.

We also have debugging facility with control flow, where we can add break points when required. With Data flow we have data viewer as a debugger which will show the data in grid, histogram, scatter plot or column chart.

Apart from control flow and data flow, other components of package include connection managers, variables, Event handlers and log providers. When we are working with data source or data destinations we require specifying the connections from which we can fetch data or load data into. These can be created by dragging and dropping data source or destination component on the design area and configuring it or by right clicking in the empty area for connection managers as shown below

We can create variables with different scope with each package. SSIS has 2 types of variables, system variables and user defined variable. System variables store information about the running packages. The scope of user defined variables can be for complete package or for a control on the package. The variable name is case sensitive. These variables can be used for passing properties at run time, specify the value for loop iterations etc.

At run time, control flow components like loops, sequence containers and also packages raise events. Event handlers are used to enhance functionality depending upon the event raised like OnError, OnProgress, OnTaskFailed etc. The events for Package are shown below:

Remember if any event has no handlers, event is raised to the next container up in hierarchy for package.

Logging can be provided for tasks, containers or packages. When you add logging to the package, you need to specify the provider and the location. The following figure shows types of log.

You can configure log by giving the name and description. You also need to give the connection manager log needs.

In this article, we had a quick overview of SSIS and the different features it provides. In the next article, I will show you how to create your first SSIS package with the help of Import Export Wizard

Feedback:

SQL Data Services (SDS) Series

SDS is a part of the Azure platform. If you have missed out on the SQL Data Services (SDS) series written by Gouri Sohoni, here’s the link list for you:

SQL Data Services (SDS)

SQL Data Services (SDS) Part II

SQL Data Services (SDS) Part III

Connecting to SQL Data Services (SDS) with SQL Server Management Studio (SSMS)

SQL Data Services (SDS) Part IV

SQL Data Services (SDS) Part V

SQL Data Services (SDS) Part VI

Feedback:

Some Similarities and Dissimilarities between 3 different Report Creation Tools from Microsoft

The three main Report Creation tools from Microsoft are Business Intelligence Development Studio (BIDS), Report Builder 1.0 and Report Builder 2.0

- The main similarity in all the three of them is that all these tools create RDL (Report Definition Language)

- BIDS provides integrated environment for working with reporting services, integration services and analysis services, which is available with Visual Studio interface. Report Builder tools facilitate working for users who are not familiar with Business Intelligence Development Studio (BIDS).

- BIDS provides more flexibility in terms of deployment configurations as well as provides different rendering extensions.

- The query designer is available for BIDS and Report Builder 2.0 but is not available with Report Builder 1.0.

- We can only create reports on report models with Report Builder 1.0. With BIDS as well as Report Builder 2.0, the report can be based on data source other than report model like relational data sources.

- Multidimensional data source are directly available with query designer for BIDS. For Report Builder, we can create report models on multidimensional data and then subsequently reports can be created based on them.

- Use of query files (.sql) is available with BIDS and Report Builder 2.0

- Report designing wizard is available only with BIDS. We also have a wizard for Report Builder 2.0 for report creation.

- All the three tools provide rendering extensions for Word, Excel, PDF, CSV (Comma limited).

- Viewing of .RDL source is only supported with BIDS.

Feedback:

SQL Data Services (SDS) Part VI

In the previous SDS posts, we discussed how to create database, create table in the cloud by writing a query. We have also seen how to write T-SQL statements with SQL Azure.

In this post, we will discuss how XML data type works with SQL Azure using some sample queries:

How let clause works with FLOWR expression in SQL Azure

--Let clause with FLOWR expression for XML is supported in SQL Azure

declare @x xml = '';
select @x.query('
for $i in (1,2,3,4)
return $i')
go
-- returns 1 2 3 4

declare @x xml = '';
select @x.query('
for $i in ("A","B","C")
order by $i descending
return $i')
go
-- returns  C B A

declare @x xml = '';
select @x.query('
let $x := 1
return $x')
go
-- returns 1

declare @x xml = '';
select @x.query('
let $x := ( <one>2</one> )
return $x')
go
-- error:
-- XQuery [query()]: let  is not supported with constructed XML

-- When we use let inside a loop, it is evaluated each time for the loop

declare @x xml = '';
select @x.query('
for $i in (1,2)
let $j := "try"
return ($i, $j)')

-- returns 1 try 2 try
-- $j is evaluated 2 times

How XQuery works with SQL Azure

CREATE TABLE #Depts
(DeptID integer IDENTITY PRIMARY KEY,
 DeptName nvarchar(40),
 Manager nvarchar(40),
 Names xml)

INSERT INTO #Depts
VALUES
('SQL zure','Sane','<Names>
<Name FirstName="Geeta" LastName="Sohoni"/>
<Name FirstName="Mani" LastName="Raje"/>
<Name FirstName="Raja" LastName="Tembhe"/>
</Names>')

INSERT INTO #Depts
VALUES
('SQL Server','Dani','<Names>
<Name FirstName="Suruchi" LastName="Risbud"/>
</Names>')

INSERT INTO #Depts
VALUES
('SQL Server 2005','Kulkarni',NULL)

SELECT * FROM #Depts

The result is as follows:

The following query gives similar results:

SELECT DeptID, DeptName,Manager,Names.query('
/Names/Name')
FROM #Depts

The result of following query:

SELECT DeptID, DeptName,Manager,Names.value('
(/Names/Name/@FirstName)[2]','char(10)') SecondPerson
FROM #Depts

is as follows:

as the last 2 records have a single person

To fetch the Manager Name

SELECT DeptName,Manager FROM #Depts
WHERE Names.exist('/Names/Name/@FirstName[1]') = 1

The following 2 queries gives exclusive results -- one returns data where there are no people under manager and the other where at least one person has a manager

SELECT DeptName,Manager FROM#Depts
WHERENames.exist('/Names/Name/@FirstName[1]') = 0

--using exist

SELECTDeptName,Manager FROM#Depts
WHERENames.exist('/Names/Name') = 1

The following query will insert one of the relational column in XML as though it is an XML tag

SELECT DeptName, Names.query('<Names>
    <Mgr>{sql:column("Manager")}</Mgr>
    {
     for $i in /Names/Name
     return $i
    }
    </Names>')
FROM #Depts

--use modify method and insert a column
UPDATE #Depts
SET Names.modify('insert element Peon {"Raju"}
  as first
  into (/Names)[1]')
WHERE DeptID = 1

--delete the newly added tag
UPDATE #Depts
SET Names.modify('delete (/Names/Peon)[1]')
WHERE DeptID = 1

Feedback:

SQL Data Services (SDS) Part V

We discussed some T-SQL statements in my previous post SQL Data Services (SDS) Part IV. In this post, I will show some more T-SQL statements with SQL Azure and discuss the compulsion of using clustered index, and how try … catch and Transaction related statements differ with the use of SET XACT_ABORT clause

Note that SQL Azure does not support heap tables. You need to create clustered index. If a table is created without a clustered index, you must create one before inserting data. If you have no clustered index for a table and you try entering data in that table, you get following error in SQL Azure

Following is the example with Try Catch and SET XACT_ABORT_OFF or ON

IF OBJECT_ID(N't2', N'U') IS NOT NULL
    DROP TABLE t2;
GO
IF OBJECT_ID(N't1', N'U') IS NOT NULL
    DROP TABLE t1;
GO
CREATE TABLE t1
    (a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
    (id int primary key identity,a INT NOT NULL REFERENCES t1(a));
GO
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);
GO
SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO
SELECT * FROM t2

Even if we use Transaction, we see that the statement which gives error is not executed. The remaining 2 inserts for which there was no error, are successful

as we see with the select statement

When we use the following statement, none of the records are inserted as required:

SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); -- Foreign key error.
INSERT INTO t2 VALUES (6);
COMMIT TRANSACTION;
GO
SELECT * FROM t1
SELECT *
    FROM t2;
GO

And SELECT statement for table t2 shows same result as what was before the insert statements

In this post, we discussed the compulsion of using clustered index, how try … catch and Transaction related statements differ with the use of SET XACT_ABORT clause.

In next article we will discuss how to use the XML data type with SQL Azure

Feedback:

SQL Data Services (SDS) Part IV

In the previous SDS posts, we discussed how to create database, create table in the cloud by writing a query and also programmatically. When working with SQL Azure T-SQL provided is a subset of T-SQL for SQL Server.

Using T-SQL with SQL Azure

1. When referring to an object in SQL Azure following are the conventions. Note that server name is not allowed in the reference

schema name.object name

i. Create a new query in SQL Server Management Studio (SSMS)
Enter query

CREATE SCHEMA MySchema

ii. Create table as follows

CREATE TABLE MySchema.MyTable
(Id int identity primary key,
UserName nvarchar(15))

iii. Insert rows as follows

INSERT INTO MySchema.MyTable
VALUES (Name1),('Name2')

Note you need to specify the schema name with the object. Currently using database name along with schema and object name is not supported

2. All normal data types are supported in SQL Azure. SQL Azure does not support User Defined Data type. It supports XML data type.

3. SQL Azure database does not support any of the SQL system table

a. There is no provision and requirement of backup and restore

b. There is no log shipping or replication requirement

4. A lot of T-SQL statements like ALTER SCHEMA, ALTER ROLE, DROP LOGIN, DROP USER, CAST, CONVERT, and SET @variable are supported. We will discuss some of them

a.    --create a stored proc
CREATE PROC DispNames
AS
SELECT * FROM Names

-- execute the stored proc
EXEC DispNames

--alter the existing stored proc
ALTER PROC DispNames
AS
SELECT UserId, [User Name] FROM Names

--execute changed stored proc
EXEC DispNames
--delete the proc
DROP PROC DispNames

b.

Notice the first variable data is truncated due to wrong length

c. Merge statement works with SQL Azure in the same manner as SQL Server as follows

CREATE TABLE t1
(Id int NOT NULL primary key, FullName varchar(100))

CREATE TABLE t2
(Id int NOT NULL primary key, FullName varchar(100))

INSERT INTO t1 VALUES
(1,'Smita Sane'),
(5,'Sarita Bhave'),
(6,'John')

INSERT INTO t2 VALUES
(1,'Smita Sohoni'),
(5,'Sarita sonu Bhave'),
(7,'Danny')

SELECT * FROM T1

SELECT * FROM T2

The result for this is as follows

MERGE t1
USING
(SELECT * from t2) target
ON t1.Id=target.Id
WHEN MATCHED
THEN UPDATE SET t1.FullName=target.FullName
WHEN  NOT MATCHED by target
THEN INSERT VALUES (target.Id,target.FullName);

SELECT * FROM t1
SELECT * FROM t2

After merge the result is as follows

So we see that in table T1 records are updated as well as inserted depending upon the condition

DROP TABLE T1
DROP TABLE T2

In next article we will discuss some more T-SQL statements with SQL Azure

Feedback:

Connecting to SQL Data Services (SDS) with SQL Server Management Studio (SSMS)

In order to connect to SQL Data Services, you should have an invitation for working with SQL Azure CTP (discussed in previous article working with SQL Data Services –SDS)

1. Start SSMS 2008. When it asks for a connection to the services, click on Cancel. If by mistake you try connecting using this window, you get following error

2. Click on New query and enter following details. We can only connect using SQL Server authentication as that is the only mode supported in SQL Azure.

3. Click on Options and enter the database name as follows

If you select <default> option or <browse Server> option from Select Database we get errors

You will get a warning as shown below:

Click OK and you are connected to SQL Azure.

Feedback:

SQL Data Services (SDS) Part III

In my previous article SQL Data Services (SDS) Part II, we discussed how to create database and tables with the help of a query in SQL Server Management Studio (SSMS).

While writing the application we have the choice of using ADO.NET provider or SQL Server 2008 ODBC driver. In this example, we are using ADO.NET in the code snippet.

Following code is for a console application which creates a table named T1 with 3 columns in it.

// Provide the following information to connect to server
private static string userName = "<administrator name>";
private static string password = "<password>";
private static string dataSource = "<data source name";
// data source will be server name we gave for SSMS query in last article
private static string sampleDatabaseName = "<name of database we created>";

static void Main(string[] args)on we hav
{

    // Create a connection string for the existing database
    SqlConnectionStringBuilder connStringBuilder;
    connStringBuilder = new SqlConnectionStringBuilder();
    connStringBuilder.DataSource = dataSource;
    connStringBuilder.InitialCatalog = sampleDatabaseName;
    connStringBuilder.Encrypt = true;
    connStringBuilder.TrustServerCertificate = true;
    connStringBuilder.UserID = userName;
    connStringBuilder.Password = password;

    // Connect to the existing database, create table and insert records
    using (SqlConnection conn = new SqlConnection(connStringBuilder.ToString()))
    {
        using (SqlCommand command = conn.CreateCommand())
        {
            conn.Open();

            // Create a table
            command.CommandText = "CREATE TABLE T1([id] int primary key," +
                "FirstName varchar(20), LastName varchar(20))";
            command.ExecuteNonQuery();

            // Insert sample records
            command.CommandText = "INSERT INTO T1 ([id],FirstName, LastName)" +
            "values (1, 'Name 1','Last1'), (2, 'Name 2','Last2')," +
            " (3, 'Name 3','Last3')";
            int rowsAdded = command.ExecuteNonQuery();

            // Query table and view data in while loop
            command.CommandText = "SELECT * FROM T1";

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine("Id: {0}, First Name: {1}, Last Name: {2}",
                                    reader["Id"].ToString(),
                                    reader["FirstName"].ToString(),
                                    reader["LastName"].ToString());
                }
            // Update a record
            command.CommandText =
                "UPDATE T1 SET [FirstName]='change name 2' WHERE [Id]=2";
            command.ExecuteNonQuery();

            // Delete a record
            command.CommandText = "DELETE FROM T1 WHERE [Id]=1";
            command.ExecuteNonQuery();

            }
        }
    }
    Console.WriteLine("Press enter to continue");
    Console.ReadLine();
 }

After you run this application, you can verify the creation of table, insertion, modification and deletion of records by connecting to SQL Azure with the help of SQL Server Management Studio as discussed in last article.

Feedback:

SQL Data Services (SDS) Part II

In the last article, we discussed the overview of SDS and how to start using the CTP by asking for an invitation.

Once you receive the invitation, you will have to go to URL https://sql.azure.com and login with your Windows Live Id where you have received the invitation. You will be shown a single project in My Projects tab with “SDS – only CTP project” and provided with ‘Manage’ as action.When you click on ‘Manage’ you will be shown Server name, Server administrator and the location for the server. You will need this information for further usage. You will observe that master database already exists. You can create a database of your choice by selecting Create Database button and enter name of the database.

In this article we will create our first table in the newly created database. Currently you cannot connect to SQL Azure by using SQL Server Management Studio (SSMS). This feature will be made available later. We can still make use of ‘New’ query, enter the server name and specify SQL Server authentication we created. SQL Azure only allows SQL Server authentication.

Make sure that you have selected the proper window for connection. Do not specify credentials in connect window for SSMS, as you will get an error similar to the one shown below

Make sure you click on Options tab and specify the database you need to connect to as shown. Do not select browse on server but enter name of the database. Keep the remaining properties as default.

You will get a warning as follows

Click on OK and continue.

Enter first query as SELECT @@ version

Let us create a table in the database with the name as Names and 3 columns as UserId , User Name and password. Make sure you are connected to the newly create database.

Add some records to the table by writing T-SQL as

INSERT INTO Names VALUES (1,’Name’,’Password’)

And finally enter the query

SELECT * FROM Names 

to view the added rows.

In this post, we created a database, created a new table in the database, added rows to the table and even retrieved the newly added rows. In the next post, we will view and edit the table programmatically.

Feedback:

SQL Data Services (SDS)

SDS is a part of Azure platform. Azure platform offers the following services: Windows Azure (Operating System in cloud), Microsoft .NET Services (set of WCF based services), SQL Azure (relational database in cloud)

SQL Azure

This service provides SQL server capabilities in cloud. We can create a database which is currently in the CTP (Community Technology Preview) form. We can have all the benefits of working with SQL Server plus no hazards of doing the administration. Thus administration tasks like replication, availability will be automatically made available to us. There will be 3 copies of data maintained out of which 2 will be synchronized and the third one may lag a bit. This also leads to limiting the size of the database to 10 GB. The size limitation is for 2 reasons, one for availability (in order to replicate the data in cloud, it needs to be within limit) and the second being shared database box for multiple users (as the same box will be used for storing the data from many users. If the size of data is huge for a single user, then the box will not be scalable to other users hence limit in size)

When we are maintaining data in the cloud again, we have 2 options of creating applications. One in which data is near the code, which means we have application running on the same box where we have data. Another will be where data is far from the code, in which we will be always manipulating data from the cloud (in case of web based application client).

How is the service provided?

The service is made available with the help of TDS (Tabular Data Stream) protocol. There are accounts available for billing purpose which will own one or more servers as per requirement. Each server will have one or more databases. These servers will use SQL Server authentication model. The databases will have one or more SQL users with respective permissions. Thus maintaining logical administration in the form of the creating views, creating triggers, tuning queries, tuning indexes etc. will be the job requirement of DBA rather than physical management in the form of how many file groups will be required, taking backup, recovery etc.

As of now, functionality like service broker, CLR (Common Language Runtime) functionality is not available with SDS, but will be subsequently made available.

How can SDS be used?

There can be various scenarios in which this service can be used.

- In small organizations, IT groups for maintaining relational database may not be available.
- In big organizations, if there is an inter department requirement for maintaining another copy of relational database, proving IT support becomes very difficult as IT staff is already overburdened.
- For creating web applications in which maintaining SQL Server for the web application may be very costly.

How can I start using SQL Azure?

You will have to register for using the current CTP over here http://msdn.microsoft.com/en-us/sqlserver/dataservices/default.aspx

You will receive the invitation for using SQL Azure which can be used with your Windows Live ID.

Feedback:

SQL Server 2008 Compliance Features – Some Resources

Compliances are standards, regulatory requirements or organizational policies which help organizations to operate securely and efficiently. SQL Server 2008 has got a number of compliance-related features that makes compliance relatively easier. Here are some documents and videos related to the same:

Compliance Guide for SQL Server 2008

SQL Server Auditing and Compliance

Compliance for Decision Makers Video

Compliance for IT Professionals Video

You can get more details about it in the SQL Server 2008 Compliance page

Feedback:

Specify a Condition while Editing TOP Rows using SQL Server 2008 Management Studio (SSMS)

I had recently posted about Using SSMS to change the Edit TOP Rows option . A user Greg commented asking me if it was possible to specify a condition while Editing the TOP Rows.

Here’s my observations. I am using the Purchasing.VendorContact table from the AdventureWorks database as a sample. Right click on the VendorContact table > Edit Top 5 rows

I get the following results:

Now let us say that using SSMS, you want to Edit only those rows having ContactTypeID=2. SQL Server 2008 gives you no option out of the box to do so – at least I couldn’t find one.

Here’s how I solved Greg’s requirement.

Update: Psy has shared the right way to do this. Follow these steps:

* Click edit top x rows. Click the "view sql pane" button (the letters SQL in a white box) from the query designer toolbar that appears in edit mode.

* Change the sql as needed.

* Click the red exclaimation mark to re-query the dataset.

* Edit as needed.

* You can also click the 'view sql pane' a 2nd time to hide it and still keep the changed query. You can also hit the execute icon as many times as needed to refresh the data.

The other way to handle this requirement is to Create a View! However this solution should be avoided:

Right click Views > New View. Type the following query:

Save this view as ‘EditVendorContact’. Now Right Click this newly created view > Edit Top 5 Rows

And here’s what you get

Feedback: