The first step is to analyze the existing MS Access setup, understanding its structure, limitations, and how it manages data.
This phase identifies performance bottlenecks, traceability issues, and other operational inefficiencies.
Based on the assessment, the SQL database is designed to meet the specific needs of the organization.
The new architecture is tailored to accommodate larger volumes of data, support complex queries, and ensure scalability.
Key features include:
Optimized table structures with proper normalization to reduce redundancy.
Indexing and partitioning to enhance query performance.
Implementation of robust security measures to protect sensitive data.
The next step is migrating the processes 1-by-1 from the MS Access program to the new SQL/Lobster environment.
This involves:
Detailed analysis of the current process in Access.
Process transformation to ensure it fits the new schema and adheres to SQL standards.
Transfering process into the SQL database while maintaining data integrity and minimizing downtime.
Lobster Data is introduced to facilitate efficient data exchange and real-time synchronization across various systems.
Its flexible ETL (Extract, Transform, Load) capabilities streamline the movement of data between SQL and other systems, improving interoperability. This integration enables faster, automated data processing, reducing manual interventions and potential errors.
Lobster Pro is employed to visualise business processes, which were previously manual or handled inefficiently by MS Access.
Through Lobster Pro, workflows are digitalized, reducing processing times, improving accuracy, and enabling real-time tracking of every step within the process.
After migration and integration, thorough testing is conducted to ensure that the new system functions as expected. This includes:
Performance testing to verify the speed enhancements.
Validating the accuracy of data migration.
Ensuring the Lobster Data and Lobster Pro integrations work seamlessly with the SQL database.
The new SQL-based system, combined with Lobster Data's optimized data handling and Lobster Pro’s visualisation, offers a significant speed increase compared to MS Access. SQL handles larger datasets and complex queries more efficiently, reducing processing times, enabling faster reporting, and supporting real-time decision-making.
One of the critical advantages of the new system is enhanced traceability. SQL databases provide comprehensive logging and audit trails, while Lobster Data ensures traceability across the entire data lifecycle. Lobster Pro further enhances this by documenting process flows, enabling organizations to track every action, from data entry to final reporting, in real time.
Once the system is fully tested, the new solution is deployed across the organization.
Staff training is conducted to ensure smooth adoption, focusing on using the Lobster Pro's interface, and learning the revised processes in Lobster Pro.
By transitioning from an MS Access program to a custom-built SQL database with Lobster Data and Lobster Pro integration, the organization experiences significant improvements in speed, scalability, and traceability. The result is a modern, efficient, and robust data management system tailored to future growth and operational efficiency.
Objective Definition: Clearly define the project’s goals, which include replacing Dynamics 365 Business Central with a simplified ERP system, aligning the company with the rest of the group, and improving communication and collaboration.
Stakeholder Identification: Identify all key stakeholders, including decision-makers, department heads, and IT teams from the company and the wider group. Engage early with the group’s companies to ensure alignment and understand their ERP system.
Project Charter: Create a formal project charter that outlines the scope, timeline, resources, and high-level risks. The charter should also establish the project team, including a project manager, ERP consultants, and key users from various departments.
Needs Assessment: Conduct workshops with key users to gather detailed functional and technical requirements for the new ERP system. Focus on identifying which Dynamics 365 functionalities are essential and how the new system can simplify processes.
Group Alignment: Collaborate with the group companies to ensure the new ERP system supports shared business processes, data structures, and reporting standards.
Gap Analysis: Compare the capabilities of the new ERP system with the existing Dynamics 365 system and identify any gaps that need to be addressed, whether through system customization, process changes, or additional tools.
Evaluate ERP Systems: Research and evaluate ERP systems that are smaller, easier to use, and meet the group's standard. Consider factors like ease of implementation, user interface, flexibility, integration capabilities, and total cost of ownership.
Select the ERP Vendor: Once the best system is identified, select a vendor and begin negotiations. Ensure the vendor offers strong support and training, especially if your team will rely on group-wide support during and after implementation.
Detailed Project Plan: Develop a comprehensive project plan that includes milestones, deadlines, and deliverables. The timeline should account for system configuration, data migration, testing, training, and go-live.
Risk Management Plan: Identify potential risks, such as data loss during migration or disruptions to business operations, and develop mitigation strategies.
Resource Allocation: Allocate resources, both internal and external. This includes project team members, IT personnel, and external consultants for ERP configuration and data migration.
System Setup: Configure the new ERP system based on the previously gathered requirements. Ensure that the system’s workflows, permissions, and interfaces are tailored to the company’s needs while maintaining alignment with the group’s processes.
Integration: Ensure seamless integration with existing systems such as CRM, inventory management, and financial reporting tools used across the group.
Data Mapping: Map the data from Dynamics 365 Business Central to the new ERP system. Define how customer, vendor, financial, and operational data will be transferred, ensuring that data formats are compatible.
Data Cleansing: Clean up old or irrelevant data to avoid transferring unnecessary information. This helps optimize system performance and prevents clutter in the new ERP system.
Migration Testing: Conduct a thorough test migration in a sandbox environment to identify any issues and refine the process before the final migration.
Training Plan: Develop a detailed training plan for employees, focusing on the key differences between Dynamics 365 and the new ERP system. Tailor the training to each department’s specific needs, ensuring that users understand both the new system and any changes in business processes.
Support and Assistance from the Group: Leverage support from other companies within the group who are already using the ERP system. Group-wide best practices can speed up user adoption and provide guidance for common challenges.
System Testing: Perform rigorous system testing to ensure all workflows, integrations, and data migrations function correctly. Involve key users in user acceptance testing (UAT) to validate that the system meets their expectations and requirements.
Pilot Implementation: Consider a phased roll-out or a pilot in a specific department to test the system in a real-world environment before a full go-live.
Final Data Migration: Complete the final data migration after thorough testing and quality checks.
Go-Live Plan: Plan the go-live process to minimize disruption to business operations. Coordinate with the group to ensure system support is available during the critical transition period.
Monitoring and Issue Resolution: After go-live, closely monitor system performance and user feedback. Set up a support team to resolve any issues that arise quickly.
Review: Conduct a post-implementation review to assess the project’s success, noting lessons learned and areas for improvement. Verify that the system is being used efficiently, and evaluate its impact on productivity and internal communication within the group.
Ongoing Support: Continue to provide training and support as users become more familiar with the system. Maintain an open line of communication with the group to share insights and ensure continuous improvement.
Data Analysis: The first step is to thoroughly analyze the structure of the machine-generated data. This could involve sensor data, production logs, or performance metrics. Ensure the data is structured in a way that is compatible with Lobster Data’s input format, such as CSV, XML, JSON, or other formats Lobster supports.
Define Data Requirements: Identify the key data points that management will need to see in the Power BI report, such as production rates, machine efficiency, downtime, or error codes.
Data Source Configuration: In Lobster Data, configure the connection to the machine data sources. This could involve setting up Lobster to read data from an FTP server, an API, or direct connections to industrial systems (like PLCs or IoT platforms).Data Extraction: Set up Lobster’s Extract-Transform-Load (ETL) process to pull the machine data at regular intervals or in real time, ensuring that all necessary data points are captured.
Data Transformation: Using Lobster’s transformation capabilities, clean and structure the raw machine data. This could involve:
Formatting timestamps.
Aggregating data over time (e.g., hourly or daily averages).
Filtering out noise or irrelevant data.
Error Handling: Set up rules in Lobster to handle errors, ensuring that any corrupt or incomplete data is flagged for review and not processed into the data warehouse.
SQL Connection Setup: Configure Lobster Data to connect to the SQL database. This connection will allow Lobster to send the processed machine data directly to the SQL Server.
Stored Procedure Design: Develop SQL stored procedures that will handle the incoming data from Lobster. These stored procedures may perform tasks such as:
Inserting new machine data into the appropriate tables.
Updating existing records based on unique identifiers (e.g., machine ID or timestamp).
Performing additional calculations or data validation before inserting into the SQL warehouse.
Trigger SQL Procedures: Once the stored procedures are created, set Lobster to trigger them after each ETL process. This ensures that every time new machine data is extracted and processed, the SQL procedures will update the data warehouse automatically.
Table Design: Create the necessary tables in the SQL data warehouse to store the machine data efficiently. Depending on the complexity and volume of the data, these could include:
Fact tables for machine metrics (production rates, efficiency, etc.).Dimension tables for machine IDs, locations, operators, or time intervals.
Data Ingestion: Ensure the Lobster-triggered stored procedures write the data into the correct fact and dimension tables in the warehouse. This structured format allows for fast querying and reporting.
Data Indexing and Partitioning: Optimize the database tables with indexing and partitioning strategies to ensure that queries, especially from Power BI, are efficient even as the data grows over time.
Power BI Connection to SQL Warehouse: In Power BI, establish a connection to the SQL data warehouse. Power BI can directly pull data from SQL tables, allowing for real-time or near-real-time visualization of machine data.
Data Modeling in Power BI: Build relationships between the fact and dimension tables in Power BI’s data model. This allows management to slice and dice data by different attributes, such as:
Machine performance by date or time.
Comparison between machines or production lines.
Analyzing performance trends over time.
Creating Visuals: Design visualizations that transform the raw machine data into actionable insights. Some key visuals might include:
KPIs: Display key performance indicators such as production output, machine uptime, or error rates.
Line Charts: Show trends in machine performance over time.
Heatmaps: Visualize machine downtime by time of day or production shifts.
Bar Charts: Compare performance across machines or lines.
Interactive Dashboards: Create interactive dashboards where management can filter by specific machines, time periods, or production batches to drill down into specific issues or opportunities.
Data Refresh in Power BI: Configure Power BI to automatically refresh the data from the SQL data warehouse at regular intervals (e.g., daily or hourly). This ensures that management always has access to the latest machine performance data.
Notifications and Alerts: Set up Power BI to send automated alerts or notifications to management when certain thresholds are met, such as when machine efficiency drops below a certain level or when production goals are exceeded.
Sharing Reports: Publish the Power BI reports to the appropriate workspace and share them with the management team, allowing them to access the dashboards on demand from any device.
End-to-End Testing: Once the system is fully set up, test the entire process from machine data extraction in Lobster Data to SQL data ingestion and report generation in Power BI. Ensure that:
Data is being extracted correctly from the machines.
SQL procedures are working as expected.
Power BI reports reflect accurate, up-to-date information.
Validation: Validate the data accuracy by cross-checking key metrics in Power BI with the raw machine data to ensure there are no discrepancies.
Monitor Performance: Regularly monitor the performance of the entire system, from Lobster’s ETL processes to SQL query performance and Power BI report responsiveness.
Optimize Queries: As the data volume increases, optimize SQL queries and Power BI models to ensure fast response times.
System Updates: Keep Lobster, SQL Server, and Power BI updated to their latest versions to take advantage of new features and security updates.
Want to get a clear view of your business? Don't know what data you can trust?
Let us help you.
