ROW Count for LIST OF TABLES IN A DATABASE

Sometimes there is a need to get record counts from every table in your database. One way of doing this is to do a SELECT count(*) on all of your tables, but this could create a lot of overhead especially for large databases and large tables. If you don't require an exact answer, it isn't necessary to use a SELECT count(*) query on the rows in a table to get the row count.

SELECT so.[name] as [table name],
CASE WHEN si.indid between 1 and 254
THEN si.[name] ELSE NULL END
AS [Index Name] , si.indid
FROM sysindexes si
INNER JOIN sysobjects so ON si.id = so.id
WHERE si.indid < 2
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]

----------------------------------
Just the table name and no of rows

SELECT so.[name] as [table name],rowcnt as ROWS
FROM sysindexes si
INNER JOIN sysobjects so
ON si.id = so.id
WHERE si.indid < 2
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]

----------------------------------
ON A LINKEDSERVER

SELECT so.[name] as [table name],rowcnt as ROWS
FROM [SERVERNAME].
[DATABASENAME].sys.sysindexes si
INNER JOIN
[SERVERNAME].
[DATABASENAME].sys.sysobjects so
ON si.id = so.id
WHERE si.indid < 2
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]

-----------------------------------

T-SQL Snippets, Codes,Tweaks

Operators Allowed in the WHERE Clause
= Equal
<> Not equal
> Greater than
<>= Greater than or equal
<= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern IN If you know the exact value you want to return for at least one of the columns

------------------------------

Taking a backup of a table,
The SQL SELECT INTO statement can be used to create backup copies of tables.

SELECT *INTO new_table_name [IN externaldatabase] FROM old_tablename

or

SELECT column_name(s)INTO new_table_name [IN externaldatabase]

FROM old_tablename

or

Here it creates the table name DEF and copies the content accordingly
SELECT COLA,COLB,COLC,COLD,COLE,COLF INTO DEF FROM ABC

or

SELECT * INTO DEF FROM ABC

or

We can also use the IN clause to copy the table into another database:

SELECT * INTO Persons_Backup IN 'Backup.mdb' FROM Persons

or

We can also copy only a few fields into the new table:

SELECT LastName,FirstName INTO Persons_Backup FROM Persons

or

SQL SELECT INTO - With a WHERE ClauseWe can also add a WHERE clause.

The following SQL statement creates a "Persons_Backup" table with only the persons who lives in the city "Sandnes":

SELECT LastName,Firstname INTO Persons_Backup FROM Persons WHERE

City='Sandnes'

or

SQL SELECT INTO - Joined TablesSelecting data from more than one table is also possible.
The following example creates a "Persons_Order_Backup" table contains data from the two tables "Persons" and "Orders":

SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id

------------------------------

Exporting the output into a text or csv file:

Syntax:
sqlcmd -S SERVERNAME -i C:\1.sql -o C:\hello.txt or [C:\hello.csv]

------------------------------

Executing select statements in bcp

The below code, iam copying the output into a directory with , delimited values
bcp "select * from [DATABASENAME]..[TABLENAME]" queryout C:\Output.csv -S SERVERNAME -c -t',' -T

NOTE: The two .. are must.

------------------------------

Filter Out unmatching records between two tables

SELECT COLA FROM ABC A WHERE NOT EXISTS (SELECT * FROM DEF D WHERE (A.COLA=D.ABC))

or

SELECT COLA FROM DEF D WHERE NOT EXISTS (SELECT * FROM ABC A WHERE (D.COLA=A.ABC))

or

SELECT A.COLA from ABC A where A.COLA NOT IN (select D.COLA from DEF D)

------------------------------

Removing 0(zeros) from the begining of a particular number COLA is a column name and ABC is the table name

select REPLACE(LTRIM(REPLACE(COLA, '0', ' ')), ' ', '0') from ABC

or

Select Replace(T.Col1,”@”, ‘at’) As [ColName] From MyTable T

------------------------------

Retrieve MSSQL table information

select * from information_schema.columnswhere table_name= 'ABC'

------------------------------

To Query the list of columns in a table

select column_name 'Column Name', data_type 'Data Type', character_maximum_length 'Maximum Length' from information_schema.columns where table_name = 'master'

or

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.ColumnsWHERE TABLE_NAME = 'master'

------------------------------

To find the name of the table in which a particular column name exists.

