Analysis Services: Can we change the mode of an instance after installation?

SQL Server Analysis Services allows us to install the instance in three different modes;

• Multidimensional
• Tabular
• PowerPivot for SharePoint

Once the instance is installed with the selected mode, it cannot be changed to another and no interface is given for changing it too. However, there is way of changing the mode if you have not yet created a database on that instance.

Microsoft does not recommend to change mode after the installation but it can be done by changing the msmdsrv.ini file. If you are to change this file for changing the mode, you need to make sure that it has no added objects (databases). You may remove all added objects and change the type but it does not guraentee that it will work as expected hence not recommended.

The msmdsrv.ini is located in C:\Program Files\Microsoft SQL Server\MSAS13.MSSQLSERVER\OLAP\Config path.

You may experience the following issue:

If so, open your text editor (Notepad) as an administrator and open the file.

Once opened, locate the property named DeploymentMode. Value of it can have following:

• 0 for Multi-dimensional
• 1 for Power Pivot for SharePoint
• 2 for Tabular

You can change as you want and save it.

Once it is changed, you can easily see whether it has accepted your change by looking at the icon set to the instance:

SQL Server Brain Basher of the Week #036 - Analysis Services Dimensions

Here is this week Brain Basher. This is based on Analysis Services Multidimensional model which allows you to create models based on dimension and fact tables. Biggest advantage with multidimensional model is, it creates aggregates (once enabled) based on the hierarchies defined and stores them for improving the performance of data retrieval from the model. The creation of aggregation is done during processing and it extracts data from sources (preferably from the RDW), creates aggregates if it is enabled and then transfers and stores dimension and fact data (row level - based on grain level) including aggregates in the model. Now the question is;

Which dimension type does not calculate aggregates based on the hierarchies defined when the cube is processed?

Calculating aggregates is not applicable for all dimension types. If the dimension is Parent-Child dimension which is a imbalance and changing dimension, the hierarchy structure is calculated at run time, not during processing. This makes the dimension write-enabled, means that records in the dimension table can be updated and updates can be seen in the model without processing the dimension.

Remember, even though this reduces the processing time, it increases the time it takes for retrieving data. If you see this as a disadvantage, you need to handle the dimension as a regular dimension.

Analysis Services Tabular Model Workspace Database Retention: Why it is important?

When creating a Tabular Data Model project, it creates a workspace database in Analysis Services Tabular Instance for maintaining all the data and metadata while it is being developed. This database is an in-memory database and with default setting, it gets dropped from the instance when the project is closed. However this default setting can be changed by setting workspace database retention property, adjusting the behavior of the workspace database. That is why it is important.

Be default, workspace retention is set to Unload from memory. When creating a project, it creates a database in the disk (default location of SSAS instance) as well as a database in-memory using the SSAS Tabular instance. Name of the database is created as ProjectName_UserName_GUID. 

Here is the default setting. See the default setting.

Database created in the disk;

Database created in the SSAS Tabular instance;

With this setting, when the project is closed, in-memory database in the instance is removed. But the database in the disk remains and it is used for creating the in-memory database when the project is opening again. Below image shows the instance once the project is close. Note that in-memory database is dropped.

This setting frees up memory allowing system to use memory for other applications but this might slow down re-opening project as it has to build the database again.

If need, you can keep it in the memory without getting it dropped. This is useful if you continuously working with the project. However, be cautious, it does holds the memory consumed without releasing.

If you rarely open the project (usually, after completion of the development), it is better to set workspace retention to Delete workspace as it deletes the database completely. 

Local or remote workspace database?
If possible, developer should use a local instance for the workspace database because it provides the best performance. However remote server can also be used with following limitations;

• Project cannot be created by importing from PowerPivot template.
• No Backup to disk option with Data Backup property.
• Slower performance because of the latency.

Analysis Services Tabular Mode: Counting non-numeric columns using COUNT function

When creating calculated columns or measures in Analysis Services Tabular Mode, we use DAX expression language for adding them. DAX offers number of functions for implementing them and almost all general functions such as SUM, COUNT, AVERAGE are exist too. However some functions behave differently when compare these function behavior with other tools. Here is an example;

Assume that you need to get a count of two columns related to a loaded data table. Let's say we have loaded Internet Sales table from AdventureWorksDW2014, and need to create Count of Promotion Key and Count of Sales Order Number (of course, there is no business logic on these two counts, it is only for demo purposes), then we add two measures like below;

CountSalesOrderNumber:=COUNT([SalesOrderNumber])

CountPromotionKey:=COUNT([ProductKey])

Once added, you will see that CountPromotionKey works fine but CountSalesOrderNumber does not work. The reason for this is, Count function in DAX only works with numeric and dates. For counting non-numeric values, COUNTA has to use instead of COUNT.

CountSalesOrderNumber:=COUNTA([SalesOrderNumber])

Note that both functions do not count blank values. If you want to get the count including blank values, use COUNTROWS function. In addition to these, COUNTX and COUNTAX are given too. They are for getting the count based on a filter.

=COUNTAX(FILTER('Reseller',[Status]="Active"),[Phone])

For getting the count only on blank values, use COUNTBLANK.

SQL Server Brain Basher of the Week #036 - SQL Server

SQL Server 2014 introduced In-Memory OLTP tables and many of you know the benefits you get with it. This feature allows you to create table in memory either as durable or non-durable and it is only for tables. Do we create in-memory databases? Can we create in-memory databases? Here is this week question based on it?

Which SQL Server component create in-memory databases that are visible for our implementation?

• SQL Server Engine
• SQL Server Analysis Services
• SQL Server Integration Services
• SQL Server Reporting Services

This question can be answered quickly if you have worked on Business Intelligence solutions. Yes, it is related to Analysis Services but for a specific type only. Analysis Services allows us to create two types of models; Multidimensional and Tabular. When working with a Tabular data model project, as you have experienced, it needs a workspace database which is created in Analysis Services tabular instance. This database is in-memory database that keep all data and metadata for the project while it is being developed. When the project is closed, it removes from the memory by default, however it can be controlled by setting Workspace Retention property.

Analysis Services: How to stop showing records when unrelated dimensions are used with measures?

It is very common to see an Analysis Services database (or a cube) that holds multiple measure groups. If your cube holds multiple measure groups, then you have many number of dimensions and they might not link with all measure groups. In that case, what if a dimension not linked (or unrelated) is used when analyzing a measure in particular measure group?

By default, if an unrelated dimension is used with a measure, dimension is forced to use its top level for showing the value. Example, if Sales Amount from Sales Summary measure group is analyzed with Reseller dimension which is unrelated, result will be as below;

This is controllable. We can stop showing records, when a measure is being analyzed by an unrelated dimension. All we have to do is, set false for a measure group property called IgnoreUnrelatedDimensions.

Once processed, you can test the same and result will be something like below;

Analysis Services: I see a new measure group when a measure is created with Distinct Count

Have you noticed that a new measure group is automatically created if you have created a new measure with Distinct Count usage? Microsoft Analysis Services create this not because of anything else, it is for improving the performance when it comes to specific analysis such as how many sales reps have sold a particular product.

You can create one with Cube Designer -> Cube Structure.

And once they are created, separate measure group are created;

And I will be able to see how many employees have sold a particular product in each year;

SQL Server Brain Basher of the Week #034 - Analysis Services Security

Security is the most important area that needs to be addressed with many solution and same goes to your Business Intelligence solutions implemented with Microsoft Analysis Services. Typically, all database management systems provide multiple server roles for managing permission, and allow us to extend it either creating custom server roles or database roles. This week question is based Analysis Services server roles;

How many server roles are exist with Analysis Services and what are they?

You do not want to think about this much. Analysis Services offers only one server level role called as Administrators. This cannot be modified and we cannot create any additional roles, but we can add members to this role providing admin rights to anyone required. However system allows us to create additional database level roles which are limited to specific databases.

You can access Administrators role by getting properties of the instance and selecting Security property.

Database roles can be created by accessing Roles node under the database.

Trying to connect on-prem Analysis Services using Power BI Analysis Services Connector?

Although Power BI Pro allows us to connect with On-Prem Analysis Services Tabular instances for consuming live data into dashboards and report, there are limitations on it. If you try to configure Power BI Connector with On-Prem Analysis Services Tabular instance using an effective account (such as onmicrosoft.com account) without an Active Directory configured domain or using an active directory account without syncing it with Azure Active Directory, you will not be able to configure. Here is what I get when trying to configure Power BI Analysis Services Connector with my Tabular Instance installed in my standalone laptop.

All Prerequisites are listed at https://powerbi.microsoft.com/en-us/documentation/powerbi-analysis-services-connector/.

Here are important things to be remembered;

• Only SQL Server Analysis Services Tabular models are supported for Power BI.
• The connector has to continue running and the computer where it is installed has to remain on.
• Power BI Analysis Services Connector and the Data Management Gateway on the same computer cannot be co-exist.
• The Analysis Services server is domain joined.
• If you use a .onmicrosoft.com email address, you'll need to sync your Active Directory to Azure Active Directory using Azure Active Directory Sync (DirSync). T

Analysis Services: Cannot see applied color for calculated measures

Color expression section of Calculations tab of Cube Designer allows us to set Fore Color and Back Color for the Calculated Measures/Members based on an expression. Once it is set properly, we should be able to see colored values for the measure created based on the expression evaluation. What if you do not see it with your MDX statement?

For example, see the below Calculated Measure called Profit, which has a Color Expression set.

And when you browse it with Browser tab, you do not see the color;

And when you get it using MDX, still it does not show the color;

But when it is opened with Microsoft Excel, it CAN BE SEEN.

What is the reason, since it can be seen with Excel, means expression set is correct and it should be worked.

The reason for MDX statement is, it needs CELL PROPERTIES to be added to the statement in order to see colors with management studio. Not only with management studio, even with other applications. Here is the way;

SSAS: How to find User-Defined Member Properties of a dimension?

User-Defined Member Properties are attribute relationships added to specific named level in a dimension. Once defined, they can be access using either PROPERTIES key word or Properties function. Here is and example of accessing a user-defined properties.

-- from MSDN
SELECT 
   CROSSJOIN([Ship Date].[Calendar].[Calendar Year].Members, 
             [Measures].[Sales Amount]) ON COLUMNS,
   NON EMPTY Product.Product.MEMBERS
   DIMENSION PROPERTIES 
              Product.Product.[List Price],
              Product.Product.[Dealer Price]  ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Month of Year].[January]) 

However, if you do not know all user-defined properties added and need to get a list, you can simply use Analysis Services Schema Rowsets for querying out information such as objects, state, sessions, connections, etc. There are many schema rowset under OLEDB for OLAP Schema Rowsets and the one we have to use is MDSCHEMA_PROPERTIES. Here is the query for getting all user-defined properties of a level in a dimension.

SELECT *
FROM $SYSTEM.MDSCHEMA_PROPERTIES
WHERE [DIMENSION_UNIQUE_NAME] = '[Dim Customer]'
 AND [HIERARCHY_UNIQUE_NAME] = '[Dim Customer].[Customer]'
 AND [LEVEL_UNIQUE_NAME] = '[Dim Customer].[Customer].[Customer]'
 AND [PROPERTY_TYPE] = 1;

Read more on Analysis Services Schema Rowsets: https://msdn.microsoft.com/en-us/library/ms126233.aspx
Read more on OLE DB for OLAP Schema Rowsets: https://msdn.microsoft.com/en-us/library/ms126079.aspx

Analysis Services (SSAS): Calculating running totals for a given period using MDX

Calculating running totals against a numeric is not an easy task with both relational databases using T-SQL and multidimensional databases using MDX. In a way, calculation on relational databases is little bit easier using new window functions than using multi-dimensional queries. But it does not mean that the calculation using MDX is much more complex and difficult to write. However it depends on the familiarity. Comparatively we do not write MDX statements as much as we write T-SQLs. That is the reason for tagging “complex” on MDX, that is the reason for spending much time even on a simple query than the time spend with T-SQL for similar implementations.

