SSAS NamedSet for FiscalTime and CalendarTime

Here i have described some the useful Fiscal and Calendar NamedSet

1. Last Years
2. Last Quartes
3. Last Months
4. Current Month
5. LastDays
6. Current Fiscal Months
7. Fiscal Months
8. Last Calendar Month

Calendar Time Namedset
1. Last Years:
ORDER(
LASTPERIODS(3, StrToMember("
[Date].[Time].[Year].&["
+ Format(Now(),"yyyy")
+"]"
))
,[Date].[Time].CURRENTMEMBER.PROPERTIES("ID", TYPED), BDESC)


2. Last Quarters


ORDER(
LASTPERIODS(4, StrToMember("[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "].&["
+ Format(Now(), "yyyy")
+ "]&["
+ cstr(datepart( "q", Now()))
+ "]"))
,[Date].[Time].CURRENTMEMBER.PROPERTIES("ID", TYPED), BDESC)


3.LastMonthsORDER(
LASTPERIODS(6, StrToMember("
[Date].[Time].[Year].&["
+ Format(Now(),"yyyy")
+"].&["
+ Format(Now(), "yyyy")
+"]&["
+ cstr(datepart("q", Now()))
+"].&["
+ Format(Now(), "yyyy")
+"]&["
+ Format(Now(),"MM")
+"]"
))
,[Date].[Time].CURRENTMEMBER.PROPERTIES("ID", TYPED), BDESC)


4.Current Month:

StrToMember("
[Date].[Time].[Year].&["
+ Format(Now(),"yyyy")
+"].&["
+ Format(Now(), "yyyy")
+"]&["
+ cstr(datepart("q", Now()))
+"].&["
+ Format(Now(), "yyyy")
+"]&["
+ Format(Now(),"MM")
+"]"
)


5. LastDays


ORDER(
LASTPERIODS(30, StrToMember("
[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "].&["
+ Format(Now(), "yyyy")
+ "]&["
+ cstr(datepart( "q", Now()))
+ "].&["
+ Format(Now(), "yyyy")
+ "]&["
+ Format(Now(), "MM")
+ "].&["
+ Format(Now(), "dd")
+ "]"
))
,[Date].[Time].CURRENTMEMBER.PROPERTIES("ID", TYPED), BDESC)


6.Current Fiscal Months



ORDER(
StrToMember("[Date].[Fiscal Time].[Fiscal Year].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[1].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[1]")
:
StrToMember("[Date].[Fiscal Time].[Fiscal Year].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN CSTR(CINT(DATEPART("q", Now())) - 1)
ELSE CSTR(CINT(DATEPART("q", Now())) + 3) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN Format(Now(),"MM") >= 4 THEN CSTR(CINT(Format(Now(),"MM")) - 3)
ELSE CSTR(CINT(Format(Now(),"MM")) + 9) END + "]"
)
,[Date].[Fiscal Time].CURRENTMEMBER.PROPERTIES("ID", TYPED), DESC)


7. Fiscal Months
ORDER(
LASTPERIODS(3, StrToMember("[Date].[Fiscal Time].[Fiscal Year].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN CSTR(CINT(DATEPART("q", Now())) - 1)
ELSE CSTR(CINT(DATEPART("q", Now())) + 3) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN Format(Now(),"MM") >= 4 THEN CSTR(CINT(Format(Now(),"MM")) - 3)
ELSE CSTR(CINT(Format(Now(),"MM")) + 9) END + "]"
)),[Date].[Fiscal Time].CURRENTMEMBER.PROPERTIES("ID", TYPED), DESC)


8. Last Calendar Month


STRTOMEMBER("

[Date].[Time].[Year].&["

+ Format(Now(),"yyyy")

+"].&["

+ Format(Now(), "yyyy")

+"]&["

+ cstr(datepart("q", Now()))

+"].&["

+ Format(Now(), "yyyy")

+"]&["

+ Format(Now(),"MM")

+"].PREVMEMBER"

)

Handle SSIS lookup failure on Date Dimension Date column NULL value

Handle Lookup failure when look up null value in Date dimension.

Problem : 

Lookup fail when look up null value in Date dimension.

I have date Dimension table with one row for Null value. In my OLTP Order transaction data some of the date data type column has null value.

I am getting the lookup key not found error and those rows are redirected into reject table. Actually both null was not matched up and not able to found the NULL value dimension key.

SOLUTION :

So in this blog I have explained the customized date look up script with the following screen shots..

Note(Alternate solution):

I don’t want to replace the OLTP date column NULL value with some default date (like 1/1/1900). If I do so in the reporting layer I have to replace the default date (1/1/1900) into NULL. Otherwise this will be misleading in the report also I feel this approach is overhead.

Database & SSIS : SQL Server 2008

Table        ColumnName                            DataType
Date_D    Date(lkp column)                        Date      
Order       ShippingDate(Source column)     Date      

 
select date_Key, date from [dbo].[Date_D]
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

 
 
select * from (select date_Key, isnull(date,'1/1/1900') as date from [dbo].[Date_D]) [refTable]

where [refTable].[date] = ISNULL(?,'1/1/1900')
 
 
 
 
 
 
 
 
 
 
   

I am not sure about the performamce of Date dimenison look up. I am working on this...Since its a date dimension volume is not the consideration.

 

I hope this would be helpful to all.

Wednesday, September 29, 2010

Create Measure for DateTime DataType in SSAS

*** Self reference entry by Liyasker **
There is a requirement to create the Measure for User InTime Hrs, OutTime Hrs, CheckInTime, CheckOutTime, AVGCheckInTime with HH:MM AM/PM format.

But SSAS measure will support only for Integer and Numeric. Our case I have to expose the cube measure in to MS EXCEL 2007. There we can’t do any conversion.

I got the right help from the following blog of John (Thanks John :)). Please go through this, this will help you.

http://sqlblogcasts.com/blogs/drjohn/archive/2007/12/03/ssas-working-with-date-and-time-measures-to-calculate-average-elapse-time.aspx

I create a Facttable view and did the float conversion like below
,CONVERT(FLOAT,CAST(InTime_InHrs AS DateTime)) + 2 AS OfficeInTime
,CONVERT(FLOAT,CAST(OutTime_InHrs AS DateTime)) + 2 AS OfficeOutTime
,CONVERT(FLOAT,CheckInDateTime) + 2 AS InDateTime
,CONVERT(FLOAT,CheckOutDateTime) + 2 AS OutDateTime

Then I apply the Time format in SSAS Measure Properties.

I hope this will help to calculate DateTime Measure in SSAS.
 
 
 
 
 
 
 
 
 
 

SSRS 2008 Tutorial: Publishing a Report

Creating reports in Business Intelligence Development Studio is good for developers, but it doesn't help users at all. In order for the reports you build to be available to others, you must publish them to your Reporting Services server. To publish a report, you can use the Build and Deploy menu items in Business Intelligence Development Studio. Before you do this, you need to check the project's configuration to make sure that you've selected an appropriate server for the deployment.
Try It!
You can publish any report, but the first report you created is probably more visually interesting at this point. To publish the first report, follow these steps:

1. Select File > Recent Projects and choose your ProductReport1 project.
2. Select Project „ ProductReport1 Properties.
3. Click the Configuration Manager button.
4. Fill in the Target Server URL for your Report Server. If you're developing on the same computer where Reporting Services is installed, and you installed in the default configuration, this will be http://localhost/ReportServer. Figure 17-7 shows the completed Property Pages.
   
   
   
   
   
   
   
   
   
   
   
   

   
   

   
   

   
   

   
   

   
   

   
   

   Figure 17-7:  Setting the active configuration
5. Click OK.
6. Select Build > Deploy ProductReport1. The Output Window will track the progress of BIDS in deploying your report, as shown in Figure 17-8. Depending on the speed of your computer, building the report may take some time.
   
   
   
   
   
   
   
   
   
   
   
   
   

   Figure 17-8: Setting report project properties
7. Launch a web browser and enter the address http://localhost/reports.
8. It may take several minutes for the web page to display; Reporting Services goes to sleep when it hasn't been used for a while and can take a while to spin up to speed. Figure 17-9 shows the result.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

   Figure 17-9: Deploying a report
9. Click the link for the ProductReport1 folder.
10. Click the link for the ProductReport1 report.

Select ALL in parameter of SSRS report

Select ALL as parameter value is one of the most common functionality which most of the reports have and there are number of ways to implement it.

I will explain below the way I do it and I find easiest. The driver of the solution is CASE option under WHERE clause of SELECT DataSet Query.

SELECT * FROM TableName
WHERE
(
CASE
WHEN @RepParam <> 'ALL' AND ColName= @RepParam THEN 1
WHEN @RepParam= 'ALL' THEN 1
END
) = 1 ;

Lets see it through an simple Student Table example where we will Select student either on the basis of the Grade they are in or select all of them.

1. Records in Student table are

2. Create a simple report with DataSet Student as SELECT * FROM Student;

3. To add the Grade parameter and option for Select ALL

3.a. Create a DataSet for available Grades for Report parameter

3.b. Configure a Report parameter "Grade" as

3.c. Modify query for Student Dataset to allow filtering on Grades as

4.a. Run the Report for ALL grades

4.b. Run the Report for grade - X

Get Rid of XML Special Characters in XML at SQL Query

The Below is the example in which the special characters in the database are populated as Extra character in XML, such as for & , it shows &amp.

select
stuff(
(select ', <' + name + '>'
from sys.databases
where database_id > 4
order by name
for xml path('')
)
, 1, 2, '') as namelist;


The solution for this is use of
root('MyString'), type
).value('/MyString[1]','varchar(max)') in the code as shown below


select
stuff(
(select ', <' + name + '>'
from sys.databases
where database_id > 4
order by name
for xml path(''), root('MyString'), type
).value('/MyString[1]','varchar(max)')
, 1, 2, '') as namelist;

Hence the extra characters of the special character are eliminated by this type of usage.

Note: The XML is used in the code, to print the comma separated values.

The original code is given below

select '; '+

case when Gokul_Org.Location IS null then Ltrim(rtrim(Gokul_Quit.Location_Name))

else Ltrim(rtrim(Gokul_Org.Location)) END

+':'+Ltrim(Rtrim(Gokul_Quit.City_Name))+','+Ltrim(rtrim(Gokul_Quit.State))+' '

from local.image.dbo.orders O

join local.image.dbo.Quit Gokul_Quit

On O.Id=Gokul_Quit.order_id and O.Shipper_quit_id <> Gokul_Quit.Id

and Gokul_Quit.quit_type ='PU'and O.Company_id=Gokul_Quit.company_id

Left Join local.image.dbo.edilocation Gokul_Org

on Gokul_Quit.Location_id=Gokul_Org.location_id and Gokul_Quit.Company_id=Gokul_Org.Company_Id

and Gokul_Org.partner_id = 'HTEDRF'and Gokul_Org.Company_Id='TMS2' and Gokul_Org.version = '004030'

and Gokul_Org.Location_Type in ('S','W')

where O.Id=Orders.Id and O.Company_Id=orders.company_id

FOR XML PATH('')),2,200000) AS 'Gokul_Location'

Using CROSS APPLY in SQL Server 2005

My interest in writing this article was started by an MSDN article titled SQL Server 2005: The CLR Enters the Relational Stage. The article shows how to write a function that returns the top three countries per category. That's always been something that was difficult to do in SQL so I was curious about the approach. The article started out well but I was very unhappy by the end. It's just soooo much easier to do this in SQL Server 2005 using the new CROSS APPLY clause in Transact-SQL. So I'm going to write a query to return the top 3 orders for each customer and I'm going to do it in about 10 lines of SQL. (UPDATE: An alert reader found an even better approach!)

First please take a second to read the MSDN article. Pay special attention to how much C# code is required for the solution and how flexible it is. Note: I also want to mention here that I started out trying to replicate their solution. I tried to install the MSI but that failed with an invalid characters message. I tried to recreate the CLR code but since he mostly posted snippets and had three versions I finally gave up trying to get that to work. One thing I really wanted to do was compare performance on identical data. Unfortunately I wasn't able to do that. My example will use the AdventureWorks database

Let's start at the TOP

Along the way to making this solution work we'll discuss a few new features of SQL Server 2005. The first is that the TOP clause can now take a variable:

DECLARE @Rows INT SET @Rows = 10  SELECT TOP ( @Rows ) * 

FROM Sales.SalesOrderHeader

This will return the top 10 rows from SalesOrderHeader. You can also replace @Rows with anything that evaluates to a number. The following query looks odd but runs just fine:

SELECT TOP (   SELECT COUNT(*)  FROM Sales.Customer    ) * 

FROM Sales.SalesOrderHeader

There are 19,185 rows in the Customer table and this query returns the top 19,185 rows from SalesOrderHeader. You can also use the TOP clause for INSERT, UPDATE and DELETE statements. If you wanted to DELETE in batches of 500 you can now do that using the TOP clause.

My Function

Next we need a function to return the TOP X rows from SalesOrderHeader based on the total sales amount. That function looks like this:

CREATE FUNCTION dbo.fn_GetTopOrders(@custid AS int, @n AS INT) 

RETURNS TABLE AS RETURN   SELECT TOP(@n) *   FROM Sales.SalesOrderHeader 

WHERE CustomerID = @custid   ORDER BY TotalDue DESC GO

Notice that it accepts the number of orders to return as a parameter. Also notice that I'm using SELECT * all over the place. I really encourage you to explicitly list out the columns when you write this for real. If you call this with a CustomerID and a number of rows it will return that many rows ordered by the total amount of the order in descending order. Also notice that there is an ORDER BY clause in this function. We'll talk about that in a minute.

Apply the APPLY Clause

The real magic happens when you use SQL Server 2005's new APPLY clause. The APPLY clause let's you join a table to a table-valued-function. That let's you write a query like this:

SELECT  C.CustomerID,   O.SalesOrderID,  O.TotalDue 

FROM   AdventureWorks.Sales.Customer AS C 

CROSS APPLY  AdventureWorks.dbo.fn_GetTopOrders(C.CustomerID, 3) AS O 

ORDER BY   CustomerID ASC, TotalDue DESC

which results in this...

CustomerID  SalesOrderID TotalDue

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

1           45283        37643.1378

1           46042        34722.9906 

1           44501        26128.8674 

2           46976        10184.0774

2           47997        5469.5941

2           57044        4537.8484 

3           53616        92196.9738 

3           47439        78578.9054

3           48378        56574.3871

4           47658        132199.8023 . . .

The APPLY clause acts like a JOIN without the ON clause comes in two flavors: CROSS and OUTER. The OUTER APPLY clause returns all the rows on the left side (Customers) whether they return any rows in the table-valued-function or not. The columns that the table-valued-function returns are null if no rows are returned. The CROSS APPLY only returns rows from the left side (Customers) if the table-valued-function returns rows.
Notice that I'm just passing in the CustomerID to the function. It returns the TOP 3 rows based on the amount of the order. Since I'm using CROSS APPLY a customer without orders won't appear in the list. I can also pass in a number other than 3 to easily return a different number of orders per customer. Even better I can pass in a different number of orders for each customer. So I could list the top 5 orders for one type of customer but the top 10 for another type of customer. How cool is that?!?
And it gets even better. Remember the function has an ORDER BY in it. It will always return the top orders based on the amount. However you can change the ORDER BY clause in the query that calls the function to display those rows in whatever order you want. You could easily display the top 3 orders in ascending order instead.

Performance and Conclusion

Unfortunately I wasn't able to compare the performance of my solution to MSDN's. The query plan from my solution doesn't look that bad. (And did I mention that it showed me a full query plan for the query with the UDF rather than just a UDF step? Sweet!) If anyone gets their solution running send me the code and I'll update the article.
I also think their solution returns the result as a comma separated value. Now that would be a great use for the CLR. You could easily wrap that around the results of this function for a fast, effecient procedure. And it happens to be the next article I'm working on. :) If you want to see a great article on using the CLR inside SQL Server I'd start with Using CLR Integration in SQL Server 2005. The code is from an earlier beta but it gives you a great idea of when to use CLR code.
In summary I think that 5-10 lines of SQL is much simpler than 50+ lines of C# (or VB) for this type of solution. The article provides a lot of good information on the CLR inside SQL Server. I just don't think this is the best place to use it. One of my biggest fears is that people will use the CLR incorrectly and cause performance problems. This is the first real case I've seen of this. Is this really how Microsoft wants us to use the CLR?

UPDATE

I was curious when I posted this how long it would take to find an even better solution. It didn't take long. Arnold Fribble posted it in the forums. The syntax needed a little tweaking but it appears you can do this in a single query. And how did I forget the PARTITION BY clause of the ROW_NUMBER function. Doh! Here's the cleaned up query:

SELECT   CustomerID,  SalesOrderID,  TotalDue 

FROM (  SELECT   O.CustomerID,   O.SalesOrderID,    O.TotalDue, 

ROW_NUMBER = ROW_NUMBER() OVER (    PARTITION BY O.CustomerID 

ORDER BY O.TotalDue DESC)  FROM    Sales.SalesOrderHeader AS O  ) AS d 

WHERE  d.ROW_NUMBER <= 3

You have to use a derived table (or a Common Table Expression) since the row number function can't be used in the WHERE clause of the query where it's defined. Now I'll have to write an article on the new ranking functions. :) Thanks Arnold!

Handling Multiple Errors in SSIS

One actual failure in SSIS can trigger a whole series of error messages. For example, failure to convert a column value from a string to an integer in a Derived Column transform generates the following messages:
[Data Conversion [70]] Error: Data conversion failed while converting column “Fiscal year” (18) to column “NumericFiscalYear” (83). The conversion returned status value 2 and status text “The value could not be converted because of a potential loss of data.”.

[Data Conversion [70]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “output column “NumericFiscalYear”
(83)” failed because error code 0xC020907F occurred, and the error row 
disposition on “output column “NumericFiscalYear” (83)” specifies failure on error. An error occurred on the specified object of the specified component.
There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “Data Conversion” (70) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread “WorkThread0″
has exited with error code 0xC0209029. There may be error messages posted 
before this with more information on why the thread has exited.
If you are logging errors to a flat file or an error logging table, then recording each error may be fine. However, if you’re writing the errors to the Windows event log, or sending them via email, you may not want to record multiple messages each time an error occurs. You might want to record only the first message, or you might want to group all the errors into a single log entry or email. Fortunately, the event model in SSIS allows you to easily customize how errors are handled.
I’ve put together a small sample package that shows how you might accomplish this. The package contains a single data flow that loads a text file, attempts to convert a column from string to numeric, and writes it to a Trash destination (see www.SQLIS.com to get this component).
The text file has an invalid value in one of the columns, which will cause the data flow to fail, and generate the four messages listed above. The package is set up to capture all of the error messages generated, store them in a collection, and concatenate them into a single string when the package is finished executing. Once that is done, the resulting string could be emailed or recorded to a log.
As mentioned, the data flow is very straightforward:
�
I’ve also created two variables at the package level: errorMessages as an Object, and emailText as a String. I’ll explain why later in the post.
The real work occurs in the event handlers. SSIS raises events for all executables(packages and tasks are both executables). The event we’re interested in is the OnError event, which is raised once for each error that occurs.
You get to the event handlers by selecting the Event Handlers tab in the SSIS designer. Once there, the Executable for which you want to capture events needs to be selected.

Since I want to handle errors for anything in the package, I’m setting the executable to CaptureErrors (the name of the package). By default, any event raised by a child executable (that is, an executable that is nested inside another executable) will also be raised in its parent. You can disable that behavior by setting the Propagate system variable, but that’s a topic for another post. I’m also using “OnError” from the list of events and have added a Script Task to the event handler.

The Script Task has two variables passed in: the System::ErrorDescription, which contains the text of the error message, and User:errorMessages, which I’ll use to keep track of all the error messages.

Here’s the script used in the Script Task:
Dim messages As Collections.ArrayList
Try
messages = CType(Dts.Variables(“errorMessages”).Value, Collections.ArrayList)
Catch ex As Exception
messages = New Collections.ArrayList()
End Try
messages.Add(Dts.Variables(“ErrorDescription”).Value.ToString())
Dts.Variables(“errorMessages”).Value = messages
Dts.TaskResult = Dts.Results.Success
I’m first attempting to retrieve the ArrayList from the errorMessages variable. If the value of the variable can’t be cast to an ArrayList, it indicates that it hasn’t been initialized yet. Once that has been handled, the error description is added to the ArrayList. This handles capturing the list of all error messages.
The next step is to process all the messages in order to email or log them. Since I only want to do this once, I’m using the OnPostExecute event, which fires when the executable is finished running.

There is another Script Task present in this event handler. This one has the User::errorMessages and User:emailText variables passed in.

The script in this task is concatenating a long string based on the error messages captured and returning it in the emailText variable:
Dim errorDesc As String
Dim messages As Collections.ArrayList
Try
messages = CType(Dts.Variables(“errorMessages”).Value, Collections.ArrayList)
Catch ex As Exception
‘If there is an exception – the object was never initialized, so there 
were no errors
Return
End Try
For Each errorDesc In messages
Dts.Variables(“emailText”).Value = Dts.Variables(“emailText”).Value.ToString
+ errorDesc + vbCrLf
Next
Dts.TaskResult = Dts.Results.Success
Once that has been done, the resulting string could be emailed or logged as desired. Since SSIS can generate fairly verbose error messages, I chose to store the messages in an ArrayList object. I then use the ArrayList to build the actual string message.

Problems with the SCD

The SCD has issues with ease of use and performance. I’ll start with the most important one: Performance. How the SCD works is for each incoming row of data it issues a SQL command to check against the reference (or Dimension) table in the database to compare the incoming row against its corresponding row in the reference (you can watch this happening in SQL profile). This isn’t a problem for small reference tables, but once you start processing thousands of incoming rows against tables with thousands of reference rows, performance starts to drag, because it is doing these row by row checks. The only performance tuning option you have at your disposal is to index the Business Key in the reference table. It would be much better if it was possible to cache the reference table in memory so look-ups could be done in memory instead of row by row against the database – according to this Connect article it may be on the list for the next release.
In terms of ease of use, there’s a couple of annoying things that can trip you up with this component. First of all, every time you complete the wizard, it creates new output transformations, deleting the old ones. If you have customized these in anyway – e.g. adding an update date column - it gets annoying fast. Fortunately the workaround is easy – create your own transformations to receive the outputs independently of the SCD, and when the wizard completes, just delete the outputs it creates and re-map the output data flows to your own transformations.
Secondly, the Wizard is actually disconnected from what is stored in the package for the data flow. The wizard’s data is stored in a separate chunk of XML within the package definition. What this means in practice is if you use the Advanced Editor to make any changes, these will not be picked up by the wizard if you run it again. So its quite easy to make tweaks that get lost if you re-use the wizard.

SCD Type 2

Configuring the SCD for a Type 2 Dimension

The first work you need to do for a Type 2 dimension actually resides in your dimension table design – you need to decide whether you are going to track changes in your table using either a simple indicator to identify current and expired records, or if you want to use effective dates – the component doesn’t natively allow you to use both, though you can customize the output to do so. The Current / Expired indicator actually uses a small text string which can either be set to the string value pairs ”True” / ”False” or “Current” / “Expired” – no customization of these is allowed in the component (again, you can customize the output to change this, but the wizard will only allow mapping of the column to one that will accept text strings). The Effective dates option requires a start and end date date time column, and in the wizard you use a variable to set the time used. The sample package demonstrates a few possibilities but below I will describe using effective dates.

Fig 2: Select a Dimension Table and Keys

First of all, note when on the first page, Select a Dimension Table and Keys the Effective dates (and Current indicator) are not mapped. Because I have named the columns in line with what the SCD expects for such indicators, it ignores them completely in the mapping – they cannot even be selected as Input Columns. If you name them differently in your design, simply map them as “Not a Key Column”.

Fig 3: Slowly Changing Dimension Columns

In the Slowly Changing Dimension Columns page set the change type of each column to “Historic” so the component will track history of changes.

Fig 4: Historical Attribute Options

The wizard will present a page that is only displayed when you have selected Historic change type columns. Here the start and end date columns are specified, and the component needs a datetime variable to use to set the expiry of old records and the start date of new records. Here I have just used the Package Start time variable – in practice you may well want to specify a variable populated with something else, such as the extract date of the data.

Fig 5: Finishing the SCD Wizard

When go to finish the wizard, you will note the additional “Historical Attribute Output” will be generated. In practice this means a set-of components will be output to manage the changes, which are illustrated below (click to zoom in). The derived columns add the effective start and end date columns and the OLE DB Command expires old records. Please review the sample component to see how this works in practice.

Fig 6: Historical Change Outputs

SCD Considerations for Type 2 Dimensions

One of the most important things to bear in mind is that the component is not intelligent in terms of knowing which data is new – so if you had two records for a given key in the sample file, you would have to sort it so it would feed it the most recent item last so that item would be the current one. It also provides no support for data which has its own change dates – for example if a record had an update date and you wished to use that to form the effective date.
The SCD component is only really suitable for tracking Type 2 changes in sources where there will be one record per key per extract and the source itself has no change tracking capabilities. Given this weakness and the difficulties with using this component generally (in terms of configuration and performance) – you may well want to look at the alternatives I mentioned in my original post about the SCD. This is a component that definitely needs an overhaul for the next release.

DataBase Management And Business Intelligence

Advanced Store Procedure using Dynamic SQL in Store procedures


For the Basic Store procedure , the reference link is

http://www.sql-server-performance.com/2003/stored-procedures-basics/

For the Advance Store Procedure , the reference link is

http://programming4.us/database/3660.aspx

The above link have , links for the other type of store procedure structures and method of use, such as

Remote Store procedure :

Remote procedure call
EXEC server_name.db_name.owner_name.proc_name