SQL Server Performance Forum – Threads Archive
Building cubesHi 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
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.
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.
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,
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
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.