How to schedule an SSAS cube refresh only for new facts or updated dimensions?

Question

Having built a few "test" datacubes through using VS2017, my team are now ready to start working with them in a more production like manner. As such there are a few basic tasks that we need to implement, but we are struggling to find useful resources for.

1. How can we do a monthly refresh of the cube without regenerating all of our dimensions and fact tables?
2. Does VS2017 recognise/honour Slowly Changing Dimensions if we implement them in our Dimension design?

To have a guess at this:

In our ETL databases (bearing in mind we're using VS2017) we need to:

For the Tables that are used in the DataSourceView, that will ultimately become the Dimensions in the cube:

• Create "current" snapshots of our dimensions based on the raw source databases; i.e. what does the Customer dimension look like now?
• Compare this with the slowly changing dimension table as held in the ETL from our last processing run.
• Make the necessary row inserts and update the audit fields of any old entries.

For the Fact Tables:

• For the period since the last refresh add any additional entries to the tables. This should use the updated Dimensions.

When we refresh the datacube on the AnalysisServer what will this do? Presumably the Dimensions tables are refreshed in their entirety as they are usually relatively small; but will the Fact tables refresh completely or just from the last place they were updated.

Apologies for the basic nature of this question, but we've moved beyond the idealised tutorial stage and are now wallowing in an abyss of jargon and our own ignorance :-(

Answer 1

1. How can we do a monthly refresh of the cube without regenerating all of our dimensions and fact tables?

You need to implement an incremental loading inside your ETL logic. You can choose between two types of incremental loading:

• Insert & Update only: You can use Lookup Component (IncInsertUpdate)
• Insert & Update & Delete: You'll have to implement a bit more complex logic (IncInsertUpdateDelete)

2. Does VS2017 recognise/honour Slowly Changing Dimensions if we implement them in our Dimension design?

Yes, there is Slowly Changing Dimension Component that you can use to handle SCDs.