December 12, 2017 at 6:29 am
Hi Everyone!
I'm a long time SQL-developer / DBA, that has recently inherited a few instances of SSAS, which I have never worked with before. The basics of SSAS I have learned myself, but now I have reached a problem which I can't figure out the correct way of solving. I'm hoping to get some help pointing me in the right direction.
What we have:
We have a larger cube containing all customers, invoices, contracts, daily measurements, etc. There is a lot of data for the last 20 years, but everything is working just fine.
What we want:
For a small set of customers, we would like to have a new cube, with the same data, but instead of daily measurement, we want measurements for every minute (which we have staged).
Problem:
We can't change all the measurement data to minute precision since it will make the processing time way to long. Making a new dimension with the more detailed data is not hard, but we also want this more detailed cube to be rather small a quick, not having any unnecessary data not related to these customers in the cube.
I can't find a way to filter a dimension and add that filtered dimension to a new cube. It also feels wrong to duplicate all of dimensions shared between the cubes, having one filtered version and one that is not. There will be a lot of redundant data on disc and we will have to process a lot of information twice. There is probably a better way to do this, but as mentioned earlier I'm just the DBA that got stuck with this when the rest of the team jumped ship.
Hopefully someone here can give me some hints on how to think.
December 12, 2017 at 8:23 am
Creating a filtered view of a cube is possible through what is called a perspective...something that is similar to a database view. However, in your scenario I don't think a perspective is the way to go because you also want to change the grain of the data while not inflating the current cube's size.
In my opinion, you have two options here:
1. Add a new dimension and filter the data source to only include the new customer's data. Then add new fact tables in your data source view, also filtered for this customer and build a new cube with these new tables. The benefit here is that you will be able to reuse other dimensions that already exist.
2. Create a completely new SSAS database for this specific use-case, filtering the source data to only include what you need. There will be some duplication of effort and maintenance here, but you will have the flexibility to contain the size and scope based on this specific use-case.
December 14, 2017 at 11:55 pm
Thanks for your help. I Think that solution number one will be the far better one in this particular case.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply