Different Dialog boxes in sql server analysis service

Use the Storage Options dialog box in SQL Server Data Tools (SSDT) to specify custom storage mode, proactive caching, and notification options for dimensions, cubes, measure groups, and partitions. You can display the Storage Options dialog box in SQL Server Data Tools (SSDT) by clicking Options on the Storage Settings dialog box.

For more information about the Storage Settings dialog box

The Storage Options dialog box contains the tabs listed in the following table.

Page	Definition
General	Use the General tab to specify storage and proactive caching settings.
Notifications	Use the Notifications tab to specify the notification method and related settings.

Adding Cube Dialog Box

       Use the Add Cube Dimension dialog box in SQL Server Data Tools (SSDT) to add a reference to a database dimension to a cube. You can display the Add Cube Dimension dialog box by doing one of the following:

• Click Add Cube Dimension in the Toolbar pane on the Cube Structure or Dimension Usage tab in Cube Designer.
• Right-click the Dimensions pane on the Cube Structure tab in Cube Designer and select Add Cube Dimension from the context menu.
• Right-click the Grid pane on the Dimension Usage tab in Cube Designer and select Add Cube Dimension from the context menu.

Note1:

          Each cube dimension can have only one relationship to a measure group. However, you can create more than one cube dimension and add it to the cube, if the database dimension on which the cube dimension is based is related to measure groups through more than one relationship in the data source view.  
         

         Such dimensions are referred to as role-playing dimensions and commonly occur with time dimensions.

Options

Select dimension

Select an existing database dimension to add a cube dimension based on it to the selected cube. Multiple cube dimensions can be defined from the same database dimension.

Adding Table Dialog Box

Use the Add Table dialog box in SQL Server Data Tools (SSDT) to add a table while creating a query in the Create Named Query dialog box, Create Polling Query dialog box, or Create Processing Query dialog box. 

rm	Definition
Tables	Use the Tables tab to display and select a table to add.
Views	Use the Views tab to display and select a view to add.
Functions	Use the Functions tab to display and select a function or stored procedure to add.
Synonyms	Use the Synonyms tab to display and select a synonym to add.
Refresh	Click to reload all of the tabs on the dialog box.
Add	Click to add the currently selected item.

You can display the Add Table dialog box in SQL Server Data Tools (SSDT) by clicking Add Table from the Toolbar pane of the Create Named Query dialog box, Create Polling Query dialog box, or Create Processing Query dialog box.

Administering remote partitions

Analysis Services supports both parallel and sequential processing of remote partitions. The master database, where the partitions were defined, coordinates the transactions among all the instances that participate in processing the partitions of a cube. Processing reports are then sent to all instances that processed a partition.

A cube that contains remote partitions can be administered together with its partitions on a single instance of Analysis Services. However, the metadata for the remote partition can be viewed and updated only on the instance of Analysis Services where the partition and its parent cube were defined. The remote partition cannot be viewed or updated on the remote instance of Analysis Services.

Note:

Although databases dedicated to storage of remote partitions are not exposed to schema row sets, applications that use Analysis Management Objects (AMO) can still discover a dedicated database by using the XML for Analysis Discover command.

Any CREATE or DELETE command that is sent directly to a dedicated database by using a TCP or HTTP client will succeed, but the server will return a warning indicating that the action may damage this closely managed database.

Remote Partitions

When partitioning a measure group, you can configure a secondary database on a remote Analysis Services instance as partition storage.

Remote partitions for a cube (called the master database) are stored in a dedicated Analysis Services database on the remote instance of Analysis Services (called the secondary database).

A dedicated secondary database can store remote partitions for one and only one master database, but the master database can use multiple secondary databases, as long as all the secondary databases are on the same remote instance of Analysis Services. Dimensions in a database dedicated to remote partitions are created as linked dimensions.

Before you create a remote partition, the following conditions must be met:

• You must have a second Analysis Services instance and dedicated database to store the partitions. The secondary database is single-purpose; it provides storage of remote partitions for a master database.
• Both server instances must be the same version. Both databases should be the same functional level.
• Both instances must be configured for TCP connections. Analysis Services does not support creation of remote partitions by using the HTTP protocol.
• Firewall settings on both computers must be set to accept outside connections. For information about setting the firewall
• The service account for the instance running the master database must have administrative access to the remote instance of Analysis Services. If the service account changes, you must update permissions on both the server and database.
• You must be an Analysis Services administrator on both computers.
• You must ensure your disaster recovery plan accommodates backup and restore of the remote partitions. Using remote partitions can complicate backup and restore operations. Be sure to test your plan thoroughly to be sure you can restore the necessary data.

