After getting a lot of posts regarding me not posting anything lately, I will now make it public. I am really busy at the moment - so for now I am not able to do anything to the blog.
I hope I'll get some time blogging about SSAS and PerformancePoint in the near future.
Sorry to let you down...
Thursday, November 20, 2008
Wednesday, October 17, 2007
DateTime-picker problem in Reporting Services 2005
If you are in a situation where you are using a localized version of Internet Explorer, that is anything else than the English version or your browser has been set up to use another locale than English, then you will run into trouble when using the datetime picker in SSRS.
The datetime picker is shown in your reports when you are having report parameters defined as DateTime..
The fact is that Report Server interprets the date selected in the picker as English, however the picker writes the date as it's supposed to in the local language. You get a rsReportParameterTypeMismatch error.
The fix, which is described by Microsoft here , is to change the page definition of the Report Viewer Page and insert your own culture on that particular page by adding the culture property of the aspx page.
The datetime picker is shown in your reports when you are having report parameters defined as DateTime..
The fact is that Report Server interprets the date selected in the picker as English, however the picker writes the date as it's supposed to in the local language. You get a rsReportParameterTypeMismatch error.
The fix, which is described by Microsoft here , is to change the page definition of the Report Viewer Page and insert your own culture on that particular page by adding the culture property of the aspx page.
Wednesday, September 19, 2007
Custom build localized time dimension
As I am from Denmark I would like the time table to appear in my language, danish, or at least my customers would. That's the main reason for this article. In Analysis Services the only option for the time table generator is to generate it in English, Spanish, French or German, which of course suits most people.
When creating a localised time table, you first have to define a table in your data warehouse. This can be done by using the following script:
CREATE TABLE [dbo].[Time](
[PK_Date] [datetime] NOT NULL,
[Date_Name] [nvarchar](50) NULL,
[Year] [datetime] NULL,
[Year_Name] [nvarchar](50) NULL,
[Quarter] [datetime] NULL,
[Quarter_Name] [nvarchar](50) NULL,
[Month] [datetime] NULL,
[Month_Name] [nvarchar](50) NULL,
[Day_Of_Year] [int] NULL,
[Day_Of_Year_Name] [nvarchar](50) NULL,
[Day_Of_Quarter] [int] NULL,
[Day_Of_Quarter_Name] [nvarchar](50) NULL,
[Day_Of_Month] [int] NULL,
[Day_Of_Month_Name] [nvarchar](50) NULL,
[Month_Of_Year] [int] NULL,
[Month_Of_Year_Name] [nvarchar](50) NULL,
[Month_Of_Quarter] [int] NULL,
[Month_Of_Quarter_Name] [nvarchar](50) NULL,
[Quarter_Of_Year] [int] NULL,
[Quarter_Of_Year_Name] [nvarchar](50) NULL,
CONSTRAINT [PK_Time] PRIMARY KEY CLUSTERED
(
[PK_Date] ASC
)
)
When creating a localised time table, you first have to define a table in your data warehouse. This can be done by using the following script:
CREATE TABLE [dbo].[Time](
[PK_Date] [datetime] NOT NULL,
[Date_Name] [nvarchar](50) NULL,
[Year] [datetime] NULL,
[Year_Name] [nvarchar](50) NULL,
[Quarter] [datetime] NULL,
[Quarter_Name] [nvarchar](50) NULL,
[Month] [datetime] NULL,
[Month_Name] [nvarchar](50) NULL,
[Day_Of_Year] [int] NULL,
[Day_Of_Year_Name] [nvarchar](50) NULL,
[Day_Of_Quarter] [int] NULL,
[Day_Of_Quarter_Name] [nvarchar](50) NULL,
[Day_Of_Month] [int] NULL,
[Day_Of_Month_Name] [nvarchar](50) NULL,
[Month_Of_Year] [int] NULL,
[Month_Of_Year_Name] [nvarchar](50) NULL,
[Month_Of_Quarter] [int] NULL,
[Month_Of_Quarter_Name] [nvarchar](50) NULL,
[Quarter_Of_Year] [int] NULL,
[Quarter_Of_Year_Name] [nvarchar](50) NULL,
CONSTRAINT [PK_Time] PRIMARY KEY CLUSTERED
(
[PK_Date] ASC
)
)
I know you could be in need of other types of date information like the week, but for now this is what I've got. Feel free to extend it. ;-)
The table consist basically of the date, month, quarter and year.
The next step is to fill in the data.
Another script is needed for this of course...
TRUNCATE TABLE [Time]
DECLARE @startDate datetime
DECLARE @endDate datetime
DECLARE @currDate datetime
SET @startDate = '1990-01-01'
SET @endDate = '2012-01-01'
SET @currDate = @startDate
SET LANGUAGE 'danish'
SET NOCOUNT ON
WHILE @currDate < @endDate
BEGIN
INSERT INTO [Time]
VALUES
(
@currDate,
DATENAME(weekday, @currDate)
+ ', ' + DATENAME(dd, @CurrDate) + '. ' + DATENAME(month, CurrDate) + ' ' + DATENAME(year, @CurrDate),
DATEADD(year, YEAR(@currDate) - 1900, 0),
DATENAME(year, @currDate),
DATEADD(quarter, DatePart(quarter, @currDate) -1,
DateAdd(year, Year(@currDate)- 1900,0)),
DATENAME(quarter, @currDate) + '. kvartal ' + DATENAME(year, @currDate),
DATEADD(month, DatePart(month, @currDate) -1,
DateAdd(year, Year(@currDate)- 1900,0)),
DATENAME(month, @currDate) + ' ' + DATENAME(year, @currDate),
DATEPART(dayofyear, @currDate),
'Dag ' + DATENAME(dayofyear, @currDate),
null, --to be updated
null, --to be updated
DATEPART(day, @currDate),
'Dag ' + DATENAME(day, @currDate),
DATEPART(month, @currDate),
'Måned ' + CAST(DATEPART(month, @currDate) as nvarchar(2)),
null, --to be updated
null, --to be updated
DATEPART(quarter, @currDate),
DATENAME(quarter, @currDate) + '. kvartal ')
SET @currDate = DATEADD(day, 1, @currDate)
END
UPDATE [Time] SET Day_Of_Quarter = DATEDIFF(day, Quarter, PK_Date)
UPDATE [Time] SET Day_Of_Quarter_Name = 'Dag ' +
CAST(Day_Of_Quarter AS nvarchar(2))
UPDATE [Time] SET Month_Of_Quarter = DATEDIFF(month,
Quarter, PK_Date) + 1
UPDATE [Time] SET Month_Of_Quarter_Name = 'Måned ' +
CAST(Month_Of_Quarter AS nvarchar(2))
UPDATE [Time] SET Day_Of_Quarter_Name = 'Dag ' +
CAST(Day_Of_Quarter AS nvarchar(2))
UPDATE [Time] SET Month_Of_Quarter = DATEDIFF(month,
Quarter, PK_Date) + 1
UPDATE [Time] SET Month_Of_Quarter_Name = 'Måned ' +
CAST(Month_Of_Quarter AS nvarchar(2))
Now the above script includes some Danish words, so of course you will now get a little lesson in Danish, although it should be almost self explaining.
"Dag" equals Day
"Måned" equals Month
"Kvartal" equals Quarter.
"År" equals Year
"Skål" equals Cheers… J
It's important to change the line:
SET LANGUAGE 'danish'
Here you switch danish with your preferred locale..
After running the script you have a time table populated with localized data. Next up is setting up the time dimension in Analysis Services using the just created time table.
First you add the time table to the data source view of your cube.
Next step is creating a new dimension using the wizard, right click the dimensions folder in the solution explorer and select "New Dimension...". The wizard then launches.
First click next at the first informative step. At the next screen shown below just click next:
"Dag" equals Day
"Måned" equals Month
"Kvartal" equals Quarter.
"År" equals Year
"Skål" equals Cheers… J
It's important to change the line:
SET LANGUAGE 'danish'
Here you switch danish with your preferred locale..
After running the script you have a time table populated with localized data. Next up is setting up the time dimension in Analysis Services using the just created time table.
First you add the time table to the data source view of your cube.
Next step is creating a new dimension using the wizard, right click the dimensions folder in the solution explorer and select "New Dimension...". The wizard then launches.
First click next at the first informative step. At the next screen shown below just click next:
The next step is to select the data source view in which you just added the time table. Then click next.
Next up you select the dimension type, which have to be Time. The screen should look like this:
Click next. You are now to define the different columns from the time table, which defines different periods of the time dimension. In the example below I've mapped Year, Quarter, Month and PK_Date to their respective fields in the time dimension. You can set up additional fields like Day of year and so on, if you need them.
Click next. The wizard then analyses the structure of the data and propose hierarchies. The proposed one is good for now. It looks like this:
Click finish.
The wizard then generates your new time dimension. It should look something like this:
To finish our work, we have to do some refinements of the dimension. First we have to show the month name instead of the number. This is done by clicking the Month-attribute in the attributes list at the left and then showing the properties for this attribute. You could do this by hitting F4. Now scroll down the bottom of the list of properties and change the NameColumn property from Month to the column Month_Name. It should look like this: 
The same must be done with the quarter attribute, too.
If you look at the hierarchy its marked with the warning sign and an error message saying that attribute relationships do not exist between one or more of the levels. This can be fixed at the attribute list at the left.
The attribute relationships can be seen by expanding the nodes with the plus sign. Start by deleting the year attribute relationship at the pk_date attribute. Then you add a attribute relationship to year at the quarter attribute by dragging year towards the <new attribute relationship> underneath the quarter attribute. The same must be done with quarter at the month attribute. It should in the end look like this: 
After this the warning disappears.
The last step is to localize the names of the attributes and the level names into your local language, in my case into Danish.
Thursday, August 16, 2007
Exploring Katmai part 4 (July CTP)
A new community technology preview of SQL Server 2008 was released earlier this month. I've been playing around with it for some time now and I am now ready to publish my discoveries..
In this CTP the most interesting newly added features are those of the Enterprise Reporting Engine - next version of reporting services.
Enterprise Reporting
First thing to notice here is, that the new reporting capabilities haven't yet made it into the Business Intelligence Development Studio. If you go that way you are still stuck with SSRS 2005.
As the new version of course consists of new features a whole new xml schema definition has been developed, which the underlying rdl-files must conform to. The schema can be found here: http://schemas.microsoft.com/sqlserver/reporting/2007/01/reportdefinition
The documentation states that it should be possible to upgrade from reports developed in SSRS 2005, however I have not been able to do that.
A new report designer
The report designer has been updated, but still looks a lot like the prior ones. On the surface it looks like this:
My first impression is, that it feels easier to set up things. Not so much fiddeling around with textboxes seems to be needed to get a nice result.
Wizards seem like the way the product is evolving. Almost every action bring up a wizard now. The Data-tab (known from prior versions) has been replaced by a wizard to write statements to retrieve data and set up connections to data sources.
Another place in which the designer seems improved is when adding fields from the data source to the data area of a table. Now you just click inside the field of the table in the designer and a list of available fields shows up.
The Tablix Control
A new control has arrived! The Tablix Control.
It's a combination of the table and the matrix controls. In the control toolbox the two controls still exists, but behind the scenes (that is in the rdl), both controls results in a tablix control.
The tablix control (again) seem easy to use and is configured by using wizards.
It's possible to set up groups, which contains other groups or have adjacent groups. Groups can be both row-based and column-based.
Improved chart control
The chart control has been updated a bit, too.. Again wizards are the way to configure this control, however, I don't remember how it's done in SSRS 2005 at the moment..
The formatting wizard looks like this:
Right now the only available chart type is the bar chart, however the documentation states that it should be possible to set all chart types as known from eg. Excel.
IIS no longer required
Reporting Services no longer needs Internet Information Services. The Report Manager now utilizes the http.sys module, which is incorporated into the operating system. In this way RS is able to manage authentication and authorization without going through IIS.
Improved engine
The engine for processing and delivering reports has been redesigned and should be faster and better, but that has been pretty hard to test.
The capabilities of exporting reports to different formats still offer the exact same formats as the prior versions. I find it a bit odd that an Office Word 2007 export feature is not present. MS has already bought the company SoftArtisans and thereby acquired the OfficeWriter, but until now it hasn't made it into the product. I think it is one of the biggest drawbacks of SSRS, so hopefully it will make it into the final release..
Other additions..
A couple of other features have made it into this community preview, however they don't really impress me that much. Mostly because it's features which I don't use very much.
Analysis Services
In Analysis Services a new algorithm for the Microsoft Time Series data mining feature, which is used to predict future values based on historical data. The algorithm should be more accurate and stable. I haven't tested this new feature, though. We are not having much clients requesting data mining features yet, but I think the future will bring more of these requirements...
Mirroring
At the database engine level some new features of mirroring have arrived with the new release. Not tested, though.
New DateTime data types
New DateTime data types are present in the new CTP (maybe in the last one, too), amongst others DateTime2, Time and Date. Time and DateTime2 has a higher precision than the well-known DateTime data type, which can be great in scenarios where you will be exchanging data with other systems like DB2 or Oracle.
As another point the DateTime2 has the ability to hold dates ranging from year 0, like in the .NET languages. So forget about 1753/01/01
In this CTP the most interesting newly added features are those of the Enterprise Reporting Engine - next version of reporting services.
Enterprise Reporting
First thing to notice here is, that the new reporting capabilities haven't yet made it into the Business Intelligence Development Studio. If you go that way you are still stuck with SSRS 2005.
As the new version of course consists of new features a whole new xml schema definition has been developed, which the underlying rdl-files must conform to. The schema can be found here: http://schemas.microsoft.com/sqlserver/reporting/2007/01/reportdefinition
The documentation states that it should be possible to upgrade from reports developed in SSRS 2005, however I have not been able to do that.
A new report designer
The report designer has been updated, but still looks a lot like the prior ones. On the surface it looks like this:
My first impression is, that it feels easier to set up things. Not so much fiddeling around with textboxes seems to be needed to get a nice result.
Wizards seem like the way the product is evolving. Almost every action bring up a wizard now. The Data-tab (known from prior versions) has been replaced by a wizard to write statements to retrieve data and set up connections to data sources.
Another place in which the designer seems improved is when adding fields from the data source to the data area of a table. Now you just click inside the field of the table in the designer and a list of available fields shows up.
The Tablix Control
A new control has arrived! The Tablix Control.
It's a combination of the table and the matrix controls. In the control toolbox the two controls still exists, but behind the scenes (that is in the rdl), both controls results in a tablix control.
The tablix control (again) seem easy to use and is configured by using wizards.
It's possible to set up groups, which contains other groups or have adjacent groups. Groups can be both row-based and column-based.
Improved chart control
The chart control has been updated a bit, too.. Again wizards are the way to configure this control, however, I don't remember how it's done in SSRS 2005 at the moment..
The formatting wizard looks like this:
Right now the only available chart type is the bar chart, however the documentation states that it should be possible to set all chart types as known from eg. Excel.
IIS no longer required
Reporting Services no longer needs Internet Information Services. The Report Manager now utilizes the http.sys module, which is incorporated into the operating system. In this way RS is able to manage authentication and authorization without going through IIS.
Improved engine
The engine for processing and delivering reports has been redesigned and should be faster and better, but that has been pretty hard to test.
The capabilities of exporting reports to different formats still offer the exact same formats as the prior versions. I find it a bit odd that an Office Word 2007 export feature is not present. MS has already bought the company SoftArtisans and thereby acquired the OfficeWriter, but until now it hasn't made it into the product. I think it is one of the biggest drawbacks of SSRS, so hopefully it will make it into the final release..
Other additions..
A couple of other features have made it into this community preview, however they don't really impress me that much. Mostly because it's features which I don't use very much.
Analysis Services
In Analysis Services a new algorithm for the Microsoft Time Series data mining feature, which is used to predict future values based on historical data. The algorithm should be more accurate and stable. I haven't tested this new feature, though. We are not having much clients requesting data mining features yet, but I think the future will bring more of these requirements...
Mirroring
At the database engine level some new features of mirroring have arrived with the new release. Not tested, though.
New DateTime data types
New DateTime data types are present in the new CTP (maybe in the last one, too), amongst others DateTime2, Time and Date. Time and DateTime2 has a higher precision than the well-known DateTime data type, which can be great in scenarios where you will be exchanging data with other systems like DB2 or Oracle.
As another point the DateTime2 has the ability to hold dates ranging from year 0, like in the .NET languages. So forget about 1753/01/01
Saturday, June 30, 2007
Deploying Embedded Code in Reporting Services
Yesterday I was setting up some reports in SQL Server 2005 at a client. I was to make a tabular report based on an MDX-statement. To get all the information intended I had to make lookups in the database to e.g. get the name of the customer and things like that.
The solution was to embed some code in the report, which made a query to the sql server database containing a Data Warehouse and then returning the result to the actual row.
You can embed code in your reports by viewing the properties of the report (right click the report surface and select properties). The resulting window contains a tab called Code.
If I then had to use my function DoCracyStuff(), I would do this by using an expession in the report looking like this: =Code.DoCracyStuff("param1")
In my scenario yesterday I had to use a sql connection and issuing a query towards the sql server. That was easy done using ADO.NET and System.Data.SqlClient.SqlConnection and SqlCommand. The only thing to do was adding a reference to System.Data at the References-tab.
Everything worked as expected. I got the details that I wanted. At least when using the preview function of the report designer.
I continued doing some more reports and when the day had come to its end and I was almost headed for the weekend, I thought it was time to deploy the reports to the reporting server. Drama! At every place in which I used my embedded code the resulting text was #ERROR. Too bad!
Googling a bit gave me more a less the answer.. Reporting Services is using the Code Access Security features of the .NET runtime, which retrospectively makes perfectly good sense. Reporting Services gives the user the opportunity to deploy his or her own reports, which gives everyone the opportunity to write code in the custom code tab. This could lead to a serious vulnerability. However by using Code Access Security Reporting Services managed to only allow the most simple operations.
Returning to my scenario, this is really a pain in the ass... I had to change the Code Access Security policy - the reports are located internally at the company and everyone has access to the database server, so giving them the opportunity to execute ADO.NET statements against the SQL Server won't cause any trouble.
So how do you change RS to allow queries to SQL Server?
You have to change the policy file of the reporting server, which you find here (by default installation):
%ProgramFiles%\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer
the file to edit is: rssrvpolicy.config.
In this config you have to make a new Named Permission Set. So at the Named Permission Set Node insert the following:
The solution was to embed some code in the report, which made a query to the sql server database containing a Data Warehouse and then returning the result to the actual row.
You can embed code in your reports by viewing the properties of the report (right click the report surface and select properties). The resulting window contains a tab called Code.
If I then had to use my function DoCracyStuff(), I would do this by using an expession in the report looking like this: =Code.DoCracyStuff("param1")
In my scenario yesterday I had to use a sql connection and issuing a query towards the sql server. That was easy done using ADO.NET and System.Data.SqlClient.SqlConnection and SqlCommand. The only thing to do was adding a reference to System.Data at the References-tab.
Everything worked as expected. I got the details that I wanted. At least when using the preview function of the report designer.
I continued doing some more reports and when the day had come to its end and I was almost headed for the weekend, I thought it was time to deploy the reports to the reporting server. Drama! At every place in which I used my embedded code the resulting text was #ERROR. Too bad!
Googling a bit gave me more a less the answer.. Reporting Services is using the Code Access Security features of the .NET runtime, which retrospectively makes perfectly good sense. Reporting Services gives the user the opportunity to deploy his or her own reports, which gives everyone the opportunity to write code in the custom code tab. This could lead to a serious vulnerability. However by using Code Access Security Reporting Services managed to only allow the most simple operations.
Returning to my scenario, this is really a pain in the ass... I had to change the Code Access Security policy - the reports are located internally at the company and everyone has access to the database server, so giving them the opportunity to execute ADO.NET statements against the SQL Server won't cause any trouble.
So how do you change RS to allow queries to SQL Server?
You have to change the policy file of the reporting server, which you find here (by default installation):
%ProgramFiles%\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer
the file to edit is: rssrvpolicy.config.
In this config you have to make a new Named Permission Set. So at the Named Permission Set Node insert the following:
<NamedPermissionSets>
....
<PermissionSet class="NamedPermissionSet" version="1" Name="RSDataAccess" >
<IPermission class="SecurityPermission" version="1" Flags="Execution" />
<IPermission class="SqlClientPermission" version="1" Unrestricted="true" />
</PermissionSet>
....
</NamedPermissionSets>
The new permission set includes the SqlClientPermission, which allow Reporting Services to run ADO.NET code.
The unrestricted="true" attribute is for allowing all calls, but you could refine this to lock down the user a bit more. Look at the MSDN docs here: http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlclientpermissionattribute_properties.aspx
So now you have a permission set. Next step is to apply this to the policy for embedded code.
This is the first code group below the xml statements above.
It should look like this:
<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="RSDataAccess" Name="Report_Expressions_Default_Permissions" Description="This code group grants default permissions for code in report expressions and Code element. ">
The task is to change the PermissionSetName from Execution to RSDataAccess.
After this your reports will be able to make calls to ado.net.
Someone will sure note that I could do the same by using custom assemblies, and only grant the necessary code access security rights for this assembly only, but I find this solution easier to manage right away, when the only permissions needed are those for SQL Server.
Sunday, June 17, 2007
Exploring Katmai part 3 (Database Engine)
Some new features of the relational database engine has come through to the CTP. Some of them looks very exciting. I actually think that some of them will make my life even better.
Merge (wuhuu)
Merge is a new statement like insert, update and delete. It works as a combination of the three, that is you will by using merge be able to merge two tables issuing updates when columns in the source table have changed, inserts when new rows have arrived at the source and deletes when rows have been deleted at the source.
As an example of this look at the following transact-sql statement:
MERGE Production.Product AS prod1
USING (SELECT [ProductID], [Name], ProductNumber, Color, StandardCost FROM Production.Product2) AS src
([ProductID],[Name], ProductNumber, Color, StandardCost)
ON (prod1.ProductID = src.ProductID)
WHEN MATCHED AND
(prod1.[Name] <> src.[Name] OR
prod1.[ProductNumber] <> src.[ProductNumber] OR
prod1.[Color] <> src.[Color] OR
prod1.[StandardCost] <> src.[StandardCost]) THEN
UPDATE SET prod1.Name = src.Name,
prod1.ProductNumber = src.ProductNumber,
prod1.Color = src.Color,
prod1.StandardCost = src.StandardCost
WHEN SOURCE NOT MATCHED THEN
DELETE;
The statement looks for updates to the table Products2. When the table has changes to Name, ProductNumber, Color or Standard Cost then an update statement is issued.
When rows in destination is not at source then this statement will delete the rows.
When rows in source is not at the destination you will be able to issue Insert statements (not in the example, though) by using WHEN NOT MATCHED THEN.
This really is a nice addition to t-sql. I have done a bunch of this type of code for the last couple of years. Now I really look forward for the new version of SQL Server. :-)
Table-valued parameters
This is a functionality which lots of sql-developers have been looking forward to.
In stored procedures it hasn't until now been easy to transfer table-valued data, for instance a list of words for doing search.
Some solved this by using Xml or by comma-separating values in a varchar parameter and then parsing the xml or comma-separated list in the stored procedure, while others did pass the values into a global temporary table and retrieved the values from there.
Some did just pass a lot of parameters, that is @search1, @search2, @search3 and so on, which had the ability to contain null values and then you would check if the value was null for each of the parameters. This approach also has the problem with the fixed count of parameters.
These approches are no longer necessary as a new parameter type is introduced namely the table-valued parameter type, where you define the structure and then have the ability to pass data in that structure to your procedures.
The table type is defined in this way:
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50), CostRate INT );
GO
And can then be used in the stored procedure or UDF's like this:
CREATE PROC TestProc
@tableValuedParam AS LocationTableType
AS
...
Administrative policies
Merge (wuhuu)
Merge is a new statement like insert, update and delete. It works as a combination of the three, that is you will by using merge be able to merge two tables issuing updates when columns in the source table have changed, inserts when new rows have arrived at the source and deletes when rows have been deleted at the source.
As an example of this look at the following transact-sql statement:
MERGE Production.Product AS prod1
USING (SELECT [ProductID], [Name], ProductNumber, Color, StandardCost FROM Production.Product2) AS src
([ProductID],[Name], ProductNumber, Color, StandardCost)
ON (prod1.ProductID = src.ProductID)
WHEN MATCHED AND
(prod1.[Name] <> src.[Name] OR
prod1.[ProductNumber] <> src.[ProductNumber] OR
prod1.[Color] <> src.[Color] OR
prod1.[StandardCost] <> src.[StandardCost]) THEN
UPDATE SET prod1.Name = src.Name,
prod1.ProductNumber = src.ProductNumber,
prod1.Color = src.Color,
prod1.StandardCost = src.StandardCost
WHEN SOURCE NOT MATCHED THEN
DELETE;
The statement looks for updates to the table Products2. When the table has changes to Name, ProductNumber, Color or Standard Cost then an update statement is issued.
When rows in destination is not at source then this statement will delete the rows.
When rows in source is not at the destination you will be able to issue Insert statements (not in the example, though) by using WHEN NOT MATCHED THEN.
This really is a nice addition to t-sql. I have done a bunch of this type of code for the last couple of years. Now I really look forward for the new version of SQL Server. :-)
Table-valued parameters
This is a functionality which lots of sql-developers have been looking forward to.
In stored procedures it hasn't until now been easy to transfer table-valued data, for instance a list of words for doing search.
Some solved this by using Xml or by comma-separating values in a varchar parameter and then parsing the xml or comma-separated list in the stored procedure, while others did pass the values into a global temporary table and retrieved the values from there.
Some did just pass a lot of parameters, that is @search1, @search2, @search3 and so on, which had the ability to contain null values and then you would check if the value was null for each of the parameters. This approach also has the problem with the fixed count of parameters.
These approches are no longer necessary as a new parameter type is introduced namely the table-valued parameter type, where you define the structure and then have the ability to pass data in that structure to your procedures.
The table type is defined in this way:
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50), CostRate INT );
GO
And can then be used in the stored procedure or UDF's like this:
CREATE PROC TestProc
@tableValuedParam AS LocationTableType
AS
...
Administrative policies
At the management side of SQL Server another new feature has been added. That is the ability to add policies at the administrative side. Hmm, that means more power to DB admins. The question is if that's a feature we like..
The policy editor looks like this:
It gives the admin the opportunity to set rules like stored procedure names must begin with "prod_" and table names can't be no longer than 20 chars and so on.
Different database options can be enforced by the admins, too. That's for instance wheter to allow quoted identifiers but also setting default File Location and File Group.
+= operator in t-sql
One of the enhancements I've seen is the += operator. That is the C-construct to add a number to a variable.
The following statement illustrates this feature:
DECLARE @number AS INT
SET @number = 1
SELECT @number --Result: 1
SET @number += 1
SELECT @number --Result: 2
Not a big change, but a change getting the t-sql language closer to the newer generation of programming languages like C#, C++ and Visual Basic.NET
Exploring Katmai part 2 (SSAS)
First up in my explorations is new features of Analysis Services in Katmai..
Attribute Relationships
A new feature when designing dimensions is a new tab "Attribute relationships", which visualizes the relationships between attributes. That is, you will be able to see the hierarchies in a visual manner and how they interact with each other.
The example below is a visualization of the attribute relationships of the product dimension.
Another new feature when talking dimensional key design is, that it is now possible to change the key in the properties window without opening up any windows/wizards..
Backup and restore
Another thing is that backup and restore has undergone some structural changes which should give a performance gain, however I haven't tested this...
So what's the verdict?
So for the matter of Analysis Services nothing much has come through yet. I look forward to seeing other improvements of Analysis Services in future community previews.
Attribute Relationships
A new feature when designing dimensions is a new tab "Attribute relationships", which visualizes the relationships between attributes. That is, you will be able to see the hierarchies in a visual manner and how they interact with each other.
The example below is a visualization of the attribute relationships of the product dimension.
From within the Attribute Relationships it is possible to create new relationships. A new dialog will popup, which looks like this:
New Key Columns Dialog Box
When designing the keys of the dimensions a new dialog box is available - nothing much really. It looks like this
When designing the keys of the dimensions a new dialog box is available - nothing much really. It looks like this
Another new feature when talking dimensional key design is, that it is now possible to change the key in the properties window without opening up any windows/wizards..
Backup and restore
Another thing is that backup and restore has undergone some structural changes which should give a performance gain, however I haven't tested this...
So what's the verdict?
So for the matter of Analysis Services nothing much has come through yet. I look forward to seeing other improvements of Analysis Services in future community previews.
Subscribe to:
Posts (Atom)
