May 17, 2017 at 7:51 am
Hi all
We've got a few cubes set up from DIM/FACT tables that are loaded on an incremental basis (i.e. we grab any new data we haven't loaded, process it through a staging database and the add it to the relevant FACT/DIM tables).
What we want to do is take that latest data and bring the cubes up to date.
We've tried various options in cube processing, but only "Process Full" seems to bring in the new data.
The down side is that it seems to clear the data from the cube and start again (which takes a while).
Is there a way to get SSAS to just process any new data it doesn't already know about (this will reduce the processing time considerably)?
On a side note, is there a way to programatically get a list of users and the roles they've been assigned to (and potentially add them)?
The reason I'm trying to get this is to enable me to add/remove users from roles using an Access database (that does other things and gives a nicer GUI).
Any help on either of the above (especially the first question) would be greatly appreciated.
May 17, 2017 at 10:34 am
richardmgreen1 - Wednesday, May 17, 2017 7:51 AMHi allWe've got a few cubes set up from DIM/FACT tables that are loaded on an incremental basis (i.e. we grab any new data we haven't loaded, process it through a staging database and the add it to the relevant FACT/DIM tables).
What we want to do is take that latest data and bring the cubes up to date.
We've tried various options in cube processing, but only "Process Full" seems to bring in the new data.
The down side is that it seems to clear the data from the cube and start again (which takes a while).Is there a way to get SSAS to just process any new data it doesn't already know about (this will reduce the processing time considerably)?
On a side note, is there a way to programatically get a list of users and the roles they've been assigned to (and potentially add them)?
The reason I'm trying to get this is to enable me to add/remove users from roles using an Access database (that does other things and gives a nicer GUI).Any help on either of the above (especially the first question) would be greatly appreciated.
Assuming that you have Enterprise Edition for anything older than SQL Server 2016, you could create partitions in your cube and only process the latest partitions.
To answer your side note question: Yes, .NET contains classes that allow you to access Analysis Services Objects and I am sure you will be able to add roles/users by using it. From what I recall it is called AMO (Analysis Management Objects). You could also script an SSAS role to see what SSAS XMLA query is being executed to create that role...which would make it possible for you to dynamically build such a query for execution. I wouldn't necessarily be a supporter of your plan to use Access for this, but then again it is not my environment and I don't have to deal with the possible consequences.
May 18, 2017 at 2:05 am
We've got Business Intelligence edition on 2012 but the data can span several months (although we could partition it by year).
I'll see what we can do with partitions.
Thanks for that.
As for the AMO, I'll see what Access can handle (may have to have a rethink on that bit).
August 14, 2017 at 10:24 am
Process Add will allow you to put in a query so you can just capture the data you need to process on a daily basis.
If you script out the XML for this you can run this automatically.
Nat
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply