SQL Kit

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 .

SQL Bits IV is over.By far this was the largest SQL Server community event that I have spoken at. The power point decks are uploaded and can be donloaded from the website http://www.sqlbits.com/information/PublicSessions.aspx –> right click on the session and Open it in new window. You will find most of the session’s PPT Deck.

Alternatively you can download it from  here

With AS 2008 , you can create subcube in Visual and Non Visual Modes. First of all What is Visual and Non Visual Mode. I will try to write some simple MDX to demonstrate it.

Fire this query without any subcubeing

select {[Measures].[Reseller Sales Amount] } on 0,

[Business Type].members on 1

from [Adventure Works]

where [Category].members

You Get

Alright so this is the All Resellers total Value  ($80,450,596.98)

Now lets fire the next MDX but this time with simple SUB CUBE

CREATE SUBCUBE [Adventure Works] AS

Select {[Category].Accessories, [Category].Clothing} on 0,

{[Business Type].[Value Added Reseller], [Business Type].[Warehouse]} on 1

from [Adventure Works]

with

member Measures.VisualSum As

([Category].Accessories,[Measures].[Reseller Sales Amount])+([Category].Clothing,[Measures].[Reseller Sales Amount])

select {[Measures].[Reseller Sales Amount] ,Measures.VisualSum} on 0,

[Business Type].members on 1

from [Adventure Works]

where [Category].members

You would see that Visual total is eaqul to Reseller total which is much less than the actual total. Why does this happen?

This is because when you try to do a total on SUB CUBE with considers only VISUAL TOTALS , the other values ( of Reseller Sales here) will not be aggregated. Sometimes this is what is required in a business scenarion but many times you will find your customers want to see total figure . In this case the query below will show the SUB CUBE total and the NON VISUAL total . For this we will create a sub cube with NON VISUAL mode.(this is available from AS 2008)

CREATE SUBCUBE [Adventure Works] AS

NON VISUAL (Select {[Category].Accessories, [Category].Clothing} on 0,

{[Business Type].[Value Added Reseller], [Business Type].[Warehouse]} on 1

from [Adventure Works])

select [Category].members on 0,

[Business Type].members on 1

from [Adventure Works]

where [Measures].[Reseller Sales Amount]

with

member Measures.VisualSum As

([Category].Accessories,[Measures].[Reseller Sales Amount])+([Category].Clothing,[Measures].[Reseller Sales Amount])

select {[Measures].[Reseller Sales Amount] ,Measures.VisualSum} on 0,

[Business Type].members on 1

from [Adventure Works]

where [Category].members

this is what you get

See that the NON VISUAL total $80,450,596.98   is without considering the sub cube and this is eaqul to the first total that we had , which was on the complete cube , but the Visual total is same as the Total we had for the SUB CUBE in Visual Mode.

I have tried my best to explain that Visual and Nonb Visual totals are how can Create SUB CUBE be used in both modes. For more  do go to

http://msdn.microsoft.com/en-us/library/ms144916.aspx

http://sqlblog.com/blogs/mosha/archive/2008/11/04/as2008-mdx-subselects-and-create-subcube-in-non-visual-mode.aspx