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.

On base of it we want to get expected sales in coming days.

For Ex. upto July 31 total Internet Sales is about $9,770,899.74 in 213 days.

So daily average sales is $9,770,899.74/213 = 45872.768732

so on base of it Sales upto 1St august will be 45872.768732 *214 days
on 2nd August Sales will be 45872.768732 * 215 days

This way we can predicate sales for the remaining days in the year.

Lets see how we can get this

/———————————————————————–/

WITH MEMBER [Measures].[SalesDayCount] AS
sum(ytd(),iif(isempty([Measures].[Internet Sales Amount]),0,1))

MEMBER [Measures].[sumamount] AS
sum(YTD(),[Measures].[Internet Sales Amount])

MEMBER [Measures].[TotalDayCount] AS
sum(YTD(),1)

MEMBER [Measures].[Expected Sales] AS
([Measures].[TotalDayCount]*[Measures].[sumamount])/[Measures].[SalesDayCount]

SELECT {[Measures].[Internet Sales Amount],[Measures].[sumamount],[Measures].[SalesDayCount],[Measures].[TotalDayCount],[Measures].[Expected Sales]} on 0,
DESCENDANTS([Date].[Calendar].[Calendar Year].&[2004],
[Date].[Calendar].[Date],SELF)
ON 1
FROM
[Adventure Works]

/——————————————————————————/

How to backup multiple database using XMLA in one batch

Recently I got a question on how to backup multiple database using XMLA in one batch.

&sp;Lets see the problem

Here are two XMLA scripts for backup databases

  <Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;
    
    <File>adv1.abf</File>
    <AllowOverwrite>true</AllowOverwrite>
  </Backup>

  <Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;
    
    <File>mine1.abf</File>
    <AllowOverwrite>true</AllowOverwrite>
  </Backup>

Here when you run this query in XMLA query window it will give error

Executing the query …
The ‘Backup’ element at line 15, column 76 (‘http://schemas.microsoft.com/analysisservices/2003/engine&#8217; namespace) appears more than once under Envelope/Body/Execute/Command.
Execution complete

so we need to run it in batch. In batch we can run multiple XMLA query. But since this is backup we can not run it in transaction. So we have to specify transaction as false in the query

So the new query will be

<Batch xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine” Transaction=”false” &gt;

  <Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;
    
    <File>adv1.abf</File>
    <AllowOverwrite>true</AllowOverwrite>
  </Backup>

  <Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;
    
    <File>mine1.abf</File>
    <AllowOverwrite>true</AllowOverwrite>
  </Backup>

</Batch>

Cheers
Amish Shah