SSAS

Does Linked Measure Group allows to delete the not used Measures for that cube?

Hello all,

After long time gap, I just want to share something regarding the Linked Measure Group and which might be sure helpful to all of us mainly in the field of SSAS .

While creating and processing cube with Linked Measure Group I got certain error so posted on the MSDN and got the response .so, I think it will be good to share this .

Below is the scenario :

I have developed the Linked Measure Group in order to share data across cubes within duplicating data for sharing same Measure Group within multiple cubes .

While processing a cube I got certain error and all I want to know that

Let’s say we have a cube A and Cube B and we create a linked Measure group within cube B by using a Measure group of Cube A  and within linked Measure Group in Cube B , we delete some measures that is not needed for cube B and Processed cube B.

Then  I got certain error while processing a cube B.

But It’s strange that it won’t give any issue while deleting the dimension that linked within Linked Measure group in Cube B.

So, does Linked Measure group allows to delete the not used Measure for that cube.

If we process the cube without deleting any Measure from Linked Measures then successfully both the Cube A and Cube B processed.

Also ,just a quick do we need to process the Cube A first and then Cube B after in order to use Linked Measure Group correctly .since while processing only cube B I got error as

‘Errors in the OLAP storage engine: The metadata for the statically linked measure group, with the name of ‘Fact Table’, cannot be verified against the source object’.

Hope to know from the one who knows better in this field. Your small help will be really appreciated.

Response and findings:

It is not recommend to delete the irrelevant measures when create a linked measure group. We can set the “Visible” property to “False” to hide this measure. so, in simple we cannot delete the Measures from the Linked Measure Group.

If you add a linked measure group to a cube, the BIDS interface will let you delete some of the measures in the group (since you may not want to show all the available measures in your new cube) – however, doing this causes the above error when processing the cube! The error is basically saying that the linked measure group metadata no longer matches that of the source measure group.

The solution is that when you add a linked measure group, all the existing measures should be left intact. To hide them from the end user, set the “Visible” property for each measure

Reference:

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/1cfa1caf-2ff1-4991-a8e5-af842f287e38

http://bizintnz.blogspot.com/2009/10/ouch-nasty-ssas-linked-object-error.html

Thanks,

Anil Maharjan

SSAS Compare, a cool tool for Microsoft Business Intelligence professionals.

Hello all,

It’s been long time gap and I was really missing my blogging time part, busy blogging for my friends at Two Hour Blogger.

Through this blog I would just want to share the new tool developed by the Red –Gate called

SSAS Compare . which I think , SSAS Compare is one of the cool tool .

It has a great feature as

With SSAS Compare, you can select SSAS databases to compare differences between cubes, dimensions, measures and other objects.

SSAS Compare can create an XMLA deployment script to deploy changes to the target database.

This tool can be helpful when we have several versions of the same catalog/database within Production side and development side and needs to compare and find out how they differ. sometime we get confuse that which one is the latest catalog and what’s the latest different within the catalog between Production side and development side.

So, this tool can be must helpful which allows to compare the catalog and choose the required cube structure need to deploy by simply generating the XMLA script.

More on PDF:

http://www.red-gate.com/labs/ssas-compare/GettingStarted.pdf

Link:

http://www.red-gate.com/labs/ssas-compare/

Thanks,

Anil Maharjan

Showing Measure values as either ‘0’ or ‘NULL’ in cube ?

Hello all,

It been long time  ,after a long time gap I would love to post this blog which might be helpful to the one who works in SSAS.

While designing a cube everyone simply doesn’t care about the ‘NULL’ or  ‘0’ values within the cube output. So through this blog , I want to share with you guys that how can we show the NULL  or 0 value output within the cube.

Also , if anyone need to show the exact client data either it’s NULL or as 0 within a Cube then for them this blog might be helpful too.

One can handle/Preserve the NULL values of data by following the blog by DevinKnight which is one of nice blog I referred

http://bidn.com/blogs/DevinKnight/ssis/1587/ssas-preserve-measure-null-values

Since , BIDS will treat all the NULL values as ‘0’ while calculation and if you have hundreds of calculated measure and you want to show the values as either NULL or 0 as per you need.

And there you need to handle the 4cases mostly NULL/NULL,NULL/0 , 0/0, 0/NULL.

If divisor is zero, then there will be no error, the cell value simply will be positive or negative infinity represented as 1.#INF – but people don’t like this for some reason and prefer to replace it with NULL, which makes the user wonder why there is no data for that calculated Measure.

Let’s say, we have a calculated measure as [Measure].[Cal] and you need to show like this.

[Measure].[Cal] =[Measure].[A]/ [Measure].[B]

case 1 when [Measure].[A]=0 and [Measure].[B]=0, then show 0,

case 2 when [Measure].[A]= NULL and [Measure].[B]=NULL then show NULL,

Case 3 when [Measure].[A]= 0 and  [Measure].[B]=NULL then show 0,viceversa

Case4 when  [Measure].[A]= value1 and  [Measure].[B]=value2 then show value1/value2,

The best approach to calculating [Measures].[Cal] is the following:

 IIF([Measures].[B] = 0, null, [Measures].[A] / [Measures].[B] )

If you return null, NON EMPTY will work and the calculation will be faster as there are optimizations in the engine to handle this specific pattern.

Least  one used:

 IIF([Measures].[B] = 0, 0, [Measures].[A] / [Measures].[B] )

You don’t want to return a 0 in the second argument of the IIF as this will always return a value and will mean that you cannot use NON EMPTY in your queries (as the calculation would never return an empty value).

The above all 4 case can be handle within one nested IIF condition as you only want to display NULL if both A and B are NULL, otherwise it is always 0 or the number.:

IIF(ISEMPTY(A) AND ISEMPTY(B), NULL, IIF(A=0 OR B = 0, 0, A/B)) i.e

IIF(     

  ISEMPTY([MEASURES].[A]) AND ISEMPTY([MEASURES].[B]), NULL,

		IIF([MEASURES].[A] = 0 OR [MEASURES].[B]=0 , 0, [MEASURES].[A]/[MEASURES].[B])

   )
Some more details to handle like this are further describe within Mosha Blog.
http://sqlblog.com/blogs/mosha/archive/2005/06/30/how-to-check-if-cell-is-empty-in-mdx.aspx

Hope this post will be helpful for all of us in the field of BI mainly in SSAS/MDX and one who is looking for such solution.

Regards,

Anil Maharjan

How can we find the distinct count of all the Hierarchy and attribute members from all the dimensions.?

Hello all,

Firstly ,Happy New Year 2012 🙂 to all .!!!

In order to start this year blogging, I would like to post another blog which might be helpful to the one who works in SSAS.

Last time, I was just trying for some trick or some queries in order to quick find the distinct count of all the Hierarchy and attribute members from all the dimensions .

For Example, If we have ‘ PaidDate ‘ dim having Hierarchy members as PaidYear ,PaidMonth, PaidDay then we need to calculate the Distinct count of all those members and similarly for all the others dimension .Then I simply used the

SQL query method as:

Select count(Distinct(PaidYear,PaidMonth,PaidDay)) from PaidDate and similar for others but all I want  is some Quick methods or some DMV queries to find out my solution.

After some research and through MDSN, I finally got my solution .It’s just a simple DMV query as

select * from SYSTEMRESTRICTSCHEMA($SYSTEM.DISCOVER_DIMENSION_STAT,DIMENSION_NAME='Dimension', DATABASE_NAME = 'Database')

--Dimension -replace with the Dimension you want to find Distinct count and Database -replace with your DB catalog.

In the returned tabular result, the ATTRIBUTE_COUNT column is to describe the count that I was looking for.

Is there any alternative solution for this kind of issue .Hope someone have some idea regarding on this.

Hope this post will be useful for all of us in the field of BI mainly SSAS.

Regards,

Anil Maharjan

Cross joins across user defined hierarchies aren’t supported .?

Hello all,

It’s not appropriate to post as this a blog but it might help to most of us in field of BI having similar kind of confusion /issue.

I have some confusion on crossjoin function within MDx.

while I try to crossjoin the different level sets of same Hierarchy. It shows error as

For example.

‘The Customer Geography hierarchy is used more than once in the Crossjoin function.’

select {{[Customer].[Customer Geography].[Country].&[United States]}*

{[Customer].[Customer Geography].[State-Province].members}} on 0

FROM [Adventure Works]

WHERE Measures.[Internet Sales Amount]

Cannot we Cross joins across user defined hierarchies ,or they aren’t supported .?

Coz I really need to implement as above MDx within my real Cube.

I try to implement by making as another Hierarchy Member but it doesn’t gives the value result as what we want/need.

with member [Customer].[Country].[United States ]as [Customer].[Customer Geography].[Country].&[United States]

select {

{[Customer].[Country].[United States ]}*

{[Customer].[Customer Geography].[State-Province].members}} on 0

FROM [Adventure Works]

WHERE Measures.[Internet Sales Amount]

Though while looking on Adventure Works Cube ,it has attribute as Country, State-Province, and others along with the User define Hierarchy as Customer Geography with in a Customer Dim.

So while we crossjoin in the members of attribute of same Customer  dimension it gives result

SELECT

{      {[Customer].[Country].[United States]}*

[Customer].[State-Province].Members

} ON 0

FROM [Adventure Works]

WHERE Measures.[Internet Sales Amount]

But similarly, while we try to crossjoin with in the attribute members of user define Hierarchy from  Customer Geography it gives error.

Does that mean we need to create some dummy/hidden attributes for that particular dimension in other to obtain the solution for my case. ?-may be this is the solution

Does this mean we cannot crossjoin across user defined hierarchies?

It is just an example ,I simply need like this within from same user defined Hierarchy. By defining as user define calculated member of different attribute/dimension I am able to obtain the output as below but the values doesn’t valid or match.

with member [Customer].[Country].[United States ]as [Customer].[Customer Geography].[Country].&[United States]

member [Customer].[State-Province].[Alabama ]as [Customer].[Customer Geography].[State-Province].&[AL]&[US]

select

{Measures.[Internet Sales Amount]} on 0,

{{[Customer].[Country].[United States ]}*

{[Customer].[State-Province].[Alabama ]}*

{[Customer].[Customer Geography].[Postal Code].members}} on 1

FROM [Adventure Works]

Country State-Province Postal Code(Can be Top 10 members) Internet Sales Amount Other Measures
United States Alabama

2015

$9,389,789.51

 111

2450

$9,389,789.51

 222

2010

$9,389,789.51

 333

Is there any alternative solution for this kind of issue .Hope someone have some idea regarding on this.

I also posted on MSDN as link

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/f03cd36e-8a4f-4ae7-a4b4-fb6f499dc9b9

Thanks,

Anil Maharjan

How to find a Calculated Measure and Calculated Dimension with in particular cube.?

Hello all,

After such a long time , I would like to post some simple tips for calculating the Calculated Measure and Calculated Dimension within a particular cube in SSAS.

Though it might simple but this help me a lot.

I simply need to list down the calculated measures within a particular cube for which I have to open the BIDS project and lookup how many calculated Measure that I had made and their names which seems little odd. So ,after some research and trying out DMV queries I finally got a simple way .

DMV:

Dynamic Management Views is introduced in Analysis Services 2008 and is used to track the server resources used .It can be queried like SQL –Like syntax. We can run DMV query in SQL server Management Studio in an MDX Query.

For Calculated Measure and Calclated Dimension Member and other entire details of Cube Catalog from SSAS.

SELECT * FROM $system.MDSCHEMA_MEMBERS

WHERE [MEMBER_TYPE] = 4

Refer some other DMV queries from the link

http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/

http://www.bidn.com/blogs/Anil/ssas/2101/how-to-find-a-calculated-measure-and-calculated-dimension-within-a-particular-cube

Thanks,

Anil Maharjan

Backup all SSAS databases automatically and schedually.

With the course of time, you might need to backup all the SSAS databases within a server instance automatically and schedule to backup these database monthly wise or according to your specific time set.

May be there are a lot of alternative methods to perform this task and many solution’s.

But I have researched a lot regarding to backup all the SSAS databases within a particular server instance.

In my last blog post I have shared “How to schedule and dynamically backup all the SQL databases within a server instance”.
Here ,I am going to show how to obtain all the cube databases backup automatically along with schedule time set.

These are the following steps you should have to follow.
1. Adding a linked server in SSMS.
You can simply add a linked server within a SSMS by using a script as
–Adding a linked server
EXEC master.dbo.sp_addlinkedserver
@server = N’SSAS_Backup’
, @srvproduct=N’MSOLAP’
, @provider=N’MSOLAP’
, @datasrc=N’ANILMAHARJAN’ /* <<< My Analysis Services server name */
/* <<< My Analysis Services database name */
go

–Setup security as per your environment requirements.
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N’SSAS_Backup’
, @useself=N’False’
, @locallogin=NULL
, @rmtuser=NULL
, @rmtpassword=NULL
go

2. Make a SSIS package.
You can simply create the SSIS Package as shown in below

2.1. Create the table in SSMS of the output of all the catalog name within a server given by frying the query into the DMV in SSAS from SSMS.
You can use the following script in order to find all the current catalogs name within a server.
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘CubeBackupInfo’) DROP TABLE [CubeBackupInfo]
SELECT * into CubeBackupInfo FROM OPENQUERY(SSAS_Backup,’select * from $system.dbschema_catalogs’)

2.2. Read the total no of database backup to be made by reading the max count of catalog name from the table just we created above.

2.3. Read the backup XMLA within a variable into SSIS from a particular location.i.e backup XMLA that generated manually and we can also generate it by using a some C# or vb.net code embedded within SSIS.
Here is an XMLA script for backup
2.4. Now, within a for loop, set the max loop to the variable as @Max_No_Backup i.e max no of backup to be made.

2.5. Here read the catalog name of database with in a server one by one by using a table that we created before from CubeBackupInfo.
2.6 Modify the XMLA script using ‘Script Task’ within where I have used C# code in order to modify the XMLA and generate the modified XMLA for each catalog name one by one.

2.7. Backup all the cube database with in a server into a default location of SSAS backup.
-stores acc to the ‘catalog name’ along with the ‘system date’ in order to know the particular backup date. as i.e. TestCube-03-11-2011.abf
-also catalog name can overwrite it if it exist already.
i.e. : C:Program FilesMicrosoft SQL ServerMSAS10.MSSQLSERVEROLAPBackup

3.Make a SQL Server Job agent.
3.1. You can easily make a job in SQL by following this link http://www.sqlservercentral.com/articles/Stairway+Series/72267/
and Running and Scheduling SSIS Packages by following the link as http://www.sqlshare.com/running-and-scheduling-ssis-packages_53.aspx
4.Make schedule to backup all the catalogs/ database within a particular server. i.e. monthly or weekly according to your specific time set.

Hope this will help for someone , also I am thinking to post another blog about ‘Dynamic partition within a cube’ using a similar approach where I have researched a lot in this topic too.

Thanks,
Anil Maharjan

How to schedule and automate backups of all SQL Server databases in SQL Server.

Hello all,

With the course of time, you might need to backup all the databases within a server instance automatically and schedule to backup these database monthly wise or according to your specific time set.

Also, what I want to say is that ,I cannot stop myself to write this another blog of mine.

writing a blog and sharing some knowledge through the blog makes us really feels happy and also somewhat we also become addicted to it. 🙂

This may be simply for one but for someone it will be helpful for those who don’t know how to do it.

May be there are a lot of alternative methods to perform this task and many solution’s.

But I have researched a lot regarding to backup all the SQL database as well the SSAS catalogs database backup with in a particular server instance.

Here ,I am going to show how to obtain the SQL database backup automatically along with schedule time set.
Firstly, I have created a SSIS package where I used simply “Back up Database Task “ from toolbox and click right then clicking in edit tab, we can set the connection as show in figure1 below:

Then we can set the Backup Type as full or other type after that you can select the databases to backup as show in figure2 below where we can set required databases to backup.

After that you can set the other different properties like
1.Backup set Expire
2.Bakup to
3.Append or overwrite if backup exist
4.for everyday backup options
5.Backup location directory currently I set to default location.
6.allow compression or not.
You can figure out by looking the figure3 below.

Now all you need to create a job agent to schedule the backup task and run SSIS package. In order to do this you can check out the link by Brian Knight as
http://www.sqlshare.com/running-and-scheduling-ssis-packages_53.aspx

or simply go to SSMS and start Sql Server Agent and right click and create new job where you have to setup some properties as shown in figure4.

Here you can schedule the backup going into the schedule tab and then just check the job working or not by right clicking it and Start job at step then you can check the all the backup database within the default location as : C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBackup

Hope you get some ideas to obtain the schedule Sql databases backup automatically ,also I am thinking to post into my another blog for “ Automate and schedule all SSAS catalogs database backup with in a server instance by using Job Agent” or similar approach which have taken most of my research time and I am happy to obtain the result successfully.

Lastly,this may be simple but I think it may work out for someone .

Thanks,
Anil Maharjan

Backup and Restore Cube at a time using SSIS Package.

Hello all,

Firstly, what I want to say is that ,I cannot stop myself to write this another blog of mine.

writing a blog and sharing some knowledge through the blog makes us really feels happy and also somewhat we also become addicted to it. 🙂

Also, this may be simply for one but for someone it will be helpful for those who don’t know how to do it.

May be there are a lot of method to perform this task and many solution’s.

While working I have to take a Backup of Cube from one database and Restore it into another database within a same machine.So,for this i usually do manually by right clicking the Backup and Restore it to another database but every time say development cube changes frequently and i have to take backup and then again restore it into production cube.

Doing manually seems little lazy,so i think of doing it dynamically or say by just a click so that.

For this I use a SSIS package where we can use the “Analysis Services Execute DDL Task ” from toolbox and by editing it, we can set/configure the values as:

Also, i forgot to mention that for the restore and backup XMLA script ,we have to right click on the database and click restore/backup and then click the script button so that it will generate the XMLA script for restore and backup to particular database.After generating the XMLA,we can directly put into the “Analysis Services Execute DDL Task” by configuring the “SourceType” :Direct input and SourceDirect : XMLA script (i.e of backup / Restore).

But here i use the file connection for pointing the XMLA script from particular location so that we can execute this for different database by only changing the XMLA file.

This may be simple but i think it may work out for someone .

Thanks,

Anil Maharjan