MDX Function, few more

After the post MDX introduction and MDX function, here in the same series I am going to explain few more function which will be very frequent.

PeriodsToDate

Is a time based function; it gives you till period values from the start of the period. A period can be any time based period like Quarter, Week, Month and Year.

Function takes a level and a member as input parameter. Level is the level you want to travel and member is till that member.

SELECT

      {[Measures].[Internet Order Count]} ON COLUMNS,

      PeriodsToDate([Date].[Calendar].[Calendar Year],[Date].[Calendar].[Month].&[2006]&[8]) ON ROWS

FROM      [Adventure Works]   

So here result will give you all the values starting from month 1 of year 2006 to the Month 8 of year 2006.

Interesting, isn’t it??

Well more understating will be when it is user dynamic.

To do so we have to create a calculated value. For crating a calculated value we use WITH MEMBER.

Following is Syntax of 

WITH MEMBER [Measures].[YourMeasureName]

AS

<>

 Than normal SELECT Statement which uses above calculation in any of their AXIS

Hear it how it comes together -

WITH MEMBER [Measures].[Internet Order Count YTD]

AS

aggregate(

PeriodsToDate([Date].[Calendar].[Calendar Year], [Date].[Calendar].currentmember)

,[Measures].[Internet Order Count]

)    

SELECT

      {[Measures].[Internet Order Count],[Measures].[Internet Order Count YTD]} ON COLUMNS,

      [Date].[Calendar].[Month] ON ROWS

FROM [Adventure Works]

WHERE {[Date].[Calendar Year].&[2007]}

Here we have simple used PeriodsToDate With in a calculation.

Here instead of a member we have used currentmember. This current member will help us calculate will you for every rows means for every member of Month attribute.

When you run this query result will give you cumulative of internet Order counts till year end.

Another important Time period based function is

PARALLELPERIOD

Return a data for  a period in the same relative position as a specified member, this period can be before or upcoming based on the input given.

SELECT

    PARALLELPERIOD([Date].[Calendar].[Calendar Year],1, [Date].[Calendar].[Month].&[2010]&[9]) ON COLUMNS

     

FROM [Adventure Works]

We can also have dynamic query here as well, see following

WITH MEMBER [Measures].[Internet Order Count PP]

AS

aggregate(

PARALLELPERIOD([Date].[Calendar].[Calendar Year],1, [Date].[Calendar].currentmember)

,[Measures].[Internet Order Count]

)    

SELECT

      {[Measures].[Internet Order Count],[Measures].[Internet Order Count PP]} ON COLUMNS,

      [Date].[Calendar].[Month] ON ROWS

FROM [Adventure Works]

WHERE {[Date].[Calendar Year].&[2010]}

Hope this will help you understand basic of MDX and also some of the Important MDX functions. 

MDX Functions

Once you know basics we can have look at the basic functions and properties of MDX.

I hope you have read the previous blog MDX  Introduction.  

In the last post I have shown you a very basic MDX, it just show one member, if you want to see all the members than

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,

[Date].[Calendar Year].ALLMEMBERS on 1

FROM [Adventure Works]

Using . ALLMEMBERS to a dimension attribute will give you all the members of that attributes.

Just like this we have some more properties and function

PREVMEMBER

 Gives you previous member of a Dimension Member.  

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,

[Date].[Calendar Year].&[2009].PREVMEMBER on 1

FROM [Adventure Works]

So you will get data across Year 2008.

RANGE

Range is not a function but it’s a way of writing MDX, by which you can see data for a range of dimension member.  

SELECT {[Measures].[Internet Order Count]} on 0,

{[Date].[Calendar Year].&[2005]:[Date].[Calendar Year].&[2007]} on 1

FROM [Adventure Works]

FILTER

If you want to filter data on Axis based on a condtion you can use filter function.  Like following

SELECT [Measures].[Internet Sales Amount] ON 0,

FILTER

(

[Date].[Date].[Date].MEMBERS

, [Measures].[Internet Sales Amount]<10000 o:p="o:p">

) ON 1

FROM [Adventure Works]

FILTER takes a set and a condition as input parameter.

In above example we are showing all the dates for which we have Internet sales amount more than 10000.

Instead of a measure you can also have Dimensions as Filter condition.

EXCEPT

Same as FILTER there is Except

--Except

SELECT [Measures].[Order Quantity]

   ON COLUMNS,

   Except

      ([Product].[Product Categories].[All].Children ,

         {[Product].[Product Categories].[Components]}

      ) ON ROWS

FROM

   [Adventure Works]

It gives you result except the condition, so its other way around of Filter.

So it takes a set and a condition as input parameter, but here result will exclude the condition and gives you result.

This are few function I have explained you above, in next post will see more of those. 

MDX Introduction

MDX: Well you must have heard of it. So like you need T SQL or SQL to communicate with a RDBS, same way you need MDX to communicate with Multi-Dimensional OLAP (MOLAP).

MDX is Multi-Dimensional Expression. .  It is part of the OLEDB for OLAP specification which was developed by Microsoft but is now supported by multiple vendors.

Main purpose of MDX is to create query statements for data retrieval. We can also perform some of the management functions, such as the UPDATE CUBE statement etc.

It is good if you have knowledge of SQL as that will help in learning MDX.

MDX seems to be tough, but believe me once you have an understanding of it, it will not be complex and you will enjoy writing it.

Let see how MDX statement looks like –

SELECT, FROM and WHERE clause you have seen in SQL as well, so they are self-explanatory.

 SELECT is to select , FROM is to from where do you want to select. In MDX it will a cube or a sub query. And WHERE to filter your selection that we call it SLICING.