Here it checks for the database for the tables which have the column name as COLA

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'ABC' )

or

if you're unsure exactly what the column is named, but you suspect you know part of the name, then try...

SELECT name FROM sysobjects WHERE id IN(SELECT id FROM syscolumns WHERE name like '%COLA%')

------------------------------

To query the stored procedures in a table

select object_name(id) as objectname from syscommentswhere text like '%ABC%'

------------------------------

Finding a text in stored procedure

SELECT so.[name] AS 'storedProcedure' FROM sysobjects so JOIN syscomments sc ONso.[id] = sc.[id]WHERE so.[type] = 'P'AND sc.[text] LIKE '%action%'

------------------------------

checking the column if it exists and add

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ABC' AND COLUMN_NAME = 'COLA')

BEGIN

ALTER TABLE ABC ADD COLA int

END

------------------------------

Renaming a column name

EXEC sp_rename 'TABLENAME.OLD_COLUM

NNAME', 'NEW_COLUMNAME', 'COLUMN'

------------------------------

To get the columns names

SELECT table_name=sysobjects.name,column_name=syscolumns.name, datatype=systypes.name, length=syscolumns.length FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes ON syscolumns.xtype=systypes.xtype WHERE sysobjects.xtype='U'and syscolumns.NAME = 'assign the name of the column here'

ORDER BY sysobjects.name,syscolumns.colid

or

SELECT table_name=sysobjects.name,column_name=syscolumns.name,

datatype=systypes.name,length=syscolumns.length

FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes

ON syscolumns.xtype=systypes.xtype WHERE syscolumns.name='COLA'

------------------------------

To split a column into two using a SELECT statement based on a condition

SELECT CASE WHEN STATE = 'VA'

THEN STATE ELSE '' END as 'Higher income group',

CASE WHEN STATE = 'NY'

THEN STATE ELSE '' END as 'Lower income group',NUMBER FROM ABC

------------------------------

case query

SELECT NUMBER, STATE, priority = ( CASE STATE when 'WA' then 1 when 'VA' then 2 when 'NY' then 3 when 'CA' then 4 end) FROM ABC ORDER BY state

------------------------------

To find out the user name at SQL prompt.

Select user_name()

------------------------------

If the table exists in the database

select name from sysobjects where xtype='u' and name = 'master'

------------------------------

Determining Your Session IDs for SQL Serversession id

SELECT @@SPID

------------------------------

No of tables count in a database

SELECT COUNT(*) from information_schema.tables WHERE table_type = 'base table'

------------------------------

list of table names

SELECT TABLE_NAME from information_schema.tables WHERE table_type = 'base table' order by table_name

------------------------------

Returns list of tables that have no primary key

select name from sysobjects where id not in (select b.id from sysconstraints b, sysobjects c where c.type = 'K' and c.id = b.constid) and type = 'U' order by name

------------------------------

SQL Query to Update a Column Value in All Tables

SELECT 'UPDATE ',RTRIM(Name),' SET UserID=2 WHERE UserID=1' FROM sysobjectsWHERE Type='U'

------------------------------

Displaying structure of a table

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'DATABASENAME' AND TABLE_NAME = 'ABC'

or

exec sp_columns ABC

------------------------------

To determine if a table exists in a SQL Server Database

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='tablename') SELECT 'tablename exists.' ELSE SELECT 'tablename does not exist.'

------------------------------

How do I find all the foreign keys in a database?

SELECT FK_Table = FK.TABLE_NAME,FK_Column = CU.COLUMN_NAME,

