Problem in adding user in security page in Analysis Service

Recently we had a problem for adding users in Analysis service security option

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.

SQL Server 2008 R2 Analysis Services operations guide

Microsoft has releases Analysis Service 2008 R2 operations guide. 

This can be quite useful to understand new feature and optimizations in 2008 R2 edition. 

Detach DB in SSAS

Detach and attach DB in Analysis Service

Now in Analysis Service 2008 its possible to detach and attach database.
Its make easy to move database to different location which was not so easy in past

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">
    <Object>
      <DatabaseID>Adventure Works DW 2008</DatabaseID>
    </Object>
    <File>adv1.abf</File>
    <AllowOverwrite>true</AllowOverwrite>
  </Backup>

  <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
      <DatabaseID>Mining Test</DatabaseID>
    </Object>
    <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' 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" >

  <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
      <DatabaseID>Adventure Works DW 2008</DatabaseID>
    </Object>
    <File>adv1.abf</File>
    <AllowOverwrite>true</AllowOverwrite>
  </Backup>

  <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
      <DatabaseID>Mining Test</DatabaseID>
    </Object>
    <File>mine1.abf</File>
    <AllowOverwrite>true</AllowOverwrite>
  </Backup>

</Batch>

Cheers
Amish Shah

Discover Method Part 1

DISCOVER METHODS
They are very rich source for getting meta data information for the server.
Lets look for more detail on DISCOVER METHODS

Gemini Renamed as PowerPivot

hi friends
Who are working with analysis service must be aware of Gemini.Its addon for Excel 2010 for users to handle large amount of data and analysis them , a self service BI
This project is now renamed as PowerPivot
We can get more information over here
http://www.powerpivot.com/

DRILLTHROUGH - Analysis Service

DRILLTHROUGH [MAXROWS Unsigned_Integer]
select mdx statement
Return set of attributes and measures

DistinctCount -- Analysis Service

Another Way for DistinctCount

As we all know that DistinctCount is a challenge for SSAS. Since its not an additive measure
AS needs additional efforts to get distinct count.

IgnoreUnrelatedDimension Property - Analysis Service

Q:- In my fact table some dimension are related to some fact tables. For that dimension it shows same value for all attributes. I dont want to show values for this dimension.

IsAggregatable Property - Analysis Service

Q:- I want to hide All in my dimension. How can I do it? Is it good to do it?

Ans:- In Analysis Service we generally see all dimension has All member. This is because of IsAggregatable property of the attribute. You can set its value to false, so that it will not show All member.

Problem with multilingual character in SSAS

Q:- I have multilingual keywords in my table. It contains keywords chinese/japanese/arabic and many other languages. When I create a dimension I cant see that characters , it shows box and question mark.

Memory properties in Analysis Service

Preallocate
It will allocated specified amount of memory to analysis service.By specifying preallocate Analysis Service uses large memory pages and we can not swap this to page file. It can give better performance to Analysis Service.