IS - Protection Level

SSIS - Export package to MSDB to with protection level

Relay on server storage and roles for access control

AS - Create Dimensions

Dimension Data: Before designing the Dimension, thoroughly familiarize the data related to the functioning organization.
In AdventureworksDW database DimEmployee table has foreign key relationship between ParentEmployeeKey column and EmployeeKey column and this Self Referencing relationship indicates that Employee is parent-child dimension.
Right click on the table and select "Explore Data" to view the data in different forms (Table, Chart,Pivot Table and Pivot Chart).

Creating Standard Dimension:

Must use the "Dimension Wizard" to create the initial structure of the dimension. Though the Wizard, you will select data source tables, the key attributes and other attributes that you want to include in the dimension.

Most cases the wizard design will not complete. After creating the dimension design, then wizard can be used repeatedly to enhance the dimension.

Creating Dimension include following steps:

1) Selecting the Creation Method

  

2) Specify Source Information, select the Data Source view and then Main Table list all the tables that are part of the Data Source View. The Main table of dimension must contain the column(s) you will use to create the key attribute of dimension.
In this Scenario, Select the "DimProduct" table as Main table, by default the "Key column" and "Name Column" displays primary key "ProductKey" as the Key Attribute column.
Every attribute has a member name. The member name is the text appears in a report or a web browser, so these names to be meaningful and descriptive name and another column that has an integer key that uniquely identifies each member. This is particularly true for key attributes.
Every attribute has two properties:
(i) KeyColumns references the column (or columns, in the case of a composite key) that uniquely identifies an attribute member
(ii) NameColumn references the column that contains the descriptive labels.
For many key attributes, the integer key will be used as KeyColumns and another descriptive column will be used as NameColumn. By default, Analysis Services uses the same column for the member key and for the member name. You can, however, specify a member name that's different that the key. Since the ProductKey column is just a unique integer that doesn't convey any information about the product to report viewer, you will choose the ProductID column that contains the product's catalog number and date the product was introduced to be the attribute name as the name of the attribute.

 

AS – Create a DS and DSV

Create an Analysis Services Project:

 

Create a Data Source:
-Data Source contains information that Analysis Services uses to connect to Source data base.
-Data Source Wizard can be used to create a Data Source.
-Creating Data Source contains following steps:
i) Data Provider Name  (Ex: Native OLE DB)
ii) Server Name            (Ex: BISERVER\MS2012SQL)
iii) Database Name      (Ex: AdventureWorksDW2012)
iv) Authentication Credentials

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Create a Data Source View:
-Data Source View is a logical data model that exists between physical data source and Analysis Services Dimensions and Cube.
-Data Source View Wizard can be used to select the Tables and Views (Meta Data) from the source database that contains data to build the Dimensions and Cube.
-Using “New Named Columns”, new column can be appended, parse data from column, or perform calculations using SQL Expression.

-Using “New Named Query”, New view/table can be added by grouping, filtering or join data from multiple tables using SQL SELECT statements.
Creating Data Source View involves following steps:

 

Right click on the (Data Source View) Diagram Pane and from the list menu can perform from the following :

Select the “New Named Query” to creates a View from the query as follows:

Using “Create Named Query “ wizard, View can be created. New relationship (Primarykey/FoerignKey) can also be created from this view to other related table.

Right click on the (Data Source View) Dimension\Fact table and can be select from list of menu as the following:

Select the “New Named Calculation”, to create a new column from SQL Expression. Using “Create Named Calculation” wizard, create column “DoubleUnitPrice”, and it shows as follows:

After Creating Data Source, Data Source view and Diagram Organizer the Solution Explorer looks as follows:

IS - ScriptTask - Protective Excel

I have fallen in to a scenario where i needed to create multiple Excel data files  from a source.   This task i was enjoyed doing. Because of a Script Task which is totally on creation of Protetctive Excel sheets, Locking some columns and coloring required column cells. I thought it is a good project to share with you all.
--> Source DB: Adventureworks database
--> Table that i would like to split in to multiple ExcelSeets:
--> Create multiple Excel sheets bassed on distinct Item. ex: File name will be like :   Item_20101105.xls

Option Strict Off
Option Explicit On
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Public Sub Main()

Dim xlApp As Object

Dim xlBook As Object

Dim xlWorkSheet As Object

Dim objRange As Object

Dim strExcelFile As String

Dim fullfilename As String

Dim ClientName, CurrentDate, SchoolOPEID As String

ClientName = CStr(Dts.Variables("ClientName").Value)

CurrentDate = CStr(Dts.Variables("CurrentDate").Value)

SchoolOPEID = CStr(Dts.Variables("SchoolID").Value)

fullfilename = ClientName + "_NewPlace_" + CurrentDate + "_" + SchoolID + ".xls"

Try

strExcelFile = "C:FlatFileSource\" + fullfilename

xlApp = CreateObject("Excel.Application")

xlBook = xlApp.Workbooks.Open(strExcelFile)

If WorksheetExists(xlBook, "Excel_Destination") Then