Configure remote partitions

Two separate computers that are running an instance of SQL Server Analysis Services are each required to create a remote partition arrangement that designates one computer as the master server and the other computer as the subordinate server.

The following procedure assumes that you have two server instances, with a cube database deployed on the master server. For the purposes of this procedure, the cube database is referred to as db-master. The storage database containing remote partitions is referred to as db-storage.

You will use both SQL Server Management Studio and SQL Server Data Tools (SSDT) to complete this procedure.

Note:

Remote partitions can be merged only with other remote partitions. If you are using a combination of local and remote partitions, an alternative approach is to create new partitions that include the combined data, deleting the partitions you no longer use.

Specify valid server names for cube deployment (in SSDT)

• On the master server: In Solution Explorer, right-click the solution name and select Properties. In the Properties dialog box, click Configuration Properties, then click Deployment, and then click Server and set the master server's name.
• On the subordinate server: In Solution Explorer, right-click the solution name and select Properties. In the Properties dialog box, click Configuration Properties, then click Deployment, and then click Server and set the subordinate server's name. 

Create and deploy a secondary database (in SSDT)

• On the subordinate server: Create a new Analysis Services project for the storage database.
• On the subordinate server: In Solution Explorer, create a new data source pointing to the cube database, db-master. Use the provider Native OLE DB\Microsoft OLE DB Provider for Analysis Services 11.0.
• On the subordinate server: Deploy the solution.

Merging Using XMLA

If partitions have the same aggregation design and structure, you can merge the partition by using the Merge Partitions command in XML for Analysis (XMLA). Merging partitions is an important action to perform when you manage partitions, especially those partitions that contain historical data partitioned by date.

For example, a financial cube may use two partitions:

One partition represents financial data for the current year, using real-time relational OLAP (ROLAP) storage settings for performance.

Another partition contains financial data for previous years, using multidimensional OLAP (MOLAP) storage settings for storage.

Both partitions use different storage settings, but use the same aggregation design. Instead of processing the cube across years of historical data at the end of the year, you can instead use the Merge Partitions command to merge the partition for the current year into the partition for previous years.

This preserves the aggregation data without requiring a potentially time-consuming full processing of the cube.

Specifying Partitions to Merge

When the Merge Partitions command runs, the aggregation data stored in the source partitions specified in the Source property is added to the target partition specified in the Target property.

To be successfully merged, the partitions specified in both the Source and Target must be contained by the same measure group and use the same aggregation design. Otherwise, an error occurs.

The partitions specified in the Source are deleted after the Merge Partitions command is successfully completed.

Note: The Source property can contain more than one partition object reference. However, the Target property cannot.

Merging using SSMS

Before merging partitions, first copy the data filter information (often, the WHERE clause for filters based on SQL queries). Later, after the merge is completed, you should update the Partition Source property of the partition containing the accumulated fact data.

• In Object Explorer, expand the Measure Groups node of the cube containing the partitions that you want to merge, expand Partitions, right-click the partition that is the target or destination of the merge operation. For example, if you are moving quarterly fact data to a partition that stores annual fact data, select the partition that contains the annual fact data.
• Click Merge Partitions to open the Merge Partition <partition name> dialog box.
• Under Source Partitions, select the check box next to each source partition that you want to merge with the target partition, and click OK.
• Right-click the partition containing the accumulated data and select Properties.
• Open the Source property and modify the WHERE clause so that it includes the partition data you just merged. Recall that the Source property is not updated automatically. If you reprocess without first updating the Source, you might not get all of the expected data. 

Source partitions are immediately deleted after the source is merged into the target partition. Refresh the Partitions folder to update its contents after the merge is completed.

Merging Partitions Cube

Partitions can be merged only if they meet all of the following criteria:

• They have the same measure group.
• They have the same structure.
• They must be in a processed state.
• They have the same storage modes.
• They contain identical aggregation designs.
• They share the same string store compatibility level (applies to partitioned distinct count measure groups only).

