Do not delete the default 'Calculate' in the 'Calculations' tab in Cube

The default 'Calculate' under the  'Calculations' tab in Cube is important to keep. Today, I define a named calculation. Then I delete the 'empty' default Calculate statement. After deployment, when I browse the cube and find no numbers for any measures. Then I use the form view, add 'Calculate' back. After deployment and re-connection, the numbers show up.

How to order the MDX query results?

Problem:
Select[Measures].[Sales Amount] on Columns,

      ([Product].[Category].members) on Rows

From  [AdventureWorks]

where [Date].[Calendar Year].&[2003]

 

It returns the result as below:

                               Sales Amount

All                          9791060.29770049

Bikes                      9359102.61770052

Components           (null)

Clothing                 138247.970000006

Accessories            293709.709999959

Unknown               (null)

 

But I need it ordered by category name as below:

                               Sales Amount

All                          9791060.29770049

Accessories            293709.709999959

Bikes                      9359102.61770052

Clothing                 138247.970000006

Components           (null)

Unknown               (null)

 

Solution:
Option 1 - Use the Order function:

Select[Measures].[Sales Amount] on Columns,

      Order([Product].[Category].members, [Product].[Category], desc) on Rows

From  [AdventureWorks]

where [Date].[Calendar Year].&[2003];

Notice, it's DESC, not ASC. Why so? Because The Order function can either be hierarchical (as specified by using the ASC or DESC flag) or nonhierarchical (as specified by using the BASC or BDESC flag; the B stands for "break hierarchy"). If ASC or DESC is specified, the Orderfunction first arranges the members according to their position in the hierarchy, and then orders each level. If BASCor BDESC is specified, the Order function arranges members in the set without regard to the hierarchy. In no flag is specified, ASC is the default. The members in the hierarchy are not necessarily arranged alphabetically. See BOL for details.

 

Option 2 - Use the AllMembers function

SELECT

  [Measures].[Sales Amount] on Columns,

  [Product].[Category].ALLMEMBERS ON ROWS

FROM [Adventure Works]

where [Date].[Calendar Year].&[2003];

 

SSAS Deployment Error - OLE DB error: OLE DB or ODBC error: Login failed for user 'NT SERVICE\NetworkService'.; 28000.

Problem:

When I try to deploy a cube in BIDS today, I get the following error:

OLE DB error: OLE DB or ODBC error: Login failed for user 'NT SERVICE\NetworkService'.; 28000.

Reason:

Because I have used NT SERVICE\NetworkService as the service account for both the SQL Server service and the SSAS Service, and I have chosen the "Service Account" on the impersonation page for the data source. Although 'Test Connection works fine", the NT SERVICE\NetworkService account does not have the permission to access the database by default.

Solution:

Option 1 - Change the service account to Local System
Option 2 - Do not use the service account, use another account with access permission on the underlying database
Option 3 - Give the NT SERVICE\NetworkService account a permission to access to the database. see this post for details.