SQLAuthority News – BI Quiz Question – How to Optimize Cube? – Hints

I earlier wrote about SQL BI Quiz over here.

The details of the quiz is as following:

Working with huge data is very common when it is about Data Warehousing. It is necessary to create Cubes on the data to make it meaningful and consumable. There are cases when retrieving the data from cube takes lots of the time.

Let us assume that your cube is returning you data very quickly. Suddenly on one day it is returning the data very slowly. What are the three things will you to diagnose this. After diagnose what you will do to resolve performance issue.

Participate in my question over here

Here is a couple of hints what I am looking for in answer:

  • How to reach to root of slow performance?
  • Is hardware causing the problem or something else?
  • Is slowness is due to how cube is build and its granularity?
  • Is underlying tables require maintenance?
  • Is there is chance to refractor the process?
  • Are there any tool which can help diagnosis the slowness of the cube?

It is not necessary to answer all the question – but something to start with.
Reference: Pinal Dave (http://blog.SQLAuthority.com)

4 thoughts on “SQLAuthority News – BI Quiz Question – How to Optimize Cube? – Hints

  1. Hi Sir,

    my sql server 2008 has been currpt then i copyed all mdf and ldf .whenever I Attached My MDF to another instance then goes error Can not find server Certificate with thumprint .I
    Do not have Master key backup and Symetric Key backup and also forgot password .
    If I replaced Master database after that change error “Please Create master key or open Session before perfome this task ” so please help me It is very important database

    Its very urgent ………..

    Like

  2. Pingback: SQL SERVER – BI Quiz Hint – Performance Tuning Cubes – Hints Journey to SQLAuthority

  3. The top 2 areas to start cube diagnose for perf issues would be as follows:
    (assuming that you have already checked the user load, reporting layer, and any other intermendiate layer and now you are sure that reports / results are slow due to cube):

    1. Check storage engine (aggregations hits or cube partition hits or fact table hits)
    2. Check query processor (query is formula intensive, many calculations, lots of memory used)

    Coming to the specific questions you asked above:

    Q: How to reach to root of slow performance?
    A: Follow the cube perf diagnosis manually + w/ help of SSAS Profiler:

    1. Storage engine – This involves checking/improving the cube structure
    1.1 Check if cube dimensions are optimized
    1.2 Check if your query is hitting the aggregation store
    1.3 Check if cube partitions are optimized for the query patterns (which are in use)

    2. Query processor – this involves checking/improving the MDX queries
    2.1 Check if MDX queries are optimized
    2.2 Check if cache warming is required for specific long running queries
    2.3 Check if queries are using lots of memory (RAM) – Memory intensive, lots of data required to perform aggregations/calculations on the fly
    2.4 Check if queries are using lots of processor power (CPU cycles) – CPU intesive, lots of calculations
    2.5 Check if there is lots of disk activity going on during query execution – IO intensive

    By performing above steps you will reach to the root/closer to the problem.

    Q; Is hardware causing the problem or something else?
    A: You will get to know after performing above diagnosis based on whether query is Memory intensive, CPU intensive or IO intesive you can accordingly sacle-up

    Q: Is slowness is due to how cube is build and its granularity?
    A: You will get to know after steps mentioned in section#1 above

    Q:Is underlying tables require maintenance?
    A: Might be required, when queries are hitting fact tables for data retrieval and on the fly aggregations , especially in case of ROLAP partitions

    Q: Is there is chance to refractor the process?
    A: Yes, there are tools available to see the complete MDX query map (internal working) and also SSAS profiler is helpful

    Q: Are there any tool which can help diagnosis the slowness of the cube?
    A: SSAS Profiler

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s