If the target partition is empty (that is, it has an aggregation design but no aggregations), merge will drop the aggregations for the source partitions. You must run Process Index, Process Full, or Process Default on the partition to build the aggregations.

Remote partitions can be merged only with other remote partitions that are defined with the same remote instance of Analysis Services.

If you are using a combination of local and remote partitions, an alternative approach is to create new partitions that include the combined data, deleting the partitions you no longer use.

To create a partition that is a candidate for future merging, when you create the partition in the Partition Wizard, you can choose to copy the aggregation design from another of the cube's partitions. This ensures that these partitions have the same aggregation design. When they are merged, the aggregations of the source partition are combined with the aggregations in the target partition.

Updating the Source after Merging Partitions:

Partitions are segmented by query, such as the WHERE clause of a SQL query used to process the data, or by a table or named query that provides data to the partition. The Source property on the partition indicates whether the partition is bound to a query or a table.

When you merge partitions, the contents of the partitions are consolidated, but the Source property is not updated to reflect the additional scope of the partition. This means if you subsequently reprocess a partition that retains its original Source, you will get incorrect data from that partition. The partition will erroneously aggregate data at the parent level

Special Consideration for partitions segmented by partition:

In addition to queries, partitions can also be segmented by table or named query. If the source partition and target partition use the same fact table in a data source or data source view, the Source property is valid after merging partitions. It specifies the fact table data that is appropriate to the resulting partition. 

Because the facts that are required for the resulting partition are present in the fact table, no modification to the Source property is necessary.

Partitions using data from multiple fact tables or named queries require additional work. You must manually merge the facts from the fact table of the source partition into the fact table of the target partition.

Alternatively, you can change the source for the merged partition to a named query that returns the contents of two separate fact tables. If this manual step is not performed, the fact table does not contain complete information.

For the same reason, partitions obtaining segmented data from named queries also require updating. The combined partition must now have a named query that returns the combined result set that was previously obtained from the separate named queries.

Merging Using SSMS

Merging Using XMLA

Using Tables Views and Named Queries

If the DSV already organizes facts into individual tables (for example, by year or quarter), you can create partitions based on an individual table, where each partition has its own data source table. 

This is essentially how measure groups are partitioned by default but in the case of multiple partitions, you break the original partition into multiple partitions, and map each new partition to the data source table providing the data.

Views and named queries are functional equivalent to tables, in that all three objects are defined in the DSV and bound to a partition using the Table Binding option in the Partition Source dialog box. You can create a view or named query to generate the data segment needed for each partition.

Note:

When you create mutually exclusive named queries for partitions in a DSV, ensure that the combined data for the partitions includes all data from a measure group that you want to include in the cube. Make sure that you do not leave a default partition based on the entire table for the measure group, or else the query based partitions will overlap the query based on the complete table. 

• Create one or more named queries to use as the partition source.
• The named query must be based on the fact table associated with the measure group. For example, if you are partitioning the FactInternetSales measure group, the named queries in the DSV must specify the FactInternetSales table in the FROM statement.
• In SQL Server Data Tools, in Solution Explorer, double-click the cube to open it in Cube Designer, and then click the Partitions tab.
• Expand the measure group for which are adding partitions.
• Click New Partition to start the Partition Wizard. If you created the named queries using the fact table bound to the measure group, you should see each of the named queries you created in the previous step.
• In Specify Source Information, choose one of the named queries you created in a previous step. If you do not see any named queries, go back to the DSV and check the FROM statement.
• Click Next to accept the default values for each subsequent page.
• On the last page, Completing the Wizard, give the partition a descriptive name.
• Click Finish.
• Repeat the previous steps to create the remaining partitions, choosing a different named query each time to select the next data slice.
• Deploy the solution or process the partition to load the data. Be sure to process all partitions.
• Browse the cube to verify the correct data is returned.

Filter a Fact table for Multiple Partitions

To create multiple partitions, you begin by modifying the Source property of the default partition. By default, a measure group is created using a single partition that is bound to a single table in the DSV. Before you can add more partitions, you must first modify the original partition to contain just a portion of the fact data.

 You can then proceed to create additional partitions for storing the remainder of the data.

Construct your filters such that data is not duplicated among the partitions.

 A partition's filter specifies which data in the fact table is used in the partition. It is important that the filters for all partitions in a cube extract mutually exclusive datasets from the fact table. The same fact data might be double-counted if it appears in multiple partitions.