PK_Table = PK.TABLE_NAME,PK_Column = PT.COLUMN_NAME,

Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN (SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT ON PT.TABLE_NAME = PK.TABLE_NAME

PS:optional: ORDER BY 1,2,3,4

------------------------------

The statement takes two tables (ABC and DEF), and figures out which columns between the two are similar:

SELECT table_name=sysobjects.name,column_name=syscolumns.name, datatype=systypes.name,length=syscolumns.length FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes ON syscolumns.xtype=systypes.xtype WHERE (sysobjects.xtype='U' OR sysobjects.xtype='v') AND syscolumns.name IN (SELECT syscolumns.name FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes ON syscolumns.xtype=systypes.xtype WHERE sysobjects.name = 'ABC') AND sysobjects.name = 'DEF'

------------------------------

How to find a text inside SQL Server procedures / triggers?

SELECT DISTINCT LEFT(so.name, 100) AS Object_Name,"object_type"= left(case so.type when 'U' then 'Table - User'

when 'S' then 'Table - System'

when 'V' then 'Table - View'

when 'TR' then 'Trigger'

when 'P' then 'Stored Procedure'

when 'C' then 'Constraint - Check'

when 'D' then 'Default'

when 'K' then 'Key - Primary'

when 'F' then 'Key - Foreign'

when 'L' then 'Log'

when 'R' then 'Rule'

when 'RF' then 'Replication Filter stp'

else '<>'

end -- case so.type ,25)

FROM syscomments sc

INNER JOIN sysobjects so

ON so.id = sc.id WHERE text Like '%empname%' ORDER BY 2,1

------------------------------

GETTING VALUES ROW BY ROW

DECLARE @cursor CURSOR
SET @cursor=CURSOR FOR SELECT * FROM abc
OPEN @cursorFETCH @cursor
WHILE (@@FETCH_STATUS=0) BEGIN FETCH @cursorEND
CLOSE @cursorDEALLOCATE @cursor

------------------------------

NO OF WAITING TASKS

SELECT COUNT(*) AS 'Number of waiting tasks'FROM sys.dm_os_waiting_tasks

------------------------------

Number of threads used with MSSQL 2005

SELECT COUNT(*) AS 'Number of threads'FROM sys.dm_os_waiting_tasks

------------------------------

Type of waiting tasks with MSSQL 2005

SELECT CAST(wait_type AS VARCHAR(30)) AS 'Waiting task',COUNT (*) AS 'Number of waiting tasks 'FROM sys.dm_os_waiting_tasks GROUP BY wait_type ORDER BY 'Number of waiting tasks' DESC WHERE wait_type <> 'THREADPOOL'

------------------------------

Resource Bottlenecks with MSSQL 2005

SELECT resource_address AS 'Resource Bottleneck',COUNT (*) AS '# of bottlenecks' FROM sys.dm_os_waiting_tasks WHERE resource_address <> 0 GROUP BY resource_address ORDER BY '# of bottlenecks' DESC

------------------------------

IO Bottlenecks with MSSQL 2005

SELECT waiting_task_address AS 'Task address', session_id AS 'Session', exec_context_id AS 'Context', wait_duration_ms AS 'Wait in millsec',CAST(wait_type AS VARCHAR(30)) AS 'Type', resource_address AS 'Resource address', blocking_task_address AS 'Blocking task', blocking_session_id AS 'Blocking session',CAST(resource_description AS VARCHAR(30)) AS 'Resource' FROM sys.dm_os_waiting_tasks WHERE wait_duration_ms > 20 AND wait_type LIKE '%PAGEIOLATCH%'

------------------------------

Has a row been changed

SELECT UNIQ_ID,CD_TYPE,CODE1,CHECKSUM(UNIQ_ID,CD_TYPE,CODE1) AS 'Checksum specific',CHECKSUM(*) AS 'Checksum general' FROM INFO

------------------------------

Create a comma delimited list (csv) of unique values

DECLARE @uniquelist VARCHAR(1000);SELECT @uniquelist = LTRIM(COALESCE(@uniquelist+',' , ' ')) + CAST(UNIQ_ID AS VARCHAR(10))FROM (SELECT DISTINCT UNIQ_ID FROM INFO) ABC SELECT @uniquelist AS 'Unique emp_short list: comma delimited'

------------------------------

Incrementing counters that already contain NULLs

SELECT counter,CASE WHEN counter IS null THEN (row_number() OVER (ORDER BY emp_short,trans_date) +(SELECT MAX(trmax.counter) FROM trans02 trmax ) + 10 ) ELSE counter END AS 'Calculated counter', emp_short,trans_date,debit,credit FROM trans02ORDER BY 'Calculated counter'

------------------------------

Adding a link server

EXEC master.dbo.sp_addlinkedserver @server = N'SERVERNAME', @srvproduct=N'SQL Server'

------------------------------

Copying the table structure without copying the data from another table

SELECT * INTO NewTable FROM abc WHERE 1=2

------------------------------

Renaming the table name and column name

EXEC sp_rename 'OldTableName', 'NewTableName'

------------------------------

Renaming Column name Syntax

EXEC sp_rename @objname = 'TableName.OldColumnName', @newname = 'NewColumnName', @objtype = 'COLUMN'

Example : XYZ and ZZZ and DEF are table namesexec sp_rename 'XYZ' , 'ZZZ' - Renaming XYZ table name to ZZZexec sp_rename 'DEF.[B]', 'GROUP' 'COLUMN' - Renaming Column B of table DEF to , GROUP

------------------------------

This script will gives row number for every table in database.
select OBJECT_NAME(object_id) TableName,st.row_countfrom sys.dm_db_partition_stats st where index_id<2>

------------------------------

Obtaining CLR Execution performance counter values.

SELECT object_name, counter_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%CLR%'

------------------------------

Query performance and time spent in SQL CLR.

SELECT (SELECT text FROM sys.dm_exec_sql_text(qs.sql_handle)) AS query_text, qs.*FROM sys.dm_exec_query_stats AS qs WHERE qs.total_clr_time > 0 ORDER BY qs.total_clr_time desc

------------------------------

Requests that are currently in SQL CLR

SELECT session_id, request_id, start_time, status, command, database_id,
wait_type, wait_time, last_wait_type, wait_resource, cpu_time,
total_elapsed_time, nest_level, executing_managed_code
FROM sys.dm_exec_requests WHERE executing_managed_code = 1

------------------------------

SQL CLR Wait Statistics

SELECT ws.* FROM sys.dm_os_wait_stats AS wsWHERE ws.wait_type LIKE '%clr%'

------------------------------

User-defined Types

User Defined Type Metadata

Includes base assembly information

SELECT st.[name] AS [Type Name] , st.max_length, st.[precision], st.scale, st.collation_name, st.is_nullable, sa.create_date, sa.[name] AS [Assembly Name], sa.permission_set_desc AS [Access]FROM sys.types AS st INNER JOIN sys.type_assembly_usages AS tau ON st.user_type_id = tau.user_type_id INNER JOIN sys.assemblies AS sa ON tau.assembly_id = sa.assembly_id

------------------------------

LIST CLR AGGREGATE FUNCTIONS

SELECT schema_name(so.schema_id) + N'.' + so.[name] AS [Name] , so.create_date, so.modify_date, sa.permission_set_desc AS [Access]FROM sys.objects AS so INNER JOIN sys.module_assembly_usages AS mau ON so.object_id = mau.object_id INNER JOIN sys.assemblies AS sa ON mau.assembly_id = sa.assembly_idWHERE so.type_desc = N'AGGREGATE_FUNCTION'

------------------------------

LIST CLR TABLE FUNCTIONS

SELECT schema_name(so.schema_id) + N'.' + so.[name] AS [Name], so.create_date, so.modify_date, sa.permission_set_desc AS [Access] FROM sys.objects AS so INNER JOIN sys.module_assembly_usages AS sau ON so.object_id = sau.object_id INNER JOIN sys.assemblies AS sa ON sau.assembly_id = sa.assembly_id WHERE so.type_desc = N'CLR_TABLE_VALUED_FUNCTION'

------------------------------

LIST CLR FUNCTIONS:

SELECT schema_name(so.schema_id) + N'.' + so.[name] AS [Name], so.create_date, so.modify_date, sa.permission_set_desc AS [Access] FROM sys.objects AS so INNER JOIN sys.module_assembly_usages AS sau ON so.object_id = sau.object_id INNER JOIN sys.assemblies AS sa ON sau.assembly_id = sa.assembly_id WHERE so.type_desc = N'CLR_SCALAR_FUNCTION'

------------------------------

Substring function

Select SUBSTRING(COLA,5,10) as NAME,SUBSTRING(COLA,152,2 )as CUSTNAME from ABC

------------------------------

Getting all the rows in one column

declare @res varchar(4000)

set @res =''

select @res = @res + A + ',' from testing

if @@rowcount > 0 select substring(@res, 1, len(@res)-1)
NOTE:Testing is the table name and 'A' is the column name

------------------------------

List of databases attached

SELECT dtb.name AS [Name] FROM master.dbo.sysdatabases AS dtb ORDER BY [Name] ASC

------------------------------

Adding strings to the output

select (CHAR('39')+COLA+CHAR('39')++CHAR('44')) from ABC

------------------------------

Generating a script using the existing table structure

SELECT CASE WHEN colid = 1 THEN 'CREATE TABLE ' + O.name + ' (' ELSE '' END+ C.name + ' ' + CASE C.xtype WHEN 167 THEN 'varchar(' + CONVERT(varchar(20), C.length) + ') 'WHEN 61 THEN 'datetime'-- ...ELSE 'UnknownType_' + CONVERT(varchar(20), C.xtype)END + ' '+ CASE WHEN C.isnullable = 1THEN 'NULL' ELSE 'NOT NULL' END+ CASE WHEN colid =(SELECT MAX(colid) FROM dbo.syscolumns C2 WHERE C2.id = O.id) THEN ')' ELSE ',' ENDFROM dbo.sysobjects O JOIN dbo.syscolumns C ON C.id = O.id WHERE O.name= 'info' AND O.type = 'U'ORDER BY colid

SSAS - SQL Server 2005 Analysis Services

Analysis Services

• Understanding Analysis Services
• Introducing BIDS
• Creating a Data Cube
• Exploring a Data Cube

Files needed:

• AdventureWorksCube1.zip
• AdventureWorksCube2.zip

So far in this course you’ve focused on getting data into a SQL Server database and then later getting the same data out of the database. You’ve seen how to create tables, insert data, and use SQL statements, views, and stored procedures to retrieve the data. This pattern of activity, where individual actions deal with small pieces of the database, is sometimes called online transaction processing, or OLTP.

But there’s another use for databases, especially large databases. Suppose you run an online book store and have sales records for 50 million book sales. Maybe books on introductory biology show a strong spike in sales every September. That’s a fact that you could use to your advantage in ordering stock, if only you knew about it.

Searching for patterns like this and summarizing them is called online analytical processing, or OLAP. Microsoft SQL Server 2005 includes a separate program called Microsoft SQL Server 2005 Analysis Services to perform OLAP analysis. In this chapter you’ll learn the basics of setting up and using Analysis Services.

SSAS Tutorial: Understanding Analysis Services

The basic idea of OLAP is fairly simple. Let’s think about that book ordering data for a moment. Suppose you want to know how many people ordered a particular book during each month of the year. You could write a fairly simple query to get the information you want. The catch is that it might take a long time for SQL Server to churn through that many rows of data.

And what if the data was not all in a single SQL Server table, but scattered around in various databases throughout your organization? The customer info, for example, might be in an Oracle database, and supplier information in a legacy xBase database. SQL Server can handle distributed heterogeneous queries, but they’re slower.

What if, after seeing the monthly numbers, you wanted to drill down to weekly or daily numbers? That would be even more time -consuming and require writing even more queries.

This is where OLAP comes in. The basic idea is to trade off increased storage space now for speed of querying later. OLAP does this by precalculating and storing aggregates. When you identify the data that you want to store in an OLAP database, Analysis Services analyzes it in advance and figures out those daily, weekly, and monthly numbers and stores them away (and stores many other aggregations at the same time). This takes up plenty of disk space, but it means that when you want to explore the data you can do so quickly.

Later in the chapter, you’ll see how you can use Analysis Services to extract summary information from your data. First, though, you need to familiarize yourself with a new vocabulary. The basic concepts of OLAP include:

• Cube
• Dimension table
• Dimension
• Level
• Fact table
• Measure
• Schema

Cube

The basic unit of storage and analysis in Analysis Services is the cube. A cube is a collection of data that’s been aggregated to allow queries to return data quickly. For example, a cube of order data might be aggregated by time period and by title, making the cube fast when you ask questions concerning orders by week or orders by title.

Cubes are ordered into dimensions and measures. Dimensions come from dimension tables, while measures come from fact tables.

Dimension table

A dimension table contains hierarchical data by which you’d like to summarize. Examples would be an Orders table, that you might group by year, month, week, and day of receipt, or a Books table that you might want to group by genre and title.

Dimension

Each cube has one or more dimensions, each based on one or more dimension tables. A dimension represents a category for analyzing business data: time or category in the examples above. Typically, a dimension has a natural hierarchy so that lower results can be "rolled up" into higher results. For example, in a geographical level you might have city totals aggregated into state totals, or state totals into country totals.

Level

Each type of summary that can be retrieved from a single dimension is called a level. For example, you can speak of a week level or a month level in a time dimension.

Fact table

A fact table contains the basic information that you wish to summarize. This might be order detail information, payroll records, drug effectiveness information, or anything else that’s amenable to summing and averaging. Any table that you’ve used with a Sum or Avg function in a totals query is a good bet to be a fact table.

Measure

Every cube will contain one or more measures, each based on a column in a fact table that you’d like to analyze. In the cube of book order information, for example, the measures would be things such as unit sales and profit.

Schema

Fact tables and dimension tables are related, which is hardly surprising, given that you use the dimension tables to group information from the fact table. The relations within a cube form a schema. There are two basic OLAP schemas: star and snowflake. In a star schema, every dimension table is related directly to the fact table. In a snowflake schema, some dimension tables are related indirectly to the fact table. For example, if your cube includes OrderDetails as a fact table, with Customers and Orders as dimension tables, and Customers is related to Orders, which in turn is related to OrderDetails, then you’re dealing with a snowflake schema.

There are additional schema types besides the star and snowflake schemas, including parent-child schemas and data-mining schemas. However, the star and snowflake schemas are the most common types in normal cubes.

SSAS Tutorial: Introducing Business

Intelligence Development Studio

Business Intelligence Development Studio (BIDS) is a new tool in SQL Server 2005 that you can use for analyzing SQL Server data in various ways. You can build three different types of solutions with BIDS:

• Analysis Services projects
• Integration Services projects
• Reporting Services projects

To launch Business Intelligence Development Studio, select Microsoft SQL Server 2005 > SQL Server Business Intelligence Development Studio from the Programs menu. BIDS shares the Visual Studio shell, so if you have Visual Studio installed on your computer, this menu item will launch Visual Studio complete with all of the Visual Studio project types (such as Visual Basic and C# projects).

SSAS Tutorial: Creating a Data Cube

To build a new data cube using BIDS, you need to perform these steps:

• Create a new Analysis Services project
• Define a data source
• Define a data source view
• Invoke the Cube Wizard

We’ll look at each of these steps in turn.

You’ll need to have the AdventureWorksDW sample database installed to complete the examples in this chapter. This database is one of the samples that’s available with SQL Server.

Creating a New Analysis Services Project

To create a new Analysis Services project, you use the New Project dialog box in BIDS. This is very similar to creating any other type of new project in Visual Studio.

Try It!

To create a new Analysis Services project, follow these steps:

1. Select Microsoft SQL Server 2005 > SQL Server Business Intelligence Development Studio from the Programs menu to launch Business Intelligence Development Studio.
2. Select File > New > Project.
3. In the New Project dialog box, select the Business Intelligence Projects project type.
4. Select the Analysis Services Project template.
5. Name the new project AdventureWorksCube1 and select a convenient location to save it.
6. Click OK to create the new project.

Figure 15-1 shows the Solution Explorer window of the new project, ready to be populated with objects.

Figure 15-1: New Analysis Services project

Defining a Data Source

To define a data source, you’ll use the Data Source Wizard. You can launch this wizard by right-clicking on the Data Sources folder in your new Analysis Services project. The wizard will walk you through the process of defining a data source for your cube, including choosing a connection and specifying security credentials to be used to connect to the data source.

Try It!

To define a data source for the new cube, follow these steps:

1. Right-click on the Data Sources folder in Solution Explorer and select New Data Source.
2. Read the first page of the Data Source Wizard and click Next.
3. You can base a data source on a new or an existing connection. Because you don’t have any existing connections, click New.
4. In the Connection Manager dialog box, select the server containing your analysis services sample database from the Server Name combo box.
5. Fill in your authentication information.
6. Select the Native OLE DB\SQL Native Client provider (this is the default provider).
7. Select the AdventureWorksDW database. Figure 15-2 shows the filled-in Connection Manager dialog box.



Figure 15-2: Setting up a connection


8. Click OK to dismiss the Connection Manager dialog box.
9. Click Next.
10. Select Default impersonation information to use the credentials you just supplied for the connection and click Next.
11. Accept the default data source name and click Finish.

Defining a Data Source View

A data source view is a persistent set of tables from a data source that supply the data for a particular cube. BIDS also includes a wizard for creating data source views, which you can invoke by right-clicking on the Data Source Views folder in Solution Explorer.

Try It!

To create a new data source view, follow these steps:

1. Right-click on the Data Source Views folder in Solution Explorer and select New Data Source View.
2. Read the first page of the Data Source View Wizard and click Next.
3. Select the Adventure Works DW data source and click Next. Note that you could also launch the Data Source Wizard from here by clicking New Data Source.
4. Select the dbo.FactFinance table in the Available Objects list and click the > button to move it to the Included Object list. This will be the fact table in the new cube.
5. Click the Add Related Tables button to automatically add all of the tables that are directly related to the dbo.FactFinance table. These will be the dimension tables for the new cube. Figure 15-3 shows the wizard with all of the tables selected.



Figure 15-3: Selecting tables for the data source view


6. Click Next.
7. Name the new view Finance and click Finish. BIDS will automatically display the schema of the new data source view, as shown in Figure 15-4.

Figure 15-4: The Finance data source view

Invoking the Cube Wizard

As you can probably guess at this point, you invoke the Cube Wizard by right-clicking on the Cubes folder in Solution Explorer. The Cube Wizard interactively explores the structure of your data source view to identify the dimensions, levels, and measures in your cube.

Try It!

To create the new cube, follow these steps:

1. Right-click on the Cubes folder in Solution Explorer and select New Cube.
2. Read the first page of the Cube Wizard and click Next.
3. Select the option to build the cube using a data source.
4. Check the Auto Build checkbox.
5. Select the option to create attributes and hierarchies.
6. Click Next.
7. Select the Finance data source view and click Next.
8. Wait for the Cube Wizard to analyze the data and then click Next.
9. The Wizard will get most of the analysis right, but you can fine-tune it a bit. Select DimTime in the Time Dimension combo box. Uncheck the Fact checkbox on the line for the dbo.DimTime table. This will allow you to analyze this dimension using standard time periods.
10. Click Next.
11. On the Select Time Periods page, use the combo boxes to match time property names to time columns according to Table 15-1.
    
    

Time Property Name	Time Column
Year	CalendarYear
Quarter	CalendarQuarter
Month	MonthNumberOfYear
Day of Week	DayNumberOfWeek
Day of Month	DayNumberOfMonth
Day of Year	DayNumberOfYear
Week of Year	WeekNumberOfYear
Fiscal Quarter	FiscalQuarter
Fiscal Year	FiscalYear

Table 15-1: Time columns for Finance cube

12. Click Next.
13. Accept the default measures and click Next.
14. Wait for the Cube Wizard to detect hierarchies and then click Next.
15. Accept the default dimension structure and click Next.
16. Name the new cube FinanceCube and click Finish.

Deploying and Processing a Cube

At this point, you’ve defined the structure of the new cube - but there’s still more work to be done. You still need to deploy this structure to an Analysis Services server and then process the cube to create the aggregates that make querying fast and easy.

To deploy the cube you just created, select Build > Deploy AdventureWorksCube1. This will deploy the cube to your local Analysis Server, and also process the cube, building the aggregates for you. BIDS will open the Deployment Progress window, as shown in Figure 15-5, to keep you informed during deployment and processing.

Figure 15-5: Deploying a cube

One of the tradeoffs of cubes is that SQL Server does not attempt to keep your OLAP cube data synchronized with the OLTP data that serves as its source. As you add, remove, and update rows in the underlying OLTP database, the cube will get out of date. To update the cube, you can select Cube > Process in BIDS. You can also automate cube updates using SQL Server Integration Services, which you’ll learn about in Chapter 16.

SSAS Tutorial: Exploring a Data Cube

At last you’re ready to see what all the work was for. BIDS includes a built-in Cube Browser that lets you interactively explore the data in any cube that has been deployed and processed. To open the Cube Browser, right-click on the cube in Solution Explorer and select Browse. Figure 15-6 shows the default state of the Cube Browser after it’s just been opened.

Figure 15-6: The cube browser in BIDS

The Cube Browser is a drag-and-drop environment. If you’ve worked with pivot tables in Microsoft Excel, you should have no trouble using the Cube browser. The pane to the left includes all of the measures and dimensions in your cube, and the pane to the right gives you drop targets for these measures and dimensions. Among other operations, you can:

• Drop a measure in the Totals/Detail area to see the aggregated data for that measure.
• Drop a dimension or level in the Row Fields area to summarize by that level or dimension on rows.
• Drop a dimension or level in the Column Fields area to summarize by that level or dimension on columns
• Drop a dimension or level in the Filter Fields area to enable filtering by members of that dimension or level.
• Use the controls at the top of the report area to select additional filtering expressions.

In fact, if you’ve worked with pivot tables in Excel, you’ll find that the Cube Browser works exactly the same, because it uses the Microsoft Office PivotTable 11.0 control as its basis.

Try It!

To see the data in the cube you just created, follow these steps:

1. Right-click on the cube in Solution Explorer and select Browse.
2. Expand the Measures node in the metadata panel (the area at the left of the user interface).
3. Expand the Fact Finance node.
4. Drag the Amount measure and drop it on the Totals/Detail area.
5. Expand the Dim Account node in the metadata panel.
6. Drag the Account Description property and drop it on the Row Fields area.
7. Expand the Dim Time node in the metadata panel.
8. Drag the Calendar Year-Calendar Quarter-Month Number of Year hierarchy and drop it on the Column Fields area.
9. Click the + sign next to year 2001 and then the + sign next to quarter 3.
10. Expand the Dim Scenario node in the metadata panel.
11. Drag the Scenario Name property and drop it on the Filter Fields area.
12. Click the dropdown arrow next to scenario name. Uncheck all of the checkboxes except for the one next to the Budget name.

Figure 15-7 shows the result. The Cube Browser displays month-by-month budgets by account for the third quarter of 2001. Although you could have written queries to extract this information from the original source data, it’s much easier to let Analysis Services do the heavy lifting for you.

Figure 15-7: Exploring cube data in the cube browser

SSAS Tutorial: Exercises

Create a data cube, based on the data in the AdventureWorksDW sample database, to answer the following question: what were the internet sales by country and product name for married customers only?

Solutions to Exercises

To create the cube, follow these steps:

1. Select Microsoft SQL Server 2005 > SQL Server Business Intelligence Development Studio from the Programs menu to launch Business Intelligence Development Studio.
2. Select File > New > Project.
3. In the New Project dialog box, select the Business Intelligence Projects project type.
4. Select the Analysis Services Project template.
5. Name the new project AdventureWorksCube2 and select a convenient location to save it.
6. Click OK to create the new project.
7. Right-click on the Data Sources folder in Solution Explorer and select New Data Source.
8. Read the first page of the Data Source Wizard and click Next.
9. Select the existing connection to the AdventureWorksDW database and click Next.
10. Select Default and click Next.
11. Accept the default data source name and click Finish.
12. Right-click on the Data Source Views folder in Solution Explorer and select New Data Source View.
13. Read the first page of the Data Source View Wizard and click Next.
14. Select the Adventure Works DW data source and click Next.
15. Select the dbo.FactInternetSales table in the Available Objects list and click the > button to move it to the Included Object list.
16. Click the Add Related Tables button to automatically add all of the tables that are directly related to the dbo.FactInternetSales table.
17. Click Next.
18. Name the new view InternetSales and click Finish.
19. Right-click on the Cubes folder in Solution Explorer and select New Cube.
20. Read the first page of the Cube Wizard and click Next.
21. Select the option to build the cube using a data source.
22. Check the Auto Build checkbox.
23. Select the option to create attributes and hierarchies.
24. Click Next.
25. Select the InternetSales data source view and click Next.
26. Wait for the Cube Wizard to analyze the data and then click Next.
27. Click Next.
28. Accept the default measures and click Next.
29. Wait for the Cube Wizard to detect hierarchies and then click Next.
30. Accept the default dimension structure and click Next.
31. Name the new cube InternetSalesCube and click Finish.
32. Select Build > Deploy AdventureWorksCube2.
33. Right-click on the cube in Solution Explorer and select Browse.
34. Expand the Measures node in the metadata panel.
35. Drag the Order Quantity and Sales Amount measures and drop it on the Totals/Detail area.
36. Expand the Dim Sales Territory node in the metadata panel.
37. Drag the Sales Territory Country property and drop it on the Row Fields area.
38. Expand the Dim Product node in the metadata panel.
39. Drag the English Product Name property and drop it on the Column Fields area.
40. Expand the Dim Customer node in the metadata panel.
41. Drag the Marital Status property and drop it on the Filter Fields area.
42. Click the dropdown arrow next to Marital Status. Uncheck the S checkbox.

Figure 15-8 shows the finished cube.

Figure 15-8: The AdventureWorksCube2 cube