"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

July 31, 2011

Maps Example - SQL Server 2008 R2

In this example we will look at SSRS reports using Maps. Beginning Spatial is very good place to get started on learning Spatial Data types and maps. We will look at simple example of consuming below query provided in beginning spatial to consume in report.


DECLARE @table1 TABLE( id CHAR(1), shape geometry );
INSERT INTO @table1 VALUES
('A','POLYGON((0 0, 4 0, 4 1, 0 0))'),
('B','POLYGON((4 2, 3 2, 3 3, 4 2))'),
('C','POLYGON((7 3, 7 1, 8 1, 7 3))'),
('D','POLYGON((8 3, 11 3, 11 5, 8 3))');

SELECT
id,
shape
FROM @table1;


Let's get started

Step 1- Create blank report and add map as shown below


Step 2 - Add Dataset
Step 3 - Add Data Source


Step 4 - Add below query


Step 5 - Ignore Error


Step 6 - Choose Spatial Data

Step 7 - Map visualization option
Step 8 - Choose Dataset to Display

Step 9 - Data visualization and Scheme

Step 10 - Report Preview


Happy Learning!!!

July 30, 2011

SQL Server 2008 R2 Examples

List of Detailed Step-by-Step Examples on SQL Server 2008 R2 Features


Complete List of Exercises - SQL Server 2008 R2 - Reporting Services


Happy Learning!!!

BI Learning - Fundamental Concepts

We have covered couple of previous posts on BI Learning's. Currently I'm learning/reading book Expert Cube Development With MS SQL Server 2008. This book is very good starter for beginners as well. Below are my handy notes on Basic BI concepts.

Fundamental Concepts
  • Fact - Represents Action, Something that has happened. Example- No of units sold, No of Repairs completed.
  • Dimension - Dimensions are attributes. Example - Product Type, Department Type, Date. Natural key would be product code, department code. Surrogate key is integer number (Identity Column) generated in dimension table. Fact and dimensions relate via surrogate key. 
  • Slowly Changing Dimensions - Managing dimension attribute changes over time.
  • Datamart - Database containing fact and dimension tables
More Details notes and learning please get your copy of book Expert Cube Development With MS SQL Server 2008.

Looking forward for more BI exercises in coming weeks. From MSDN SQL Server Analysis Services Tutorial is my next learning point.
 
 
Happy Learning!!!

July 28, 2011

Datamodel Templates

If you are developing Data models for your organization, You can refer to readily available data models presented in database answers site. 900+ datamodels. Covers real time scenarios.

Another good read, Using SSMS for Data Modelling

Couple of Interesting Data Models 

Happy Learning!!

July 27, 2011

TSQL - Temptables using Dynamic SQL

Interesting learning on Temptables using Dynamic SQL. When I use temp tables in Dymanic SQL my table creation failed. I have simplified this into example provided below.

--Example Code
IF OBJECT_ID('tempdb..#T2') IS NOT NULL
    DROP TABLE #T2;
GO   
IF OBJECT_ID('dbo.T1') IS NOT NULL
    DROP TABLE dbo.T1;
GO
-- Test Tables
CREATE TABLE dbo.T1 (
a [int] NOT NULL,
b [int] NOT NULL)

--Populate Data
INSERT INTO T1 (a,b)
VALUES(10, 20),(10,30),(20,30)

DECLARE @sql nvarchar(max)
SET @sql = 'SELECT T1.a AS Col1, SUM(T1.b) AS Col2 INTO #T2 FROM T1 GROUP BY T1.a'
PRINT @sql 

EXEC sp_executesql @sql

SELECT * FROM #T2

When you execute this example you would receive error

--Error
Line 2: Msg 3701, Level 11, State 5:
Cannot drop the table '#T2', because it does not exist or you do NOT have permission.

There are two possible solutions for this. One is using global temptables (Use ## to make it global temp tables), second solution is create the table before calling insert records. I found similar examples while searching for this error.

Solution #1 - USE GLOBAL Temp Tables ##

--Example Code
IF OBJECT_ID('tempdb..##T2') IS NOT NULL
    DROP TABLE ##T2;
GO   
IF OBJECT_ID('dbo.T1') IS NOT NULL
    DROP TABLE dbo.T1;
GO
-- Test Tables
CREATE TABLE dbo.T1 (
a [int] NOT NULL,
b [int] NOT NULL)

--Populate Data
INSERT INTO T1 (a,b)
VALUES(10, 20),(10,30),(20,30)

DECLARE @sql nvarchar(max)
SET @sql = 'SELECT T1.a AS Col1, SUM(T1.b) AS Col2 INTO ##T2 FROM T1 GROUP BY T1.a'
PRINT @sql 
EXEC sp_executesql @sql
SELECT * FROM ##T2

Solution #2 - Based on post . CREATE the TABLE before calling the INSERT query


--Example Code
IF OBJECT_ID('tempdb..#T2') IS NOT NULL
    DROP TABLE #T2;
GO   
IF OBJECT_ID('tempdb..#T3') IS NOT NULL
    DROP TABLE #T3;
GO   
IF OBJECT_ID('dbo.T1') IS NOT NULL
    DROP TABLE dbo.T1;
GO

-- Test Tables
CREATE TABLE dbo.T1 (
a [int] NOT NULL,
b [int] NOT NULL)

CREATE TABLE #T2 (
a [int] NOT NULL,
b [int] NOT NULL)

