smaller cubes on big database

Question

smaller cubes on big database

Anamika

SSCertifiable

Points: 6202
More actions
July 4, 2012 at 1:10 am

#277388

hi,
while creating cubes, can i create a cube with a subset of data from a big database?
To eloborate, while creating the cube itself i want to limit the cube on some where clause.
for example, i want to create a cube only for a particular customer.
Thanks,
Regards,
Ami

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 1

The answer is yes, you can limit what is in the cube. We do that frequently as our DW doesn't serve only the cubes. The datasource view for the cube is a good place for this.

I personally wouldn't create a cube per client, but you can limit to one client for the cube if desired.

Anamika SSCertifiable Points: 6202 More actions · Answer 2

hi,

thanks for your reply. but how can i limit my records in Data Source view?

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 3

Anamika (7/6/2012)
hi,
thanks for your reply. but how can i limit my records in Data Source view?

If you open your DSV in BIDS, right-click on the design surface and select New Named Query...

when you do that, a query building interface opens and you can add your table(s) and create a query to limit rows or columns or both.

There are several fields in my dw tables that I do not want in the cube so I use queries more than tables in my datasource views.

richykong SSCommitted Points: 1502 More actions · Answer 4

I've always tried to build views in SQL for a filtered data set instead of using the named queries. In my opinion, views are easier to manage and maintain.

You could always create a customer dimension and create a SSAS role that only has access to a specific Customers data. Doing it this way allows you to create a separate role for each Customer and also some kind of CEO role that has access to all customers on a single cube.

If you have an extremely large data warehouse, you probably would also want to look into Fact partitioning. Each fact partition is essentially a filtered query from the original Fact table in the DSV.

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 5

richykong (7/19/2012)
I've always tried to build views in SQL for a filtered data set instead of using the named queries. In my opinion, views are easier to manage and maintain.
You could always create a customer dimension and create a SSAS role that only has access to a specific Customers data. Doing it this way allows you to create a separate role for each Customer and also some kind of CEO role that has access to all customers on a single cube.
If you have an extremely large data warehouse, you probably would also want to look into Fact partitioning. Each fact partition is essentially a filtered query from the original Fact table in the DSV.

I respectfully disagree that SQL views are easier to manage and maintain since they are part of the DW database, Actual Objects to backup, etc.

Independent objects in a db are subject to corruption, modification, deletion by all people with access to the database (which would be every developer here) while Named Queries in the dsv limits hands to the BI team and the very few others with SSAS permissions or access to the BIDS solution.

Environments do vary. Here, there are maybe 3 of us that have a shot at hurting the cubes and dsvs but 50 developers that can do anything I can do on the db side. Additionally, our DW views serve a very specific purpose and it is not the cubes.

The bottom line is that both SQL Views and Named Queries in the dsv are very similar so OP can easily try both methods and determine best practice for them.