xlWorkSheet = xlBook.Sheets("Excel_Destination")

' Color and format title row

objRange = xlWorkSheet.Range("A1", "BW1")

objRange.Font.Size = 11

objRange.Font.Bold = True

objRange.Interior.ColorIndex = 16

objRange.Font.ColorIndex = 1

'Adjust titles to show up

objRange.EntireColumn.Autofit()

'Color required field using following code.

' Dim myCount As Integer

' myCount = xlWorkSheet.UsedRange.Rows.Count

Const xlEdgeLeft = 7

Const xlContinuous = 1

Const xlAutomatic = -4105

Const xlThin = 2

Const xlGray16 = 17

Const xlHairline = 1

objRange = xlWorkSheet.Range("E1:K1", "E65535:K65535")

'objRange.Interior.ColorIndex = 3

objRange.Borders.LineStyle = xlContinuous

objRange.Borders.ColorIndex = 3

objRange.Borders.Weight = xlThin

xlWorkSheet.Columns("AI:AI").NumberFormat = "0,#"

objRange = xlWorkSheet.Range("BX:ET", "BX65535:ET65535")

objRange.Columns.Delete()

' Following will lock all columns except specified.

xlWorkSheet.Unprotect()

Dim strmypassword

xlWorkSheet.Protection.AllowEditRanges.Add("FirstSet", xlWorkSheet.Columns("E:AP"))

xlWorkSheet.Protection.AllowEditRanges.Add("SecondSet", xlWorkSheet.Columns("BE"))

xlWorkSheet.Protect(strmypassword)

xlBook.Save()

End If

Catch e As Exception

MsgBox("ERROR:" & e.ToString, MsgBoxStyle.Critical)

Finally

If Not xlBook Is Nothing Then

xlBook.Close()

xlBook = Nothing

End If

If Not xlApp Is Nothing Then

xlApp.Quit()

xlApp = Nothing

End If

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

Function WorksheetExists(ByRef xlWorkbook As Object, ByVal strWorksheetName As String) As Boolean

Dim xlWorksheet As Object

If xlWorkbook Is Nothing Then

WorksheetExists = False

Else

xlWorksheet = xlWorkbook.Sheets(strWorksheetName)

WorksheetExists = Not xlWorksheet Is Nothing

End If

End Function

End Class

SQL Assesment

Here is a SQL Server Exercise:

At the beginning of this school year, the school A has
+ 30 teachers
+ 45 classes
+ 500 students
1. A teacher can be assigned to teach multiple classes. However, a teacher may not be assigned to teach any class. Teacher Information is stored in table "TEACHER"
2. A class can only be taught by one teacher. However, a class may not have any teacher assigned to it yet. A class can have multiple students. But a class may not have any student registered to attend yet. Class information is stored in table "CLASS"
3. A student can attend multiple classes. However, a student may not yet attend any class. Student information is stored in table "STUDENT"
4. Information of student registration is stored in table "CLASS_STUDENT"
Below are the examples of school records stored in the above 4 relational tables.
TEACHER:

 
CLASS:

STUDENT:

CLASS_STUDENT:

 
1) Write a SQL script that creates a table called "STUDENT_HOBBIES". This table should contain
a. Hobby ID of type integer. This field should be an IDENTITY column that starts from 100 and increments by 1.
b. Student ID of type integer
c. Hobby of type character. This field could contain up to 50 characters
2) Write a SQL statement that displays the name of all classes in the school A, together with the name of the corresponding teacher that is assigned to teach the class. If no teacher is assigned to a class yet, display "N/A" as teacher name. Below are the sample results

3) Write a SQL statement that displays the class_id, class_name, total students attending that class, and a message saying "Under Size" if there are less than 2 students in the class; otherwise, the message should be "Normal Size". Below are the sample results

4) Write a SQL script that displays the list of all the teachers that have not been assigned to any class.
5) Write a SQL script that checks if table "STUDENT" does not have a column called "gender", adds column "gender" of type integer to the table.
6) Documentation of the database showed that no one had manually created any index on table "TEACHER", but when the DBA attempted to create one using the following SQL statement He got the following error:
Why can’t table "TEACHER" have more than one CLUSTERED INDEX?
7) Consider the following table that is unique on prospect_id, but may have duplicates household_ids. Write SQL code to create another table that is unique by household_id and retain all columns that the original table have. For households from the original table that have more than one prospect_id, retain the record with the lowest model tile.

 
8) There's a flat text file that contains the information of book title, author, and published date. Below is the file format

Below is the sample of the flat text file.

 
a) How would you load this file into a table called "BOOK" using a method other than DTS/SSIS package.
b) There are duplicates on book title , author name . Either write a script or briefly describe the method you would use to remove those duplicates from the table "BOOK" (only keep the record that has the latest published_date )
c) Over time, table "BOOK" has grown to have 120 columns. Write a SQL script that displays the comma separated list of all the columns in table "BOOK"

IS–Parameters and Return Codes in Execute SQL Task

 

 