CREATE TABLE #T3 (
a [int] NOT NULL,
b [int] NOT NULL)

--Populate Data
INSERT INTO T1 (a,b)
VALUES(10, 20),(10,30),(20,30)

DECLARE @sql1 nvarchar(max)
SET @sql1 = 'SELECT T1.a AS Col1, SUM(T1.b) AS Col2 INTO #T3 FROM T1 GROUP BY T1.a'

PRINT @sql1

DECLARE @sql nvarchar(max)
SET @sql = 'INSERT INTO #T2(a,b) SELECT T1.a AS Col1, SUM(T1.b) AS Col2 FROM T1 GROUP BY T1.a'

PRINT @sql

EXEC sp_executesql @sql1
EXEC sp_executesql @sql

SELECT * FROM #T2
SELECT * FROM #T3

Table#3 had 0 records, Table #2 contained correct data set.

Happy Learning!!!

Configuring Reporting Services in SQL Server 2008

For configuring Reporting Services in SQL Server 2008, Below posts would be useful

Reporting Service 2005 browsing to the home displays no report folders
Error (rsAccessDenied) - 'NT AUTHORITY\NETWORK SERVICE' are insufficient
The permissions granted to user ' are insufficient for performing this operation. (rsAccessDenied)


Happy Learning!!

July 24, 2011

TSQL - Manager - Manager - Employee Recursive CTE Query

This post is based on my MSDN forum question. I have to implement this logic in one of procedures. Query Approach seems to be fine based on comments. Necessary foreign key is already in place. One of stackoverflow post was useful for me to arrive at solution.

Below is the solution: Input is Employee Id, Output is list of Employees working for the Manager.

CREATE TABLE Employee_Manager
( EmployeeId [int] NOT NULL,
  ManagerId [int] NULL)
  

INSERT INTO [dbo].[Employee_Manager] ([EmployeeId], [ManagerId])
VALUES (1,2),(3,1),(4,1),(5,4),(6,4)

SELECT * FROM [dbo].[Employee_Manager]

Manager - Employee
2       -   1
1       -   3
1       -   4
4       -   5
4       -   6

/* Return list of employee when managerid is passed as input */
DECLARE @MgrId [int]
SET @MgrId = 4
;WITH EmployeeHierarchy(ManagerID, EmployeeID)
AS
(
      SELECT [EM].[ManagerID], [EM].[EmployeeID]
      FROM [dbo].[Employee_Manager] EM
      WHERE [EM].[ManagerID] = @MgrId
      UNION ALL
      SELECT [EM].[EmployeeID], [EM].[EmployeeID]
      FROM [dbo].[Employee_Manager] EM
      INNER JOIN [EmployeeHierarchy] EH ON
      EM.[ManagerID] = [EH].[EmployeeID]
)   
SELECT [EMH].[EmployeeID], [EMH].[ManagerID]
FROM [EmployeeHierarchy] EMH

Please feel free to comment on this post for any better solution...

More Reads
T-SQL Tuesday #18 - CTEs - The permission hierarchy problem


Happy Learning!!!

July 22, 2011

TSQL - Pivot without Aggregating Results

This is based on today's work. I had two tables Table A and Table B. I had to Pivot results of Table B and update the results. There were two more constraints
  • Table B can Have 0 to 10 Records for Each Id in Table A
  • We were using Entity Framework and Calling the Proc
  • For this I would need to provide the result set (Fixed Schema)
Below is the solution approach
  • For dynamic pivot this post was helpful to get started
  • Created a dummy record with ten results to achieve Fixed Schema Results. This record would be deleted before sending results
  • I was not aware till date how EF handling Stored Procs. This post was very useful as my proc returned error. Changed Setting to SET FMTONLY OFF based on the post
Getting to Actual Example 

--Step 1
IF OBJECT_ID('dbo.TableB') IS NOT NULL
    DROP TABLE dbo.TableB;

--Step 2
IF OBJECT_ID('dbo.TableA') IS NOT NULL
    DROP TABLE dbo.TableA;

CREATE TABLE TableA (
    Id   INT          IDENTITY (1, 1) PRIMARY KEY,
    NAME VARCHAR (50) NULL
);

CREATE TABLE TableB (
    Id       INT           NOT NULL,
    Comments VARCHAR (500) NOT NULL,
    FOREIGN KEY (Id) REFERENCES TableA (Id) ON DELETE NO ACTION ON UPDATE NO ACTION
);

IF OBJECT_ID('dbo.TableC') IS NOT NULL
    DROP TABLE dbo.TableC;

IF OBJECT_ID('tempdb..##TestResults') IS NOT NULL
    DROP TABLE ##TestResults;

CREATE TABLE TableC (
    Id         INT          NULL,
    Comments   VARCHAR (50) NULL,
    [RowCount] INT          NULL
);