• In SQL Server Data Tools, in Solution Explorer, double-click the cube to open it in Cube Designer, and then click the Partitions tab.
• Expand the measure group for which are adding partitions. By default, each measure group has one partition, bound to a fact table in the DSV.
• In the Source column, click the browse (. .) button to open the Partition Source dialog box.

• In Binding Type, select Query Binding. The SQL query that selects the data appears automatically.
• In the WHERE clause at the bottom, add a filter that segments data for this partition.
• Examples of WHERE clause syntax include WHERE OrderDateKey >= '20060101' or WHERE OrderDateKey BETWEEN '20051001' AND '20051201'. For other examples
• Notice that the following filters are mutually exclusive within each set:

et 1:	"SaleYear" = 2012 "SaleYear" = 2013
Set 2:	"Continent" = 'NorthAmerica' "Continent" = 'Europe' "Continent" = 'SouthAmerica'
Set 3:	"Country" = 'USA' "Country" = 'Mexico' ("Country" <> 'USA' AND

• Click Check to check for syntax errors, and then click OK.
• Repeat the previous steps to create the remaining partitions, modifying the WHERE clause each time to select the next data slice.
• Deploy the solution or process the partition to load the data. Be sure to process all partitions.
• Browse the cube to verify the correct data is returned.
• After you have a measure group that uses multiple measure groups, you can create additional partitions in SQL Server Management Studio. Under a measure group, right-click the Partitions folder and select New Partitions to start the wizard.

 Instead of filtering data in a partition, you can use the same query to create a name query in the DSV, and then base the partition on the named query

Creating and Maintaining Partition Cube

You can create additional partitions for a measure group to improve processing performance. Having multiple partitions allows you to allocate fact data across a corresponding number of physical data files on local as well as remote servers.

In Analysis Services, partitions can be processed independently and in parallel, giving you more control over processing workloads on the server.

Partitions can be created in SQL Server Data Tools during model design, or after the solution is deployed using SQL Server Management Studio or XMLA. We recommend that you choose one approach only.

 If you alternate between tools, you might find that changes made to a deployed database in SQL Server Management Studio are overwritten when you subsequently redeploy the solution from SQL Server Data Tools.

Notes

Check whether you have either the business intelligence edition or enterprise edition. Standard edition does not support multiple partitions. To check the edition, right-click the server node in SQL Server Management Studio and choose Reports | General. For more information about feature availability,

From the outset, it's important to understand that partitions must share the same aggregation design if you want to merge them later. Partitions can be merged only if they have identical aggregation designs and storage modes.

Explore the data in Data Source View (DSV) to understand the range and depth of the data you are partitioning. For example, if partitioning by date, you can sort on a date column to determine the upper and lower bounds of each partition.

The most important consideration when creating partitions is to segment the data so that there are no duplicate rows. Data must be stored in one, and only one, partition to avoid double counting any rows. As such, it's common to partition by DATE so that you can define clear boundaries between each partition.

You can use either technique to distribute the fact data across multiple partitions. The following techniques can be used to segment the data.

Choose an approach

Technique	Recommendations
Use SQL queries to segment fact data	Partitions can be sourced from SQL queries. During processing, the SQL query is to retrieve the data. The query's WHERE clause provides the filter that segments the data for each partition. Analysis Services generates the query for you, but you must fill in the WHERE clause to properly segment the data. The primary advantage of this approach is the ease with which you can partition data from a single source table. If all of the source data originates from a large fact table, you can build queries that filter that data into discrete partitions, without having to create additional data structures in the Data Source View (DSV). One disadvantage is that using queries will break the binding between the partition and the DSV. If you later update the DSV in the Analysis Services project, such as adding columns to the fact table, you must manually edit the queries for each partition to include the new column. The second approach, discussed next, does not have this disadvantage.
Use tables in the DSV to segment fact data	You can bind a partition to a table, named query, or view in the DSV. As the basis of a partition, all three are functionally equivalent. The entire table, named query, or view provides all of the data to a single partition. Using a table, view, or named query places all of the data selection logic in the DSV, which can be easier to manage and maintain over time. An important advantage to this approach is that table bindings are preserved. If you update the source table later, you do not have to modify the partitions that use it. Secondly, all of the tables, named queries and views exist in a common work space, making updates more convenient than having to open and edit partition queries individually