I'm completely new to SQL Server Analysis Servers (SSAS) so you'll have to excuse my ignorance. I've been running through the MSDN SSAS tutorial get a handle on how to use SSAS.
We have SQL Server Reporting Services (SSRS) reports configured to display user login history over a given time period. We have a table with
user id and
login time. This is joined to the
user management table to display details about the user on the report. However, this is not true point-in-time reporting as the user's details, their surname for example, may have changed over the given time period. We are not using SSAS at the moment. We do have other, more complicated, requirements but this is a good example to begin with.
I want to enable SQL Server 2008 Change Data Capture on the user management table. This data would then be pulled into the reporting database and transformed into a data warehouse for faster searching.
Firstly, is SSAS the way to go? I could write a SQL query to get the latest user record for each login, however it's a little fiddly, especially in queries that have multiple joins, and I don't want to reinvent the wheel if some technology already does that, possibly faster.
If SSAS is indeed the way to go how would I go about achieving this? Has anyone out there already implemented this solution and what did you do?
I've been looking for books or any forum posts and can't find any information on this specific requirement, including emailing people at Kimball. I really need an idiots guide.