Connection Type	Parameter marker	Parameter name	Example SQL command
ADO	?	Param1, Param2, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
ADO.NET	@<parameter name>	@<parameter name>	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID
ODBC	?	1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
EXCEL and OLE DB	?	0, 1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

TSQL - IDENTITY Property

IDENTITY is pretty much a very important property of a database. In post i will different places the use of (Creation, Seeding and Reseeding) IDENTITY property.
1) SQL Server allows only ONE identity column per table.
       Ex:
       Create Table #Identity_Post (RankNumber int identity(1,1),
                                                    First_Name varchar(40))
2) Identity(seed,increment)
      Ex: identity(1,1) means starting number is 1 and it will add 1 to next number
      identity(1000,5) means starting number is 1000 and it will add 5 to next number

3) SET IDENTITY_INSERT [Table_Name] [ON/OFF]
This property is one very usefull, When we want to insert a deleted rowid  (identity number) again in the table we can use this property.
     Ex:
     CREATE TABLE #TempTable (Rowid int IDENTITY(1,1), Name nvarchar(20))
     INSERT INTO #TempTable (Name) VALUES ('BiSpecialist')
     INSERT INTO #TempTable (Name) VALUES ('BwSpecialist')
     INSERT INTO #TempTable (Name) VALUES ('IsSpecialist')
     INSERT INTO #TempTable (Name) VALUES ('RsSpecialist')

     ----Delete a row so that we can create a gap in between the rows.

     DELETE from #TempTable WHERE Name = 'RsSpecialist'

     SELECT * FROM #TempTable

     ----Now it will through an error    

     INSERT INTO #TempTable (Rowid, Name) VALUES(4, 'AsSpecialist')

     ----SET IDENTITY_INSERT to ON.    

     SET IDENTITY_INSERT #TempTable ON

     ----Now its a identity magic    

     INSERT INTO #TempTable (Rowid, Name) VALUES(4, 'AsSpecialist')

     ----Then SET IDENTITY_INSERT  OFF, so that next time it won't allow the explicit insert.    

     SET IDENTITY_INSERT #TempTable OFF

     SELECT * FROM #TempTable

     DROP TABLE #TempTable

 

4) DBCC CHECKIDENT  (Database Console Command)
http://msdn.microsoft.com/en-us/library/aa258817(v=sql.80).aspx
DBCC CHECKIDENT
( table_name   [, { NORESEED | { RESEED [, new_reseed_value ] } } ]  )  [WITH NO_INFOMSGS]

 

Argument	Description
table_name	Is the name of the table for which to check the current identity value. The table specified must contain an identity column. Table names must comply with the rules for identifiers.
NORESEED	Specifies that the current identity value should not be changed.
RESEED	Specifies that the current identity value should be changed.
new_reseed_value	Is the new value to use as the current value of the identity column.
WITH NO_INFOMSGS	Suppresses all informational messages.

Parameter Specifications:

DBCC CHECKIDENT statement	Identity correction(s) made
DBCC CHECKIDENT ('table_name', NORESEED)	The current identity value is not reset. DBCC CHECKIDENT returns a report indicating the current identity value and what it should be.
DBCC CHECKIDENT ('table_name') (or)  DBCC CHECKIDENT ('table_name', RESEED)	If the current identity value for a table is lower than the maximum identity value stored in the column, it is reset using the maximum value in the identity column.
DBCC CHECKIDENT ('table_name', RESEED,new_reseed_value)	Current identity value is set to the new_reseed_value. If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value. If the table is not empty, setting the identity value to a number less than the maximum value in the identity column can result in one of the following conditions:   If a PRIMARY KEY or UNIQUE constraint exists on the identity column, error message 2627 will be generated on later insert operations into the table because the generated identity value will conflict with existing values. If a PRIMARY KEY or UNIQUE constraint does not exist, later insert operations will result in duplicate identity values.

Exceptions: The following table lists conditions when DBCC CHECKIDENT does not automatically reset the current identity value and provides methods for resetting the value.

Conditions	Reset Methods
The current identity value is larger than the maximum value in the table.	Execute DBCC CHECKIDENT (table_name, NORESEED) to determine the current maximum value in the column, and then specify that value as the new_reseed_value in a DBCC CHECKIDENT (table_name, RESEED, new_reseed_value) command.  (or)   Execute DBCC CHECKIDENT (table_name, RESEED, new_reseed_value) with new_reseed_value set to a very low value, and then run DBCC CHECKIDENT (table_name, RESEED) to correct the value.
All rows are deleted from the table.	Execute DBCC CHECKIDENT (table_name, RESEED, new_reseed_value) with new_reseed_value set to the desired starting value.

User Defined Functions

As part of SQL Server, there are two types of functions.

1) Built-In/System Function

2) User defined functions

In this post, I will discuss about the User Defined Functions:

User defined Function,is like a TSQL program/Procedure that accepts the parameters and provides shortcut results through programming shortcuts

System Databases

There are 5 system databases.

1) master: Stores the Metadata of the SQL server instance level information.