SQL Kit

SSAS 2008 has improved Dimension and Aggregation Designer, new Attribute Relationship Designer, Optimize performance with block computations mode and dynamic management views for enhanced resource monitoring.SSRS 2008 reports has the unique data format of Tablix which allows writing reports with combined advantage of Table and Matrix formats. It can integrate with Microsoft Office SharePoint Server 2007 for central delivery and management of business insight. It also enables users to quickly gain insight into complex sets of data by displaying data graphically with enhanced visualization capabilities. Performance has improved drastically for situations where you are generating large reports.

SSIS Pipeline is optimized to enable more parallel loading of data. You can write script components in C# now. It comes with improved scalability with thread pooling and enhanced lookup transformations. It also performs more functional and scalable data transfers with the improved SQL Server Import and Export Wizard.

Other engine features that you need to be aware of are, improvements to partitioning, change data capture to enable easy extraction of changed data from a production system, backup and table compression which enables better performance and less storage space utilisation. It also comes with optimized Star-Join, improved lock escalation handling, Merge statements and other advanced T SQL enhancements.

The slides and demos are available here for download.

Have a look at this query

WITH

MEMBER [Measures].[REFUNDS] AS [Measures].[Internet Sales Amount] – [Measures].[Internet Sales Amount] * .4

MEMBER [Measures].[PROFIT] AS [Measures].[Internet Sales Amount] – [Measures].[REFUNDS]

MEMBER [Product].[Product].[Top10] AS

sum(

TOPCOUNT([Product].[Product].[Product].MEMBERS

, 10

, [Measures].[Internet Sales Amount]

)

,

([Measures].CurrentMember)

)

MEMBER [Product].[Product].[Top10PercOfTotal] AS [Product].[Product].[Top10]/[Product].[Product].[All Products]

SELECT

{

[Measures].[Internet Sales Amount],

[Measures].[REFUNDS],

[Measures].[PROFIT]

} ON COLUMNS,

{

[Product].[Product].[Top10]

,[Product].[Product].[Top10PercOfTotal]

,[Product].[Product].[All Products]

}

ON ROWS

FROM [Adventure Works]

This yeilds same result as yours ie. not a flat cell by cell division

I don’t expect it to produce this result . If the computations was going as planned the output should be 0.35272454  (35 %) for each.

I have altered the SOLVE ORDER (this is the property which determines the order of cell computation)

Here is modified query

WITH

MEMBER [Measures].[REFUNDS] AS [Measures].[Internet Sales Amount] - [Measures].[Internet Sales Amount] * .4,SOLVE_ORDER = 1

MEMBER [Measures].[PROFIT] AS   [Measures].[Internet Sales Amount] - [Measures].[REFUNDS],SOLVE_ORDER = 2

MEMBER [Product].[Product].[Top10] AS

 sum(

  TOPCOUNT([Product].[Product].[Product].MEMBERS

  , 10

  , [Measures].[Internet Sales Amount]

  )

  ,

  ([Measures].CurrentMember)

  )

,SOLVE_ORDER = 3

MEMBER [Product].[Product].[Top10PercOfTotal] AS 

[Product].[Product].[Top10]/[Product].[Product].[All Products]

, FORMAT_STRING = "Percent"

 , SOLVE_ORDER = 4

SELECT 

 {

  [Measures].[Internet Sales Amount],

  [Measures].[REFUNDS],

  [Measures].[PROFIT]

  } ON COLUMNS,

 {

[Product].[Product].[Top10]

,[Product].[Product].[Top10PercOfTotal]

,[Product].[Product].[All Products]

  } 

  ON ROWS

FROM [Adventure Works]

·

There you go. SOLVE_ORDER determines the series of how cell computation  will be formed. One with smallest SOLVE_ORDER will be evaluated first.

Hope this explains SOLVE_ORDER and how it works.

The evening’s agenda includes the following presentations (see Events section for more details):

What’s new in SQL Server 2008 for BI

Presented by Ashwani Roy, MCTS MCITP MCAD MCP

We’re also pleased to let you know that SQL Server MVP, Simon Sabin will be presenting. Details about this presentation will be made available shortly.

Location:
Bank Of America 
5 Canada Square
London E14 5AQ
United Kingdom

Register @ http://www.sqlpass.org.uk/

Excel 2007 provides many APIs which are quite powerful from analytics point of view.Not all of them are exposed via the UI though ( for some reason … ). SQL Server MVP Greg Galloway has developed this very cool tool which is EXCEL 2007 add-in and lets you do very cool stuff.

You know that calculated members are evaluated on the fly.But the only way you can have this calculated member available from browsing is by doing this physically in the cube. There is a way that you can do it in the excel itself .These are called Private Calculated Members

Simple ratios or differences and stuff that are very specific to a (or small group) of cube users can be put in here , rather than cluttering the cube.

Limitation: If you run "OLAP Tools… Convert to Formulas" on a PivotTable with private calculated members, the private calculated members will show N/A. There is no known workaround at this point other than having your OLAP administrator define these calculated members in the cube itself.

There is much more to this too and the download and more information is available here http://www.codeplex.com/OlapPivotTableExtend.

Some time back I took a small session for a group of SQL Server users about some cool stuff in SQL Server 2008 from the T SQL point of view. Some of the students wanted to have access to the scripts , so here they are

MERGE STATEMENT and OUTPUT CLAUSE

drop table Source
drop table Destination

go

CREATE TABLE Source (id int,val varchar(56));

CREATE TABLE Destination (id int,val varchar(56));

GO