I had to write a similar query today for calculating running totals mixing with YTD and previous year values. As usual I searched for best practices, there were many posts on this based on either calendar year or financial year but could not find a specific one for calculating running totals for a given period. Wrote it, and thought to share it. Here is the MDX, it is written on AdventureWorksDW. You may find it interesting and helpful.

1. WITH MEMBER [Measures].[Running Total]
2. AS
3. (
4.     AGGREGATE([Date].[Calendar].[Month].&[2006]&[10]
5.             :[Date].[Calendar].CurrentMember
6.             , [Measures].[Sales Amount])
7. )
8. SELECT
9.     {[Measures].[Sales Amount]
10.     , [Measures].[Running Total]} ON 0
11.     , [Date].[Calendar].[Month].Members ON 1
12. FROM (SELECT {[Date].[Calendar].[Month].&[2006]&[10]
13.             :[Date].[Calendar].[Month].&[2007]&[11]} ON 0
14.         FROM [Adventure Works])

Analysis Services: Exception of type 'System.OutOfMemoryException' was thrown.

There could be many reasons for this but in most cases, this misleads us, specifically if the MDX is running with Management Studio. You see that there is enough memory, and you are confident of the MDX, but why the Management Studio throws this?

Reason is simple. This Knowledge base article explains it;

“SSMS is a 32-bit process. Therefore, it is limited to 2 GB of memory. SSMS imposes an artificial limit on how much text that can be displayed per database field in the results window. This limit is 64 KB in "Grid" mode and 8 KB in "Text" mode. If the result set is too large, the memory that is required to display the query results may surpass the 2 GB limit of the SSMS process. Therefore, a large result set can cause the error that is mentioned in the "Symptoms" section.”

How do we make sure that MS throws the exception because of its limitations, not because of anything else. Simplest way is, get the code executed using another technique. It can be a .NET code using a provider that supports executing MDX against SSAS or could be a SSIS package. I used a simple SSIS package that has a connection to source and data reader destination. Here is the way;

1. Create a new SSIS project and add an OLE DB Connection to SSAS.
   
2. Make sure you add “Format=Tabular” for Extended Properties in Connection Manager.
   
   
3. Add a data flow task and configure an OLE DB Source.
4. Set the SSAS connection made to OLDE DB Source and add the MDX statement as SQL Command.
5. Add a Data Reader destination and connect with the source.
6. Execute and see.
   

If the code gets executed without any issue, it means you are hit by SSMS limitation not because of anything else.

Analysis Services - Backup and restore errors: File '.abf' specified in Restore command is damaged or is not an AS backup file. The following system error occurred: Access is denied. (Microsoft SQL Server 2008 R2 Analysis Services)

If you experience this error when an SQL Server Analysis Services backup is restored, most probably the reason is Analysis Services – Service account has no permission on the file (or folder). Simply grant permission for the account by accessing its properties – Security tab.

Ordering Attribute Members of Dimensions in Analysis Services

As each attribute in a dimension can have a key column and optionally name column, attribute can be sorted by either key or name column. Sorting is done with OrderBy property. Once the property is set, attribute is sorted in ascending when it is displayed. This is okay with most cases but in some cases sorting is needed in a different way. Examples cane be, sorting in descending order, sorting by not its key or name, by some other attribute. This post discusses these exceptional cases.

Sort by some other attribute
Good scenario for this is Month attribute in a Month dimension. Usually we set Month attribute’s Key Column as MonthKey and its Name Column as MonthName. If the sorting is set as Key with OrderBy property, months will be ordered chronologically only if you have inserted them in source table in an order. If they are not inserted in an order, months will not be ordered as we want. Again, if OrderBy property is set as Name, months will be displayed in alphabetical order which is not the way we usually want. See the image below.

