I’ve recently joined an organisation that has previously carried out their MI reporting using SSRS by executing stored procedures which used parameters in SQL Server Management Studio and we are keen to explore whether Looker can be configured in a similar fashion to allow reporting to be “dynamic”.
My understanding is that a limitation of Looker is that it must be configured to look at models which are essentially static (or updated at specified intervals) and therefore the same applies to views too.
Is there a way to configure a “live” connection to a SQL Server Management Studio database?
Or must reporting be configured to take all data across in a looker view and then use downstream filtering etc in the visualisation stage to refine as required?
To give you an idea of where I’m going with this I’m trying to consider a future scenario whereby we can configure 15 minute refreshes to visualisations for end users to have running on their machine or wallboards throughout the working day…
Any help/advice would be greatly appreciated
This may require additional discussion to fit your exact use case but while Looker model definitions are defined and then queried on top of, when the actual query is being sent to the database, it is going to return the most live data right from the database itself. The only time this would not necessarily be true is if the query was returning from cache which can be configured on the model or explore level using the persist_for parameter. Cache defaults to five minutes so if you do not query within 5 minutes of the last run, you are going to get the live data from the database itself.
Its also possible to configure dynamic filters on the explores themselves to control what data is returned by an explore. Or as you said you can set the filters in the Look itself, which will control the data returned at query time.
The main takeaway here is that while LookML allows you to construct a model of your data structure, the relationships at play, and the fields in your tables, the querying will be done at the explore level when the user goes to run the query so it can definitely be set up to dynamically query the data and return data as live as it is in the db itself. Please let me know if you have any additional questions here.