Creating Analysis Services Cubes - SQL Server Analysis Services 2012 Cube Development - page 114

and select properties to view and modify configuration options). The relevant proper-
ties are as follows:
1. Log\QueryLog\CreateQueryLogTable : Set this property to True unless
you intend to create the relational database table manually. The default value
is False .
2. Log\QueryLog\QueryLogConnectionString : This property sets con-
nection properties for the SQL Server relational database where you intend
to store query logs. By default, logs are not collected and therefore the de-
fault value is blank.
3. Log\QueryLog\QueryLogTableName : This is the name of the relational
table where the query log will be stored. The default value is
OLAPQueryLog .
4. Log\QueryLog\QueryLogSampling : To capture every query request set
this property to 1 . The default value is 10 , which means only one out of every
10 query requests will be logged. Keep in mind that capturing every query
on a very busy SSAS server will result in a large amount of data and could
cause performance overhead.
The term "Query Log" is a bit of a misnomer because if you examine the table, the
most relevant column, called dataset, contains a collection of ones and zeros separ-
ated by occasional commas and not the actual MDX query. The content of the data-
set column is called a subcube and is expressed as a vector of dimension attributes.
For example, 000,000,001,000 indicates that my query requested a subcube, which
included an explicit reference to only one attribute from the third dimension. The oth-
er three dimensions are represented with three zeroes each because the query did
not explicitly reference any of the three attributes included in those dimensions. Per-
haps a more accurate name would be a subcube log, because each MDX query can
request multiple subcubes and the entire cube space can be defined as the collec-
tion of subcubes. Apart from the naming convention, the query log is very useful; as
you will see shortly, subcubes determine the essence of aggregation design.
Usage-based Optimization wizard reads the query log table, reporting the total num-
ber of queries, distinct queries, and users, as well as the average response time. If
you log every subcube request on a busy SSAS server, you could soon have a huge
query log table. So the wizard allows filtering subcubes based on the execution date
and the number of users who requested a particular subcube. Next, the wizard trans-
lates the subcube vectors into attributes and allows you to count the rows in the fact/