Note that records are not inserted in an order. Month attribute’s key is set with MonthKey and its name is set with MonthNameWithYear. You can see how they are displayed with OrderBy set as key and name.

In order to have the proper ordering on Month attribute, we need an additional column to be used for sorting. Best candidate is MonthNumberOfYear column. It can be used as sorting key for Month. One requirement for doing this is, have a relationship between Month and MonthNumberOfYear, in other words, making MonthNumberOfYear as Related Attribute for Month. By default, when the dimension is created, wizard adds all the dimension’s attributes as related attributes of the key. In this case, it has been added too. If not, you need to manually add this through Attribute Relationships (a tab) in BIDS’s Dimension designer.

Once the column is set as a related attribute, change the OrderBy to AttributeKey and set OrderByAttribute as MonthNumberOfYear.

Once processed, months will be displayed in an order as we want. There are two things to be considered in this setting. If MonthNumberOfYear column is not required for querying, you can hide it by setting AttributeHierarchyEnabled property to false. Other thing is, Relationship Type between an attribute and related attribute. It can be set as either Rigid or Flexible. If the relationship is fixed over time, not going to change (Type 2), rigid is the best option. If the relationship can change over time (Type 1), type has to be set as Flexible.

Descending Order
Once the sorting is set for an attribute, it is sorted in ascending order. That is the only possible sorting order supported by Analysis Services. If descending order is required, just like the previous one, support is needed from another attribute that maintains value in descending order. For example, if months need to be sorted in descending, MonthNumberOfYear has to be filled with values 12 to 1, not 1 to 12.

Analysis Services Security: Dimension Data – Select all members or Deselect all members?

While configuring Analysis Services security on Dimension Data, I did not pay much attention on two options available with the screen; Select all members and Deselect all members. However the importance of them was raised up when my colleague Asanka Padmakumara showed a point on them. Which one should we select? See the screen below;

I have selected the first option which is Select all members and granted permission on Accessories and Bikes. I can do the same, select the other option too. Let’s move into Advanced section and see how it has been set;

Note that other two categories (Clothing and Components) have been added under Denied member set. What we have set? Yes, we granted permission on Accessories and Bikes but we have done indirectly is, denying permission on Clothing and Components. Main thing we need to consider is, permission on future categories. Since the items we did not select have gone to Denied member list, all future items will be available for this role. If you need to let this role to access future categories without explicitly setting them, use Select all members for setting permission.

Now let’s use the other option; Deselect all members;

I have given same permission set but the option selected is Deselect all members. Have a look on Advanced section now;

Now you can see the items we selected have been added to Allowed member set. This means all other items we did not select are denied to this role. What will happen to future categories? This is the difference. Since we have explicitly added Accessories and Bikes to Allowed member set, future items will NOT be available under Allowed member set. If you need to disallow this role to access future categories without explicitly setting them, use Deselect all members for setting permission.

Analysis Services Security: DENY does not override GRANT

Is the famous rule of thumb broken? I am sure that you all are aware the rule and you know that DENY always wins over GRANT. If you want to see an exception (or a situation) read one of my previous posts “Column GRANT overrides DENY TABLE, Avoiding with Common Criteria Compliance”.

This post discusses another situation, it is not with SQL Server Relational Engine, it is will SQL Server Analysis Services.

Analysis Services behaves bit differently. As you know, if an object of SQL Server database is denied explicitly, it is always denied even with an explicit grant. For example, if you are in a Role that grants SELECT on Table1 and in another role that denies SELECT on Table1, you will not be able to execute SELECT on Table1. Analysis Services authorizes on items differently. It unions all grants and authorizes. If you are in a Role that grants on Item1 and Item2 (Note that in Analysis Services, once items are granted, all other items are denied, no need to explicitly deny them as SQL Server database engine objects) and in another Role that grants Item2 and Item3, you are granted for Item1, Item2, and Item3.

Let me show you a sample on this. The below image shows a Role in Analysis Services Adventure Works DW 2008R2 database. This is how it is created;

