SQL Server Performance

Building cubes

Discussion in 'Analysis Services/Data Warehousing' started by jorge27, Sep 29, 2005.

  1. jorge27 New Member

    Hi guys,

    I do not have a lot of experience working with Analysis Services and I would like to know if it is possible to build cubes from views instead of tables, if affirmative is there any performance issue or problems using views?
    I also want to know if it is possible to set filters when processing the cubes, for example: if my fact table is T1, and it has a field F1, I only want the cube to contain those values where F1='Y'
    I am using Analysis Services of SQL Server 2000.

    Thanks
    Jorge
  2. ranjitjain New Member

    Hi Jorge,
    Yes it is possible to create a cube from Views.
    Performance of cube depends on design and data.
    Nothing to do with table/View.
    As per applying filters it's better to be done at SQL end and limit the rows in view where F1='Y'
    and then process the cube which will load only those data which are in view.
  3. RGKN New Member

    Indeed it is possible. Do be careful though, not to use select *, because when you change the data structure of the fact table you will need to refresh the views. However if you explicitly name the fields when you create the view I don't think you have that problem. Good practice dictates that you would never have dreamt of using "SELECT * " anyway. Also make sure you have an index on F1.

    If you are using MOLAP then the performance impact will largely be in processing the cubes.

    Regards,

    Robert.

  4. jorge27 New Member

    Thanks for your valuable posts. I will take into account your recommendations.
    By the way, I am using JPivot to render the data to the user, do you have any experience on this tag library? I would appreciate some feedback from anyone who has used it, and know if it gives good performance or has problems or bugs that do not recommend its use. Particularly, I find it a little bit slow, even working with not very large cubes, I am afraid its performance will be unacceptable when working with large cubes.

    Thanks & Regards,
    Jorge
  5. satya Moderator

    Jpivot is an opensource program and using them in SQL Server analysis has to be tested completely before using them on the production. Probably you may checkhttp://jpivot.sourceforge.net/ (vendor) for any recommendations on large cubes.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page