Dynamics Community Forum Thread Details

Cube based on customized views

I'm newer on SSAS, i have done same research and i have found the following steps to create a Cube:

1-define data sources (views and tables)

2-create perspective based on those data sources.

3-Use BI wizard to create the cube an edit SSAS project using VS.

I'm wondering to know: if I need to create a new cube based only on customized views, how can we proceed? is there different ways to do it? :

1-Using separate Views (each one is considered as dimension) wich should be related to each other? should use union views?

2-Or we can create only one view wich contain all information?

Thanks.

I don't know that there is any particular recommended way for designing views to be included in a data cube. But I would advise to limit the number of joins used in each view to as few as possible and to include only fields you actually use in the cube.

The general idea is that ssas will have to query the view data and then join all the related views, so you want to pick a design when the query to do so can run quickly. If you are considering several possible view designs, I'd suggest building a cube from each and seeing which one processes the fastest on whatever data you have.

One view per dimension and then one for the measures is the most common pattern, but that doesn't mean you can't try others.

If the set of tables you will query is small one view to join everything tends to work out fine too. But if the set of required joins get larger than say a dozen, processing tends to become slower. That said, if all you need from some dimension is one or two fields, joining those in to the measure group view instead of creating a separate dimension view is probably fine.

A few things to pay attention to though are that the columns needed to join the dimension view to the measure group view need to be in both views.

Also you will likely need to customize the data source view (and cube file) in the ssas project to define those joins between the dimensions and the measure groups. In some cases the project generator can figure out how the dimensions are related, but it often cannot, in which case you need to add that info yourself into the ssas project. It's easy to add though, you just create a table relationship in the data source view and then create a dimension usage in the cube file that links the dimension to the measure group.

The following article covers how to create the perspective after you've defined the views. Perhaps it might be helpful once you get to that point. msdn.microsoft.com/.../cc622026.aspx

Hope this helps,

Lance

Quick Links