Running Total handling Null Values

I have written an artilce on running total in SSAS 

Regarding this recently I got a problem by mail for running total in SSAS by one developer from U.K.

sys.dm_exec_procedure_stats DMV to get cached proc stats

We all are used to sys.dm_exec_query_stats which gives us useful information for queries running on the server.
Similarly in SQL 2008 we can get details for procedures. For this we will have to use sys.dm_exec_procedure_stats
It is supported in SQL 2008 and higher versions.

TYPED flag in Member Properties in Analysis Service

We are using  member properties in Analysis Service for various purposes.
From 2005 onwards its providing typed flag as extension to member properties.

Various way to calculate running total from All Period to Currentmember

Various way to calculate running total from All period to currentmember

As MDX is a very vast and dynamic language we can write same query using multiple way
One of the regular demand in MDX is to sum running total from starting of time period to current time  member. We can do it using various way.

Rank Function (For same value)

As we all are aware of rank function. It gives incremental rank for selected set.
I am going to discuss a silent feature of rank which many are not aware since now.

Rank with partitioning - MDX

Rank on grouping

Recently I got a problem of ranking based on a group.
Lets try to solve the same problem using Adventure Works cube.

Having function in SSAS

Having function

This is undocumented function in SSAS. Those who have worked with SQL Server knows that having is used to filter aggregate data. Same use here. In general we can get same result using filter function but having is more easy to understand and might be giving better result.

Expected Annual Sales (MDX- Analysis Service)

Recently I got a query on to show annual sales.

For example in Adventure works for year 2004 there is sales upto July 31st.

Discover Method Part 2

Lets look at some more Discover Commands . Many of this commands are not documented at BOL .

For previous part of this article
Discover Method Part 1

DMVs in Analysis Service --Part4

For previous part of this article

DMVs in Analysis Service -- Part 3 (MDSCHEMA_MEMBERS)

DMVs in Analysis Service -- Part 2 (MDSCHEMA)

DMVs in Analysis Service -- Part 1 (DBSCHEMA)

Last Nonempty Date/ Last Transaction Date

I freqeuntly got question on to get last non empty date or last date when transaction happened.

Here we will look at two example on how to get it from Adventure Works cube

DMVs in Analysis Service -- Part 3 (MDSCHEMA_MEMBERS)

Till now we have used MDX query to get hierarchical members in a hierarchy, But after DMVs its now possible to get this values using DMV MDSCHEMA_MEMBERS
It will be a fun to use DMV to get value. Lets more explore in it.

DMVs in Analysis Service -- Part 2 (MDSCHEMA)

As we all know that AS 2008 has come with many new features
I am using SQL Server since last 8 years and DMVs are great improvement in SQL Server 2005
Now exciting thing is that this DMVs are also in Analysis Service
Till now its not possible to view metadata in Analysis Service , but after 2008 its possible.
We will explore this DMVs in coming articles.

DMVs in Analysis Service -- Part 1 (DBSCHEMA)

As we all know that AS 2008 has come with many new features
I am using SQL Server since last 8 years and DMVs are great improvement in SQL Server 2005
Now exciting thing is that this DMVs are also in Analysis Service
Till now its not possible to view metadata in Analysis Service , but after 2008 its possible.
We will explore this DMVs in coming articles.

How to get comma separated list of members for Dimension Attribute

Q:- I want a comma separated list of all members of attribute
How can I get it?

Clear Cache and bug in Clearing Cache for partition

We can Clear Cache by using ClearCache command in XML for Analysis (XMLA)

for a specified Database, Cube, MeasureGroup ,dimension or partition.

How to create subcube in analysis service

It restricts cubes space to selected member in the select expression for create subcube statement


CREATE SUBCUBE

AddCalculatedMembers - MDX

AddCalculatedMembers

By default when we create calculated members we can not see them while resolving member of a set.

How to get last sales date/sales amount for each product -Analysis Service

How to get last date/last sales amount for each product when product was sold.

We are looking at a sample which will show last sales date and slaes amount for each product. Similarly we can get first date when product was sold using this function. We can use LastNonEmpty function but its not available except developers edition.

SQL 2008 SP1 released

MS has released SP1 for SQL Server 2008.

To download click here

Also MS has released cumulative update of SP1.

To download click here