• Name: Business Users
• Membership: Dinesh
• Cubes: Adventure Works – Read
• Dimension Data:
  • Product Dimension, Category – granted for Accessories and Bikes
  • Other Dimensions – granted for all

Second image shows another role in same database. Its properties are as follows;

• Name: Coordinators
• Membership: Dinesh
• Cubes: Adventure Works – Read
• Dimension Data:
• Product Dimension, Category – granted for Bikes and Clothing
• Other Dimensions – granted for all

Business Users role denies for Clothing and Components. Coordinators role denies for Accessories and Components. When Dinesh sees data, he sees union of both; Accessories, Clothing, and Bikes. The reason for this is, Analysis Services uses all GRANTs from all Roles for connected user and then authorizes. That is why he sees Accessories and Clothing even though they are denied from each role. This is how he sees when Categories are loaded with Excel.

Note: This type of security cab be tested with Management Studio too. Start Cube Browser and click on Change User icon. The opened window allows you to set the context with Current User, Other User, or Roles.

MDX Error with OLEDB: The Parameter is incorrect

If you have read my previous post, you know why I went for “OLE DB - Microsoft OLE DB Provider for Analysis Services 10.0” instead of “Microsoft SQL Server Analysis Services - .NET Framework Data Provider for Microsoft Analysis Services” for querying data from a cube into SSRS reports. Unfortunately I have to give up the OLE DB Data Source Type because it does not support for parameterizing the MDX query. When a parameter is added, I get following error:

An error occurred during local report processing.
An error has occurred during report processing.
Query execution failed for dataset ‘DataSet1’.
The following system error occurred: The parameter is incorrect..
Error Code = 0x80070057, External Code = 0x00000000:.

As usual, I googled and found that it has been reported on January 2007 and has identified as a bug in .NET’s System.Data.OleDb. The thread has been updated again on April 2008, and seems not fixed. You can read the thread here.

Anyway, since it is related to .NET, I am going to start searching on .NET updates and fixes on this. I will share if I can find something and solve this issue with it.

MDX Error: First Axis of the query contains dimension other than Measure Dimension…

If you try to set some other dimension elements as the first axis other than measure dimension, you will receive an error like below:

I got this error with Reporting Services when I try to query the OLAP database since I had referred some other dimension in the first axis. How do we fix this:

All we have to do is, change the Data Source type for OLE DB and select the provider as Microsoft OLE DB Provider for Analysis Services 10.0. You may see some limitations with this :(.

Connecting to Analysis Services with the Port Number

First of all, let me tell you that why I need to connect to Analysis Services with the Port number; because the Analysis Services 2008 is running as a named instance and SQL Server Brower needs to be disabled as a security measure. Why do we need SQL Server Browser Services? By default, Analysis Services uses the port 2383. When you connect to the default instance (in my case, it is 2005), it connects to the SSAS without any problems, even though you have not specified the port with the connection string. If you try to connect to a named instance of Analysis Services (again, in my case, it is 2008), without specifying the port, you will still be able to connect, as long as the SQL Server Browser is running. Named instances of the SSAS use port numbers that are dynamically assigned, therefore they are varies. When you restart the service, it assigns a new one. Assigned port number can be seen with msmdredir.ini file in the C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig folder. Though we do not specify the port in the connection string (even with SSMS), SQL Server Browser handles the request and directs us to the correct instance, because it knows the port numbers assigned. Now my issue was, unavailability of SQL Server Browser hence cannot connect to the named instance. When we try to connect to the named instance with SSMS or BIDS, it says that Browser is not running. Since the Browser cannot be enabled (as a security measure), all we have to do is, find the port number (from the ini file specified above) and specify with the connection string. One thing you have to remember is, when specifying, it should be {MachineName}:{PortNumber}, NOT like {InstanceName}:{PortNumber}. If my machine name is DINESH-PC and named instance is SQL2008; When connecting to the default instance: DINESH-PC:2383 When connecting to the named instance: DINESH-PC:50019, NOT DINESH-PC\SQL2008:50019 If need to fix a port number, use the ini file or Port property in Analysis Services, for changing.