How to enable SSAS Usage-Based Optimization in SSAS

By
roopesh.valluru
-
88
30038

Friends,

Usage-Based Optimization is a fantastic feature in SSAS to improve the most frequently used MDX queries of any cube database. This feature is used to design aggregations based on actual user queries using Business Intelligence Development Studio. But when ever you try to design Usage-Based Optimization you might have seen all the options disabled. Why is it so and how to enable it ? For the answers to these questions, continue reading the post.

This uses the technique of creating aggregations on FREQUENTLY USED queries in the given time interval. Options in Usage-Based Optimization wizard will be disabled because the queries executed on the CUBE database will not be stored anywhere by default and you c an see the same in below given pic.

 

When there is no queries stored then this concept fails and hence all the options will be disabled. We can change the settings in order to enable the STORAGE of queries that are executing on cube and hence we can enable the options in Usage-Based Optimization wizard. PFB the steps involved in enabling the storage of queries.

  • Connect to SSAS using SSMS (Sql Server Management Studio).
  • Right click on the server and select properties as shown below.

  • set CreateQueryLogTable to true.
  • Set the value of QueryLogConnectionString to any database where you wish to store your queries.
  • Set the value of QueryLogSampling to the appropriate value for your environment (the default value of 10 means every 10th query will be logged). The lower the value of this property, the higher the overhead of query logging will be on the system.
  • Set the value of QueryLogTableName property to any TABLE NAME of your wish. 
  • Click OK.

 

Everything set and now try to execute some queries on the cube and check the table(given in the above steps) in the database(mentioned in the above steps). It should contain the queries you executed on the cube database.

Once the queries started logging, we are all set to configure Usage-Based Optimization for the queries. In the below pic you can see all the options are enabled.

These are the pre requisites to configure Usage-Based Optimization and check the next post to see the steps involved in configuring the same.

Thanks,
Roopesh Babu V

RELATED ARTICLESMORE FROM AUTHOR

88 COMMENTS

  87. Our Suitcase sets are designed to “nest” comfortably inside each other so you can own that beautiful set of matching luggage without any storage hassles Hard plastic is durable and easy to clean Choosing the best makeup case depends really on a few things: (a) How much makeup you have, (2) where you’re going, and (3) what you’re hoping to store, which brings us to… We’ve successfully received your request. 100% Italian design, 100% made in Cambodia. Why We Love It: Available in 13 different styles and four sizes, this case also boasts numerous compartments making it a “variety is the spice of life”-type of traveler’s ideal choice. The robust shape and size fits perfectly into a car for one-week getaways closer to home. Interested by this product?
    http://linkcentre.com/profile/elandeni1985
    Fluffy brush delivers volume Fluffy brush delivers volume Worked at Sephora for many years and still swear by and exclusively use Covergirl Lash Blast (orange tube) (and Benefit Rollerlash, but that is a bit more pricey). Waterproof Lash Blast in brown is the jam to my jelly рџЌЇрџђќ Especially if you have straight and stubborn lashes that won’t normally hold a curl! For an even thinner brush option, Innisfree’s Skinny Waterproof Microcara Mascara is one of Shackleton’s favorites. She says it has “the price of a drugstore mascara with the pack of a professional brand,” thanks to its super-fine brush. “Without a doubt, this one stands out for getting even the tiniest lashes noticed,” she adds. Finding a mascara that doesn’t smudge doesn’t have to be expensive. There are cheap drugstore mascara products that do just as good a job in the heat and humidity. Plus, they won’t fail to give you length and definition. Whenever I look for a mascara, I make sure it does three main things: It makes my lashes look full, it doesn’t dry up or clump, and it has to be smudge proof.

  88. A primeira versão do bingo surgiu em 1530, na Itália. Na época, os membros dos conselhos políticos de Gênova eram decididos através de sorteio. A mecânica era muito simples. Os nomes dos candidatos eram inseridos em bolinhas que, em seguida, seriam retiradas aleatoriamente de uma urna. Soa familiar? Ainda no século XVI, esse sistema passou a fazer bastante sucesso. Tanto que, não muito tempo depois, foi criada uma loteria semanal com a mesma mecânica. A Lo Gioco Del Lotto D’Itália, como foi chamada, pode ser considerada a primeira utilização lúdica desse método de sorteio. E, dessa forma, a mãe do bingo. Nota do consumidor Jogar ao bingo online da Playspace é muito simples e divertido. A primeira coisa que deve fazer é escolher a sala de que gosta mais (Madrid, Barcelona, México, Rio de Janeiro, etc.), em cada uma das quais a quantidade de cartões e apostas é diferente. De seguida, deverá comprar os cartões que desejar e, a partir daqui, pôr-se confortável e desfrutar da partida.
    http://malpkellsofal1973.cavandoragh.org/clique
    Estas promoções podem ser de carácter temporário ou definitivo, estando estas últimas associadas a dias da semana, por exemplo, nos quais é possível obter saldo extra a jogar em determinadas slots. Já nas promoções temporárias inserem-se, entre outros, os torneios de slots (ou de outros jogos como roletas e blackjack), que geralmente funcionam à base de acumulação de pontos para subir numa tabela de prémios e ganhar bónus mais competitivos no final do torneio. Se você é um apostador obstinado que quer jogar e ganhar ainda mais, 20 rodadas grátis podem ser pouco para você. Nesse caso, vai ficar feliz em saber que o National Casino possui muitos outros bônus. Um dos mais famosos é o seu Bônus de Boas-vindas de 100% e 100 rodadas grátis. Para coletar esse bônus, basta criar uma conta e fazer um depósito mínimo de R$ 80. Como o valor máximo desse bônus de depósito é R$ 600, os jogadores vão poder curtir o National Casino por muito tempo com esse bônus. Isso sem falar das 100 rodadas grátis, que também podem render grandes ganhos.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

three + two =