--Step 3 
INSERT  INTO [dbo].[TableA] ([NAME])
VALUES                     ('A'),
('B'),
('C');

INSERT  INTO [dbo].[TableB] ([Id], [Comments])
VALUES                     (1, 'A Comments'),
(1, 'A Second Comments'),
(1, 'A 3rd Comments'),
(2, 'B 1st Comments'),
(2, 'B 2nd Comments'),
(2, 'B 3rd Comments');

--Step 4
WITH   CTE ([Id], [Comments], [RowCount])
AS     (SELECT [T].[Id],
               [T].[Comments],
               row_number() OVER (PARTITION BY [Id] ORDER BY [Id] ASC)
        FROM   TABLEB AS T)
INSERT INTO TableC (Id, Comments, [RowCount])
SELECT [CA].[Id],
       [CA].[Comments],
       [CA].[RowCount]
FROM   [CTE] AS CA;

--Step 5, Insert Dummy Records
INSERT  INTO [dbo].[TableC] ([Id], [Comments], [RowCount])
VALUES                     (999999, 'Test', 1),
(999999, 'Test', 2),
(999999, 'Test', 3),
(999999, 'Test', 4),
(999999, 'Test', 5),
(999999, 'Test', 6),
(999999, 'Test', 7),
(999999, 'Test', 8),
(999999, 'Test', 9),
(999999, 'Test', 10);

DECLARE @sql AS NVARCHAR (MAX);
SET @sql = N'SELECT [ID]';

SELECT   @sql = @sql + ',MAX(CASE WHEN [RowCount] =' + CAST ([RowCount] AS CHAR (5)) + ' THEN [Comments] ELSE '''' END) AS [Col' + CAST ([RowCount] AS CHAR (5)) + ']'
FROM     [dbo].[TableC]
GROUP BY [dbo].[TableC].[RowCount]
ORDER BY [dbo].[TableC].[RowCount];

SET @sql = @sql + N'
                    INTO ##TestResults FROM  [dbo].[TableC]
                    GROUP BY  [dbo].[TableC].[Id]
                    ORDER BY  [dbo].[TableC].[Id]';

EXECUTE sp_executesql @sql;

DELETE ##TestResults
WHERE  [Id] = 999999;

SELECT *
FROM ##TestResults;

Please feel free to comment this post for any better approach to handle this scenario.

More Reads

Dumping SQL data in pivoted format
SQL query to convert columns into rows

Happy Learning!!!

July 21, 2011

BI Learning - Developing First Cube

I'm trying things in a random fashion. Trying to play with AdventureWorks BI DB to create my first CUBE

Let's get Started.

Step 1. Create New Analysis Services Project

Step 2. Under Data Source, Create connection to AdventureWorks DW DB

Step 3 - Create new datasource view from AdventureWorks DW Database


Step 4. Removed Account table as it had no relation with our Fact table

Step 5 - Next step is create cube from existing table. Follow the rest of the steps and complete the process


Step 6 - Now Change Impersonation settings from DataSource. I received an error while trying to deploy, Changing the same to fix the error


Step 7 - Now Deploy the cube


Step 8 - After Deploying the CUBE goto cube browser, You would see below error. Fix this error by setting CUBE->Language-> English (US)



Step 9 - Now Drag and Drop few measures and dimensions



Step 10 - Now, We will look at using SSRS to report data from this cube. This will be in next post.

Step 11 - Create a new SSRS project


Step 12 - Add the SSAS data source to the Reports Project

Step 13 - Create a New Report based on this SSAS Data Source

Step 14 - Drag and Drop Measures and Dimensions as we selected in Step 9. Please note the parameter checkbox is checked to use this Dimension as input parameter for the report


Step 15 - Sample SSRS Report from the CUBE


More Reads
Below posts were helpful and good learning's to complete this exercise


Happy Reading!!

July 20, 2011

BI Learning - Getting Started with ETL

Next Step based for BI Learning Series is is ETL Design. I posted my question in MSDN SSIS forum and Stackoverflow.

Approach is provided in the question, This seem to work fine. I'm hoping for some more comments for the question.

For Error Handing, Error Handling can be done by package. All the packages can invoke this package on error. This learning is based on Question in MSDN forum.

Next Learning List
  • Passing elements from parent to child package in SSIS
  • Desigining the ETL solution for adventureworks DB based on proposed approach
SSIS Design Pattern - Custom Error Handling
Top 10 SQL Server Integration Services Best Practices
SQL Server Integration Services Standards
Faster Extraction & Loading by SSIS
SSIS – Copy file when Exists and Not Locked by another process
Dynamically creating Excel Sheets and populating data from SQL server tables
SSIS: Work Flow vs Stored Procedures
How accurate is your BI information? - Part 1
How accurate is your BI information? - Part 2
How accurate is your BI information? - Part 3
How accurate is your BI information? - Part 4
SSIS Error Handling
Building a Successful ETL Project
Microsoft SQL Server Integration Services Performance Design Patterns
Troubleshooting SSIS Package Performance Issues


Happy Learning!!