In addition to it, here you define Axis. Well its Multi-Dimensional, isn’t it??  So need different dimensional Axis here.

Let see an example here -

 I consider you have basic knowledge of Facts and Dimensions and you have Adventure Works DB & Cube installed.

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,

[Date].[Calendar Year].&[2009] on 1

FROM [Adventure Works]

Well its very simple SELECT statement, where we have selected measure Sales Amount with respect to Calder Year 2007 of date dimension.

Result will be as follows –

So writing first statement was not that tough??

You can put different member of different dimension and can try by your Self.

In the Next blog I will go more detail about MDX and will discuss some of the function of MDX.

Microsoft SQL Server 2012: changing course of BI

With the resale of SQL Server 2012, Microsoft is taking himself one step ahead in BI market.

In this resale we have seen significant improvement in Microsoft BI Stack including IS, AS, RS and Self Service BI.  

But here I am not going to talk about the improvement Microsoft is doing in his product.

Here I would like to explain how it can change the BI implementation thinking.

Earlier

Following is traditional BI cycle.

In a typical BI implementation see above Pic, We usually build a Enterprise BI solution and then this solution gets divided for different teams and then we emphasize on an individual need of BI.

Now Self-service implementation is growing and more and more customers are looking towards self-service implementation to have bigger percentage in their BI implementation policy.

Microsoft has included/modified their tool stack to make self-service more powerful and more effective. Now self-service BI tools can be a center point in the BI implementation and around that we can build rest of the module.

Now by SQL- 2012

Now tools like PowerPviot can be used to build a quick BI solution or to show case (as Proof of concept) what can be achieved.

A person in a team and enterprise can now use this tool and build a quick solution using PowerPviot.

This solution he can use it for individual use. Also he has option to share this with his team using SharePoint.

This can be also considered as first step and if solution satisfy the needs of larger audience than  it can be implemented at enterprise level and can go for full implementation cycle of BI use stander practice tools like IS, AS, RS and PPS. 

This way you have guarantee of the acceptance of your BI solution before you have implemented. (Remember this one of the challenges customer faces)

So now I feel whole stack can be turn around to give you different picture. See below.

 PS: I am sure people will have different opinion and it may differ than above one. It just my perspective with new SQL Server release.

SSRS: URL Native and Integrated mode

Many times some application would require accessing SSRS. One way of doing it is by URL access.

So if you are looking for accessing Reports using URL’s here are the example in Native and in Integrated Mode.

We can pass parameter in SSRS report URL also we can change the format of it.

For Native Mode URL -

http://MacineName/reportserver?/Folder/ReportName&rs:Format=HTML4.0&rs:Command=Render

For SharePoint Integrated Mode

https://mysharepointsite/_vti_bin/ReportServer?https://mysharepointsite/mydoclib/myreport.rdl&rs:Command=Render&rs:Format=HTML4.0

Here HTML is rendering extension. Common extensions are HTML4.0, IMAGE, EXCEL, WORD, CSV, PDF and XML.

You can change the rendering format and can get report in any of the export options.

Keep Playing with it and you will see wonderful options.  Cheers. 

FME Desktop : Tool for Spatial Data

FME Desktop tool is required for converting ESRI shape files into spatial data so that we can use this for creating map reports.

Reading data from shape file.

Writer for converting spatial data and loading it  to the SQL Server Database.

Connecting from reader to writer, mapping columns and run the translation  to load  the data into the database.

SQL Server Installation - Things to Remeber

Some Important things to remember before you go for SQL Server Installation.

1. For local installations, you must run Setup as an administrator. If you install SQL Server from a remote share, you must use a domain account that has read and execute permissions on the remote share.

2 You may need to apply cumulative updates to the original media before you install SQL Server 2008 R2, if you are affected by a known issue in the setup program.

3 Typical stand-alone instances of SQL Server 2008 R2, whether default or named instances, do not use a nondefault value for the Instance ID check box.

   

4  Instance root directory — By default, the instance root directory is C:\Program Files\Microsoft SQL      Server\100\. To specify a nondefault root directory, use the field provided, or click Browse to locate an  installation folder.

5  If you specify nondefault installation directories, ensure that the installation folders are unique to this instance of SQL Server. None of the directories in this dialog box should be shared with directories from other instances of SQL Server.

6  The VIA protocol is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

7  Microsoft SQL Server 2008 R2  requires  Windows Installer 4.5 and .Net Framework 3.5s.

8   Domain Account: This account is a part of your domain that has access to network resources for which it is intended to have permission for. It is always advised to run SQL Server and related services under a domain account with minimum privilege need to run SQL Server and its related services.The Domain Account Password should not  expire.

NOTE:Microsoft recommends that you do not use the Network Service account for the SQL Server or the SQL Server Agent services if an account with lesser privileges is available, because Network Service is a shareable account. Network Service is appropriate for use as a SQL Server service account only if you can ensure that no other services that use the account are installed on the computer. Local User or Domain User accounts that are not a Windows administrator are more appropriate for SQL Server services.

9  Before you upgrade SQL Server, enable Windows Authentication for SQL Server Agent and verify the required default configuration: that the SQL Server Agent service account is a member of the SQL Server sysadmin group.

10  For Analysis Services instances that you deploy in a SharePoint farm, always use SharePoint Central Administration to change the server accounts for PowerPivot service applications and the Analysis Services service. Associated settings and permissions are updated to use the new account information when you use Central Administration.

11  If you have no domain accounts or running under multiple domains and Your SQL Server needs access to other resources in the network, you can create an user account with same username and password across all Servers and use that as service account in your servers.VN:F [1.9.13_1145]