Templates SSRS Reports

In an attempt to standardise the look and feel of the SSRS Reports we produce, template for a Portrait and Landscape report can be generated.

The reports can have a ‘Technical Notes’ textbox which is hidden, any stored procedures\need to know info on the report can be stored here, date formatting, page formatting can be stored.

the location to be stored is

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

Now the reports can be added using the templates.

SQL Database Restore

SQL Database Restore

1. Copy the back file to the local server.

2. Open Microsoft sql server management studio.

3. Expand the database, right click on task --- click on restore database.

4. Under General page click on From device, select the file location and add.

5. Now click on the check box to restore.

6. Now click on the options page, click RESTORE WITH NORECOVERY option under recovery state.

7. Click ok.

--Note now you have the database backed up say upto last midnight at 12:00 . Now we need to add the transaction log back up upto current time say 11:40 am in the morning.

8. Now we need to repeat step 3 but select the .trn file (i.e., the transaction log back up file) please refer to my earlier post on SQL Backup.
9. Click on the check box for the restore upto what time you would like to restore, the date and time is mentioned if you scroll.

10. In the options please note to select click RESTORE WITH RECOVERY option under recovery state.

SQL Backup

SQL Database Backup Plan

Database backup and restore can be achived by the following


1. First step is to do the back up

BACKUP DATABASE [ManufacturingChina] TO
DISK = N'Z:\DATABASE\ManufacturingChina_backup.bak' WITH NOFORMAT,
INIT, NAME = N'ManufacturingChina_backup', SKIP, REWIND, NOUNLOAD, STATS = 10

--NO FORMAT - specifies that the media header should not be written on all volumes used for this backup operation. This is the default behavior.

--INIT - Specifies that all backup sets should be overwritten

--SKIP - Disables the checking of backup set expiration and name that is usually performed by the BACKUP statement to prevent overwrites of backup sets.

--NOREWIND -Used only with TAPE devices to improve performance when performing multiple backup operations to a loaded tape

--SQL Server will retain ownership of the tape drive until a BACKUP or RESTORE command is used with either the REWIND or UNLOAD option or the server is shut down.

2. step 2 is to do the log back up with INIT that is to overwrite

BACKUP LOG [ManufacturingChina] TO DISK =
N'Z:\DATABASE\ManufacturingChina\ManufacturingChina.trn'
WITH NOFORMAT, INIT, NAME = N'ManufacturingChina-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

3. the above two scripts to be on one job scheduled at says 12:00 midnight

4. now we need to do the log back up with NOINIT that is with append

BACKUP LOG [ManufacturingChina] TO DISK = N'Z:\DATABASE\ManufacturingChina\ManufacturingChina.trn' WITH NOFORMAT, NOINIT, NAME = N'ManufacturingChina-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO

5. Finally to keep the size of the database in tact

DBCC SHRINKFILE (N'ManufacturingChina_Log' , 786)

786 is the default size given in this example, you can provide the size based on the database default log size.

6. Step 4 and 5 can be scheduled on a separate job

SQL Job failure

This script identifies failure of sql jobs to the connected sql instance.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[up_sqljob_check] Script Date: 08/22/2009 09:22:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[up_sqljob_check]
as
begin
-- Variable Declarations
DECLARE @PreviousDate datetime
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT

-- Initialize Variables
SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 7 days
--select @PreviousDate
SET @Year = DATEPART(yyyy, @PreviousDate)
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)
SET @FinalDate = CAST(@Year + @Month + @Day AS INT)
--select @FinalDate
-- Final Logic
/*
SELECT j.[name],
s.step_name,
h.step_id,
h.step_name,
h.run_date,
h.run_time,
h.sql_severity,
h.message,
h.server
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = 0 -- Failure
AND h.run_date > @FinalDate
and enabled = '1'
ORDER BY h.instance_id DESC
*/
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML = N'

SQL Job Failures on 34 server

