Now we will use a small ETL process to feed our data model. Need for more DB permissions: Now we need to have CREATE and EXECUTE permissionįorth and last scenario: Accessing production tables with the data already transformed.Medium refreshing times: We may not see a lot of improvement on the refreshing times compared to the previous scenario.Required database knowledge: Like before, we will need some database knowledge.Shared dataset and transformations: We can share SPs or views and the maintain only one process even if we can't share the data model.Same from the previous scenario: The process is quite similar, thus, similar advantages.It's good to mention that we may not have the required permission on the databases and we'll need to go to other team inside our organization to create the SPs or views. The maintenance is now centralized in the data layer, while the application only receive transformed data. We will isolate the transformations from the application layer. The main goal here is to use the SPs or views among all the reports even when we can't share the data model. Now we want the same query as scenario 2 for the raw data, but we put the query inside a stored procedure or a view. Third scenario: Stored procedures or views that manage the transformations We must maintain every report individually if we can't share the data model Several reports maintenance: This issue is not solved yet. Medium refreshing times: We are yet transforming all the data every time.Required database knowledge: Database queries are not so intuitive nor the interface so friendly.Changes on the source structure will not affect the process: The integration process will take care of the changes on the source’s structure.Better performance: We'll reduce the refresh time and server impact.Few steps: We can do almost all the transformations in a single query.When the report is quite big, we will improve the refreshing times and even the report size thanks to the database engine. In many cases, one simple query may be enough, in some others we will need to create a couple of subqueries. This query will access the raw data and transform it into the desired result set. The idea with this scenario is to develop a single query for each dataset. From now on it will be necessary to have the data integrated into a database. Here we will use the strength of SQL Server. The transformations here will be minimal, or even nonexistent. Power query will not be the do everything. Second scenario: Accessing raw data in databases through queries
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |