It takes 4 Clicks to Build a Model (Cube) in Denali Tabular Mode

Click 1 – Create a Tabular Project Click 2 – Import Tables (Master / Facts) from DW Click 3 – Adding Measure As you can see I have added 2 Measures – Sum (TotalProductCost) Sum of TotalProductCost:=SUM([TotalProductCost]) Sum of SalesAmount:=SUM([SalesAmount]) Click 4 – Viewing Cube in Excel Click on Excel Symbol on Tool Bar, this…

0

PowerShell: Getting Cube XMLA

Problem Description: Automate the steps of 1. Connecting to SSMS 2. Select Cube 3. Right click and generate XMLA “Create Cube” 4. Schedule from SQL Job agent For last few days I was playing around with PowerShell so thought of achieving this using PS. Code which I wrote for getting cube Create XMLA is: [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")…

0

SSAS Cluster: Issue with Mount Points while Performing Administrative Tasks

  Issue: Environment consists of 2 nodes, SQL and SSAS 2008 can failover between the 2 nodes, and accessible for queries. All the drives and mount points come online on the nodes after failover. SQL Server Fails over perfectly. The Windows Cluster has been validated and no errors in the log. When failover from Node…

0

MDX:Handling division by zero errors

WITH MEMBER [Date].[Calendar Year].[2006VS2007] AS [Date].[Calendar].[Calendar Year].&[2007] / [Date].[Calendar].[Calendar Year].&[2006], FORMAT_STRING = ‘Percent’ SELECT { [Date].[Calendar Year].[Calendar Year].&[2006], [Date].[Calendar Year].[Calendar Year].&[2007], [Date].[Calendar Year].[2006VS2007]  } * [Measures].[Internet Sales Amount] ON 0, non empty {([Product].[Product Categories].children ) } ON 1 FROM [Adventure Works]   If you notice where Value of Internet Sales is Null for Base Year…

0

MDX: Nonempty v/s NonEmpty

Nonempty Returns the set of tuples that are not empty from a specified set, based on the cross product of the specified set with a second set. NONEMPTY(set_expression1 [,set_expression2]) set_expression1 A valid Multidimensional Expressions (MDX) expression that returns a set. set_expression2 A valid Multidimensional Expressions (MDX) expression that returns a set. In this example, getting…

4

Top 10 products with Lowest Reseller Order count of at least 400.

  This shows how to use Head, Order, Filter & TopCount Function.   Reseller Order Count Classic Vest, S 514 Short-Sleeve Classic Jersey, XL 495 Hitch Rack – 4-Bike 468 AWC Logo Cap 460 Long-Sleeve Logo Jersey, L 451 Water Bottle – 30 oz. 444 AWC Logo Cap 442 Women’s Tights, L 437 Long-Sleeve Logo…

1

MDX Descendant Function

This example shows how to use Descendant function with Desc_Flag as self_before_after which means this will show all members at level – Subcategory_Category_Product. and to prove that we are getting correct result I am using Member Property – Level which shows Level Name to which Member Belongs With Member Measures.ProductStatus As [Product].[Status].Member_Key Member Measures.Levela as…

0

MDX: EXISTING

  By default, sets are evaluated within the context of the cube that contains the members of the set. The Existing keyword forces a specified set to be evaluated within the current context instead.   with member [Measures].[k] as ([Product].[Category].currentmember, [Measures].[Measures].[Reseller Sales Amount] ) Select [Product].[Category].[All].Children on 1 , Measures.k on 0 from [Adventure Works]…

4

SSAS 2005 Backup/Restore Limitation

  In SQL 2005, you may get below mentioned error after restoring SSAS Database using .abf file File system error (sample): The following file is corrupted: Physical file: \.cub\.1.det\.1.prt\1.fact.data">\\?\G:\AS_Data\<DatabaseID>\<CubeID>.cub\<MeasureGroupID>.1.det\<PartitionID>.1.prt\1.fact.data. Logical file. It’s a known issue with SQL 2005 backup, which is fixed in SQL 2008. SQL 2005 backup fails to properly backup files which are…

0

Different Kinds of SSAS Processing in simple words…..

Often time I been asked this quest – what will be the impact of processadd on indexes or aggregations, do I need to recreate aggregations or Indexes after creating Process Update or what is the right approach of handling processing, so thought of writing this article. Here are the major Process Types in SQL 2005…

10