INSERT Source VALUES (1,’A');
INSERT Source VALUES (2,’B');– This will be inserted

INSERT Destination VALUES (1,’q');–this will be updated to A
INSERT Destination VALUES (6,’w');
INSERT Destination VALUES (8,’r');

GO

MERGE Destination D — target table

        USING Source S — source table

        ON S.ID = D.ID

        WHEN MATCHED  THEN

                UPDATE set D.val = S.val

          WHEN NOT MATCHED THEN

                — insert a row if the stock is newly acquired

                INSERT VALUES (S.ID, S.VAL)

        — output details of INSERT/UPDATE/DELETE operations

        — made on the target table

        OUTPUT $action, inserted.*, deleted.*; —this is not required in Merge statement I have kept it here just to demostrate that from SQL 2005 onwards
                                                       — OUTOUT clause can be used along with INSERTED , DELELED to get the what has been done as a result to DML action
                                                       — Might help in auditing the DML actions
/*output of the OUTPUT CLAUSE IS

$action    id    val        id        val— I can see what is Inserted and what is Deleted
INSERT    2    B        NULL    NULL
UPDATE    1    A        1        q

*/                                                      

SELECT * FROM Destination;–See the result

GO

GROUPING SETS

GROUPING SETS are a new feature of SQL Server 2008. Using them will allow multiple groupings to be returned in one record set.

We will grouping on City and StateProvice in the same query

use AdventureWorksLT
go
—old way —————-
SELECT NULL, City, COUNT(StateProvince) AS Nbr
FROM SalesLT.Address
GROUP BY  City
UNION ALL
SELECT StateProvince, NULL, COUNT(StateProvince) AS Nbr
FROM SalesLT.Address
GROUP BY StateProvince

–useing GROUPING SETS in SQL 2008
SELECT StateProvince, City, COUNT(StateProvince) AS Nbr
FROM SalesLT.Address
GROUP BY
GROUPING SETS
(
  (City),
(StateProvince)
–()
  );

TABLE VALUED PARAMTERS

In this example I will pass a table valued parameter in the stored procedure

–USING Table valued input Parameters
CREATE TYPE myTableType AS TABLE (id INT, name NVARCHAR(100),qty INT);

ALTER PROCEDURE myProc (@tvp myTableType READONLY) AS

declare @table TABLE (id int)

    UPDATE Inventory SET
    qty += s.qty
    FROM Inventory AS i INNER JOIN     @tvp AS tvp
    ON i.id = tvp.id
GO

NEW DATE TIME DATA TYPES

– IN SQL 2008 INSTANCE
declare @datetime datetime,@DATETIME2 DATETIME2
select @datetime = ’1500-03-04 15:43:26.857′–this will give out of range exception asyou cannot have anything before 1900
select @datetime2 = ’1500-03-04 15:43:26.857′– there you can do it
SELECT @DATETIME2

DECLARE @DATE DATE , @TIME TIME
SELECT @DATE =’1900-01-01′,@TIME = ’15:43:26.857′
SELECT @DATE ,@TIME

DECLARE @DATETIMEOFFSET datetimeoffset
SELECT @DATETIMEOFFSET = ’2005-09-08 12:20:19.345 -08:00′    — stored datetime with OFFSET Lets say you want datetime datetime captured in LONDON server transfrom to US and lets assume the offset is 8 hrs
SELECT @DATETIMEOFFSET

————————————————————————————————————————–

Thats all for now!! I though it will be easier to get the scripts from here rather than download from my sky drive. It is not a very extensive list , it just covers the 1 hours session which I took for beginner sql users.

What is CDC :-

Change data capture (CDC) is a set of software design pattern used to determine (and track) the data that has changed so that action can be taken using the changed data. Also, Change data capture (CDC) is an approach to data integration that is based on the identification, capture and delivery of the changes made to enterprise data sources.

It is a very common requirement for a data warehouse load package is to determine what has changed in the source systems and load this data into the warehouse.

All I wanted to give was a script that you can use along with adventure works Database to see what are basic stuff in CDC and how it works. off course you can use it in your Data warehouse SSIS packages.

—————————————————————

USE AdventureWorks
GO
–Enable Change Data Capture on the Database–
EXEC sp_cdc_enable_db
GO

–Enable Change Tracking on a table–
EXEC sp_cdc_enable_table
    @source_schema = ‘HumanResources’ ,@source_name = ‘Employee’,@supports_net_changes = 1,@role_name = null
go
–Simulate a Data Change —
UPDATE HumanResources.Employee
SET   ManagerID = 10 WHERE Title = ‘Production Technician – WC60′
go

select * from HumanResources.Employee where Title = ‘Production Technician – WC60′

–Get the Data —
select sys.fn_cdc_get_min_lsn(‘HumanResources_Employee’),sys.fn_cdc_get_max_lsn() 
go

select *  from cdc.fn_cdc_get_all_changes_HumanResources_Employee
(0×00000036000017400078,0x0000003600001EC1006C,’all’)

declare @minLSN varbinary(max),@maxLsn varbinary(max)

select @minLSN = sys.fn_cdc_get_min_lsn(‘HumanResources_Employee’)
,@maxlsn=sys.fn_cdc_get_max_lsn() 

select *  from cdc.fn_cdc_get_all_changes_HumanResources_Employee
(@minLSN ,@maxLsn ,’all’)

–Disable CDC as I dont want this running on my Laptop–
EXEC sp_cdc_disable_db
GO
/*
    WHEN 1 THEN ‘delete’
    WHEN 2 THEN ‘insert’
    WHEN 4 THEN ‘update’

*/

–exec sp_configure

—————————————————————————————————–

With SQL Server 2005 onwards we are provided with a set of DMVs. These DMVs allow us to monitor facts like which index is being used and which are not , where are IO bottlenecks , what is cache hit ratio and other stuff. But what about analysis service.

What if I want to know which of my hierarchies are being used , which are the aggregations that are not being used etc etc.

Well from SQL 2008 onwards you can have this capability.

AS 2008 has

–>  4 DBSCHEMA (Database Schema DMVs)

1. $SYSTEM.DBSCHEMA_CATALOGS

2. $SYSTEM.DBSCHEMA_COLUMNS

3. $SYSTEM.DBSCHEMA_PROVIDER_TYPES

4. $SYSTEM.DBSCHEMA_TABLES

–> 10 DMSCHEMA DMVs

1. $SYSTEM.DMSCHEMA_MINING_COLUMNS

2. $SYSTEM.DMSCHEMA_MINING_FUNCTIONS

3. $SYSTEM.DMSCHEMA_MINING_MODEL_CONTENT

4. $SYSTEM.DMSCHEMA_MINING_MODEL_CONTENT_PMML

5. $SYSTEM.DMSCHEMA_MINING_MODEL_XML

6. $SYSTEM.DMSCHEMA_MINING_MODELS

7. $SYSTEM.DMSCHEMA_MINING_SERVICE_PARAMETERS

8. $SYSTEM.DMSCHEMA_MINING_SERVICES

9. $SYSTEM.DMSCHEMA_MINING_STRUCTURE_COLUMNS

10. $SYSTEM.DMSCHEMA_MINING_STRUCTURES

These DMVs describe data mining models in the Analysis Services database.

–> There are 13 DMVs which describe the Meta Data of Analysis service data base (cube,partitions,hierarchies etc)

1. $SYSTEM.MDSCHEMA_CUBES

2. $SYSTEM.MDSCHEMA_DIMENSIONS

3. $SYSTEM.MDSCHEMA_FUNCTIONS

4. $SYSTEM.MDSCHEMA_HIERARCHIES

5. $SYSTEM.MDSCHEMA_INPUT_DATASOURCES

6. $SYSTEM.MDSCHEMA_KPIS

7. $SYSTEM.MDSCHEMA_LEVELS

8. $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS

9. $SYSTEM.MDSCHEMA_MEASUREGROUPS

10. $SYSTEM.MDSCHEMA_MEASURES

11. $SYSTEM.MDSCHEMA_MEMBERS

12. $SYSTEM.MDSCHEMA_PROPERTIES

13. $SYSTEM.MDSCHEMA_SETS

These DMVs expose a Whole New gold mine for Analysis and monitoring of SSAS. Have a look at some of the DMX queries below which use these DMVs to expose some very useful information.

1. Open SQL Server Management Studio

2. Open a DMX query Window and connect to Adventure Works Cube .(if you dont have adventure works cube you can download it from codeplex.com)

3. paste this query

SELECT *
FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE CUBE_NAME = ‘Adventure Works’
   AND MEASUREGROUP_NAME = ‘Internet Sales’
ORDER BY [MEASUREGROUP_NAME]

You will see that you have information about all the measures in the Measure Group

here is sample output (Only selected few column due to space constraint)

Now that we know how useful can this DMV be here are some scripts that can help you play around with

select * from $system.dbschema_tables
select * from $system.dbschema_columns
select * from $system.dbschema_catalogs

select * from $system.mdschema_cubes
select * from $system.mdschema_hierarchies

select * from [Adventure Works].[$Product] –database dimension

select * from $system.discover_commands
select * from $system.discover_connections
select * from $system.discover_memoryusage
select * from $system.discover_object_memory_usage
select * from $system.discover_object_activity where object_reads > 0
select * from $system.discover_partition_stat

SQL Server MVP Darren Gosbell has blogged about it and I suggest you look at his blog http://geekswithblogs.net/darrengosbell/Default.aspx 

(even otherwise it is a very good blog)

June 10th BI Event in Reading; SQL 2008 R2 & Gemini; Data Modelling to Info Architecture; Attribute Relationships, Aggregations & using MDX studio to its best

For more information and to register: http://www.sqlserverfaq.com/events/168/SQL-2008-R2-and-Gemini-From-Data-Modelling-to-Information-Architecture-and-Attribute-Relationships-Aggregations-and-using-MDX-Studio-to-its-best.aspx

Join us for another UK SQL Server User Group meeting.
5.30pm – 6:00 Registration and networking + Tea/Coffee with biscuits.
Meet and greet.
6:00pm – 6:15pm Round Table discussion and Nuggets – ALL
Take stock and get the latest news in the SQL Server field. This is also a great opportunity to ask any burning questions you have, may be a problem at work.
It’s also a great opportunity to share your knowledge and gain exposure in the industry by giving a short 1 – 6 minute "nugget" demo/tip.
6.15pm – 7:00pm Andrew Fryer, Microsoft

SQL Server 2008 R2 and Gemini
SQL Server 2008 R2 will be the release with Gemini in it. Gemini is about trying to marry self service BI for end users with proper control of that data for the IT Professional. The beta program has only been recently announced and so it’s still early days but if you want to know what to expect then this is your first real opportunity in the UK. So join Andrew Fryer for an hour of peering into what 2010 BI looks like
7:00pm – 7:45pm Alex Pratt and Andrew de Rozairo Sybase

From Data Modeling to Information Architecture
It used to be about doing the thing right: a tactical approach for data modelling was necessary, because all the data was contained within the same domain. Now it’s about doing the right thing. Management demands information that is strategic and which helps them make business decisions. This information is more complex, housed in multiple domains, created in different languages, and used by different areas of the organisation for opposing purposes. Adding to the complexity is the critical need for it to be timely, accurate and comprehensive. 
In this 45-minute session, Andrew de Rozairo and Alex Pratt of Sybase will impart the secrets of successfully building a foundation for a comprehensive and successful information architecture. You’ll learn how to take a new approach to modelling: combining the business view, information view and the technology view to achieve a strategic vision. 
7:45pm – 8pm BREAK: Light refreshments

More time to network and ask questions…
8pm – 8:15pm Vincent Rainardi
Nugget (demo) on SSAS
8:15pm – 9.00pm Ashwani Roy
Attribute Relationships, Aggregations and using MDX Studio to its best
Setting proper relationships for the attributes of a dimension is essential from a query performance point of view. It is one of the most important things while dimensional modelling.
Aggregations are pre calculated summaries of cube data that help enable Microsoft SQL Server 2005 Analysis Services (SSAS) to provide rapid query responses. Aggregations are like fact table data grouped and aggregated to a higher level in the Hierarchy. Building the correct aggregations can greatly enhance query performance.
MDX Studio can be used to analyze complex MDX expressions, monitor performance characteristics of MDX queries and get insight into how MDX interacts with other UDM features such as attribute relationships. MDX Studio provides unique visualization representation of MDX expression and allows the user to interact with different stages of MDX execution. It is built by Mosha Pasumansky, who is inventor of MDX and one of the Architects of Microsoft Analysis Services.
Speaker Bio’s
Andrew Fryer
Microsoft Evangelist specialising in Business Intelligence.
Ashwani Roy
Ashwani Roy is a Business Intelligence Consultant with IMGROUP with 5+ Years of experience in RDBMS and OLAP. He specialises in Database engine, SSIS and SSAS, C#, Design Patterns.
Ashwani is MCTS & MCITP in SQL Server 2005 and also has 2 patents in Business Intelligence domain.
Ashwani is also frequent blogger on www.sqlkit.com  and http://ashwaniroy.spaces.live.com  and one of the authors of Server 2008 MCP exams 451 (database solutions). He is also regular responder on Microsoft Forums 
Andrew de Rozairo
Andrew de Rozairo has focused on delivering business value through technology for the last 23 years. An Electrical Engineering and Computer Science degree from the Massachusetts Institute of Technology with an MBA from INSEAD, Europe’s leading Business School, provides him with a unique mix of technical and business perspective. Andrew has over 16 years in management roles in the Data Management Industry, including as CEO of a VC-backed encryption start-up, and European Managing Director of an American data monitoring software company. In his current role as Business Development Manager EMEA for Sybase, Andrew works with strategic clients and partners to develop and deliver business value propositions based on leading-edge Sybase modeling, metadata management and analytics technology solutions.

It is quite amazing , how powerful data can be and for someone who works in BI it is even fascinating to see a software that combines the search capabilities to BI.

Today I stumbled accorss this post from Mosha @ http://sqlblog.com/blogs/mosha/archive/2009/05/14/wolframalpha.aspx and went to website http://www.wolframalpha.com/ for a quick test drive.

With the hardware and computational power that is available to us , and the kind of computational ability that Wolfram talks about , it seems like the goal of “make all systematic knowledge immediately computable by anyone” is reachable .