reinsurance data model

17.1. reinsurance data model

Objective #

To design and implement a database schema optimized for efficient data storage and organization. This schema will act as the backbone for generating reports and creating interactive dashboards with any Business Intelligence (BI) tool. It should be tailored to align with the functionalities of the SICS system, ensuring that all essential data points are readily accessible for analytical purposes.

ETL Process (Extract, Transform, Load) #

Develop an ETL pipeline to move data from the SICS system into the database and ensure it is transformed for analytics.

Actions #

  • Define how data will be extracted from SICS - whether through direct database queries, API integrations, or file imports.
  • Implement the transformation logic to convert raw data into a format suitable for reporting. This may include aggregation, cleaning, and formatting of the data.
  • Load the data into the schema, ensuring that historical data is retained and that updates to current data occur in a timely manner.
  • Use schema designs to optimize for fast querying and efficient data retrieval for BI tools.
  • Define views or materialized views to simplify complex queries and make it easier for BI tools to pull the data they need.
  • Ensure that appropriate indexes and partitions are applied to key tables and columns used in reporting.

Integration with BI Tools #

Objective #

Ensure seamless integration between the database schema and selected BI tools.

Actions #

  • Provide the BI tool with access to the database, either through direct queries, ODBC connections, or data connectors.
  • Define data models or datasets in the BI tool based on the schema design.
  • Ensure that visualizations such as charts, tables, and graphs are connected to the schema and can pull real-time data for dashboards and reports.

REINSURANCE DATA MODEL #

The Reinsurance Data Model (RDM) is a specialized database schema developed to manage tables, views, and procedures required for creating reports and dashboards for SICS Reinsurance clients. RDM views process and aggregate data from one or more base SICS tables, acting as the source for populating RDM tables. The UPDATE_RDM_TABLES.sql procedure facilitates the transfer of incremental or updated data from the views into the RDM tables. Once the data is stored in the RDM tables, it can be accessed by Business Intelligence (BI) tools to generate analytics and insights based on specific business requirements.

RDM_1.png

PREPARING DATABASE FOR REINSURANCEDATA MODEL #

To prepare the database for Reinsurance Data Model (RDM) using Assure Reinsurance (SICS) SysAdmin.
Steps:

  1. Logon to SysAdmin Access the SysAdmin interface.
  2. Navigate to the Manage Table Section: Navigate to the Manage Table Section and the RDM Reporting
  3. Select the Option:
  • Create Table: Select this option to create empty RDM tables.

RDM_2.png

NOTE - Constraints will be created for the RDM Tables while creating the RDM Tables.

  • Create Views: Select this option to create empty RDM Views

RDM_3.png

  • Create Update RDM Tables Procedure - Select this option to create Update_RDM_Tables procedure

RDM_4.png

  • Drop RDM Tables Constraints

Note - Make sure you drop all the RDM table Constraints from SYS Administration by selecting Drop Constraints option as shown below

RDM_5.png

Note - Without dropping RDM table Constraints the Update_RDM_Tables procedure will not get executed successfully.

Creating RDM Database Objects Manually #

  1. Same Schema/Database: If the RDM is being created within the same schema or database that already contains the SICS base tables, no additional configuration is required. Simply create the RDM table views and execute the UPDATE_RDM_TABLE scripts. Refer to step 3 and subsequent steps for detailed instructions.
  2. Different Schema: If you intend to create the RDM tables, views, and procedures in a different schema, ensure that the SCHEMA_NAME is prefixed to all SICS table references.
  3. Creating RDM Tables: To create the RDM tables in the database, execute the T_RDM_<TABLE_NAME>.SQL given under the folder CREATE_RDM_TABLE after establishing a connection to the database.
  4. Creating RDM Views: To create the RDM database views, execute the T_RDM_<VIEW_NAME>.SQL given under the folder CREATE_RDM_VIEWS after establishing a connection to the database.
  5. Create UPDATE_RDM_TABLES Procedure: To create the RDM database views, execute the UPDATE_RDM_TABLE.SQL given under the folder CREATE_RDM_VIEWS after establishing a connection to the database.
  6. Drop Constraits on RDM Tables - Make sure you drop all the RDM Tables Constraints using RDMReportingConstraintsScript-drop.sql before executing UPDATE_RDM_TABLES procedure.
  7. Scheduling Incremental Data Loads: After creating the RDM tables and views, execute the UPDATE_RDM_TABLES procedure twice and record the completion times. Use this information to schedule the procedure for regular execution.

Connecting BI Tool for reporting purpose #

For the reference purpose shown below the steps to connect Power BI tool to SQL Server database. You can use any BI tool of your choice.

For Microsoft SQL Server

  • Open Power BI report and in “Home” Tab click on SQL Server or go to “Get Data” option to see SQL server Data Source.
  • It will open below window and then please enter server name and data base name.

RDM_19.png

  • Select Import mode for storing table data in power BI’s memory. Reports will fetch data from cache. Select DirectQuery mode to fetch live data from Database.
  • Click on Ok.
  • Now, you can see list of tables in that database and can select required tables and click on Load to see those tables inside Power BI Report.

RDM_7.png

To Change Data Source in Power BI #

This section describes the steps to follow to change data source of an existing Power BI report.
There are 2 methods to change the data source.

a) From Data Source Settings

  • From Data Source Settings
  • Open Existing Power BI report and click on dropdown arrow at Transform data option.

RDM_8.png

  • Click on Data Source Settings and it will open below window.

RDM_9.png

  • Click on Change Source then it will allow us to change server name and Data base name.

RDM_10.png

  • Click on Ok and close that window.

  • Now, click on Apply Changes or try to refresh the data then it will prompt us for credentials.

RDM_11.png

  • Select Database and provide required database user Id and Password to login to that SQL server.
  • Now, report will load the data into tables from the connected database.

b) From Transform Data

  • Click on Transform Data.

RDM_12.png

  • Right Click on a table and select Advanced Editor.

RDM_13.png

  • From here we can change Data base name and Schema name.

RDM_14.png

  • Click on Done and Refresh the table.

RDM_15.png

Note - Repeat the above steps for all existing tables to change the data source.

  • If there is any DirectQuery related queries are there in the report, then it will ask for permission to change data source.

RDM_16.png

  • Click on Edit Permission and Run the query.

RDM_18.png

Note - After performing these steps click on Close & Apply to apply these data source changes and to refresh the table data.