Partitions
-They can be done only for fact tables/measure groups.
-A partition stores part of the data for a measure group.
-Each partition can pull its data from the same table or from separate table as long as the structure matches.
-Each partition can have its own aggregation and storage scheme.
-Most commonly, you will do a partition on time basis.
-Partitions can speed processing because the engine may have to process only a small subset of data. By default SSAS will make a partition for each measure group defined within the cube structure. (table binding)
You can store the partition of a single measure group at different storage locations.
By default every partition is table binded.
When you are performing any partition, you will make it query binded from table binding.
The following query shows the partitioning of factinternetsales based on the OrderDates.
Internet Sales 2005
SELECT * FROM dbo.FactInternetSales
Where orderdatekey <= 20051231
Internet sales 2006
Select * from dbo.factinternetsales
where orderdate >= 20060101 and orderdatekey <= 20061231
Merging Partitions
Merging combines multiple partitions into one
There are certain criteria that must be met
1. They should be in the same cube
2. They should have same structure
3. they should have the same storage modes
4. they should contain identical aggregation designs
Aggregations
(Set Count Aggregations and Usage Based Optimization)
Aggregations are higher level summaries of the data. Aggregations are pre calculated summaries of data. Specifically, an aggregation contains the summarized values of all measures in a measure group by a combination of different attributes(dimensions).
Aggregations are most useful for speeding up queries by returning pre calculated values instead of figuring them out at runtime.
You cannot control the types of aggregation creation, you can only have the control to define the percent of possible combinations it has to take.
–You can create only 1 aggregation to any measure group or partition.
–Aggregations: Usage of attributes.
Types
Default: use default rules based on the dimension and attribute types to create aggregations.
Full- use it in all combinations/aggregations (These setting should be avoided in cases where an attribute contains a large number of members)
None-Don’t use it in any aggregation
Unrestricted- attribute is evaluated to see if it should be placed in any aggregation.
Aggregation counts: The counts are used to help the wizard to estimate aggregations based on expected usage patterns.
Usage Based Optimization
An aggregation contains the summarized values of all measures in a measure group by a combination of different attributes/dimensions. At design time, you can use the aggregation Design wizard to define aggregations based on your dimensional design and data statistics. After the cube is in production and representative query statistics are available, you should consider running the usage-based optimization wizard to fine tune the aggregation design based on the actual queries submitted to the server.
Properties to set in order to get the user queries submitted for usage based aggregation.
CreateQuerylogtable: Set to True to allow the AS to create a table to log the queries.
QueryLogSampling: The frequency for query sampling. By default, every tenth query is logged.
Query log connection string: Connection string specifying the server and database to be used to log the queries.
QueryLogTableName: Name of the table in which you log the queries that are run against the cube.
The server does not log the actual MDX queries. Instead, it logs certain query information, which includes that dimensions requested by the query and the attributes that the server used to satisfy the query. The server logs these statistics in the query log table. The most important column for the Usage-Based optimization wizard is the dataset column, which captures the attributes used to resolve the query.
