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!!!

SQL Server 2008 R2 Examples

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

• Creating a Map Report with a Spatial Data Query
• Creating a Chart with a Text Annotation in an ASP.NET Web Application
• Exporting a Report Feed from SQL Server 2008 R2 Reporting Services

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!!!

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 

• Shoppers Recommendations
• Retail management
• Data Model for Airline Reservations 
• Data Model for Product Catalogs
• Order Tracking

Happy Learning!!

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!!

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!!!

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!!!

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

SSRS Drillthrough for SSAS cubes, how to pass a parameter to an MDX dataset

Steps to create a SSRS report with MDX accessing a Cube

Calculated Members in SSAS Cube

Create a SSRS Report based on a Cube and Deploy it

MDX Solutions from Field, for the Field

Happy Reading!!

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

More Reads

Creating a Solid SSIS ETL Solution: Part 1 – Package and Error Logging
Creating a Solid SSIS ETL Solution: Part 1 – Package and Error Logging (cont.)
Creating a Solid SSIS ETL Solution: Part 2 – Package Configurations
Creating a Solid SSIS ETL Solution: Part 3 – Package Template Overview
Creating a Solid SSIS ETL Solution: Part 4 – Email Task and Package Execution Report
SSIS Design Pattern - ETL Instrumentation, Part 1

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!!