' +
N' ' +
N' '+
-- N' ' +
N'' +
CAST ( ( select distinct
'2' "td/FONT/@Size", j.name "td", ''
-- '2' "td/FONT/@Size", h.message "td", ''
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = 0 -- Failure
AND h.run_date > @FinalDate
and enabled = '1'
--ORDER BY h.instance_id DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'

Name	Message

' ;
declare @count numeric
SELECT @count = count( distinct j.[name] )
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = 0 -- Failure
AND h.run_date > @FinalDate
and enabled = '1'
select @count
if @count > 1
begin
EXEC msdb.dbo.sp_send_dbmail
@recipients = '',
--@copy_recipients = '',
@subject = ' SQL Job failures on 34 server',
@body = @tableHTML,
@body_format = 'HTML' ;
end
end
--select * from MSDB.dbo.sysjobs

OLAP Cube Processing

Olap cube processing

1. The olap cube and the dimensions has to be prcessed to have the updates.
2. This can be done manually or automatic.
3. Manual process --- to open sql server management studio, connect to analysis services, click on the analysis database dimensions first --- dimensions will be listed on the explorer details window on the side, select all right click and process.
4. Now click the dimension --- right click and process.
5. If there are errors in processing please check you data first, for example primary key validation, datetime format.

Now let us see how to automate the process.

1. Using the manual process above click on script to generate the scripts. copy in a notepad.

2. Open microsoft sql server management studio.

3. Connect to the database.

4. Open sql server agent.

5. Click on jobs --- right click and click on new job.

6. Clcik on general --- enter the name of the job, please not you should rights on the database to perform this job.

7. Now click on the step --- click new step, enter the step name, select the type as sql server analysis services command , under run as -- select sql agent service account, now copy the scipt under the command, click ok

8. Click on the schedule -- schedule it.

9. Click on notifications --- select the mail to be notified if the job fails.

Create a olap cube

To create a olap cube

The following is advised to be known
1. Please make sure the tables to be used are in line with your requirement. for example - sales analyis in a company by customer, by product type, by reps and by jobnumber.

2. Also have the calendar table included to view by year,quater,month and date.

3. To create this table please refer to my earlier post in SSAS.

4. After this is done open the sql server business intelligence studio.

5. Click new project --- business intelligence projects --- analysis project --- under browse select the location where the project is been saved.

6. In the data sources --- create a new datasource, use the windows authentication account. In the data source view --- create a new data source select the tables to be used in the cube.

7. In the cube right click and follow the wizard, click on build the cube using a data source, click next --- select the fact (are measure where the numeric values stored e.g., grossprice, totalnet price ) and dimension tables (used for filter e.g., by product type, by invoicdate) --- clik next
now select the measures to be displayed, click next --- it will required to select atleast one dimension ( note dimensions can be created later) --- click next --- give a name for the cube and finish.

8. Now having created the cube with one dimension additional dimensions can be created, but before, click on the green arrow to process the cube, you might get an error if you have not set the deployment server. Go to menu project --- properties and click on deployment and specify the server name e.g., 192.0.0.111\MSSQ1 do not forge the instance name.

9. Now press F5 or click on the green arrow and process the cube, you should see something similar to image below.

10. Addition dimensions can be added dimension right click --- new dimension --- click next --- build the dimension using a new source -- check on autobuild --- click next --- click on standard dimension or time dimension --- click next --- select the table --- check the field you need to filter --- e.g., by product type.

repeat step 10 for additional dimensions.

Hosting OLAP Reports

To host olap reports on windows server 2008 using IIS Manager

Make sure you have the olap cube on the asp.net project and wanted to host, follow the steps below.

1. Copy the application folder containing the bin, obj, my project folder and files default.aspx,defualt.aspx.vb, to the c:\inetpub\wwwroot

2. Go to administrative tools --- iis manager

3. Open default web site --- click the folder you had copied.

4. Right click the folder you had copied --- click convert to application.

5. Change the application pool to --- Classic .NET AppPool

6. Make the sure the folder in c:\inetpub\wwwroot\ has appropriate rights to access the hosted application.