How do you design BI data systems and platforms?

The first step, in developing Business Intelligence (BI) data systems and platforms is to identify and evaluate the sources of data. Formats with variations in terms of quality, volume, and frequency of updates. It is crucial to assess these data sources based on their relevance, reliability, and accessibility to align with your BI objectives. Additionally ensuring high-quality data is important which means the data must be accurate, complete, consistent, and up-to-date. Different tools and techniques for improving data quality such as profiling, validation, cleansing, and enrichment can be used before integrating the data into your BI systems and platforms.

This is good advice but it shouldn’t be where you start. The first thing you need to do is understand high-level requirements, the key user personas, and the primary use cases. This will help you decide the appropriate type of BI platform. You also need to clarify scope, meaning what user groups and what data sources will be involved. Of course, this assumes the requesting stakeholder has already identified the business capabilities the BI platform is expected to support and has established alignment with the overall business and data strategy. Without this step, BI projects may wind up over building for the immediate use case or failing to architect for future change or expansion.

Some itens to consider or to get started: - Noisy data can cause a lot of problems when trying to extract insights or applying some analysis algorithm on them; - Create a diagram about the data types and structures found in the sources to be used; - Filter the data using predefined parameters to reduce undesired noises and to get the scope more "on target"; - Apply preprocessing algorithms to clean the data. This is important if your source are a text-based one, resultant of a Text Mining Application, like scraping comments data from internet. - For Text Mining Based Data, such as comments, an Vectorial or NoSQL Database can be useful for better performance - like OpenSearch, or MongoDB.

I think another important call out in the design phase is "what are we trying to accomplish?". I have found that asking the why in the beginning helps you to determine the right data sources and the right amount of data sources.

Establish a data governance framework for managing sources. Assess the credibility and legal aspects of external data providers. Use automation for ongoing quality checks and periodic reviews to ensure alignment with BI objectives.

Data architecture and modeling plays a critical role in designing business intelligence data systems and platforms. Data architecture defines the structure and flow of data within an organization. Data architecture includes decisions about indexing, partitioning, and storage that can further enhance query performance. Data modeling helps design the schema and relationships within the central data repository, ensuring the data is structured that supports analytical queries. Data architecture and modeling documentation provide a clear understanding of how data flows through the organization. This helps users and data professionals trace the source of data and understand its transformation process.

Data modeling is that task that seems simple from the outset and very quickly grows in complexity. Set aside ample time for your developers to produce these models if you're working with large datasets. Items like indexes, schemas, and transformation lineages, as well as a separate reporting database *outside* of your BI platform, will be necessary when dealing with multiple enterprise systems.

Data modeling is a critical component of BI because it provides a logical description of how different data entities relate to one another. Relationships become complex when combining data from different domains which may themselves have their own separate data models. Forming these logical relationships then enables multi-domain and multi-dimensional analyses.

In my experience Hard Business rules were implemented in Azure Data Factory during transformation and data was consolidated from multiple sources and created separate Data marts in Azure SQL. Modelling was done on top of this and soft Business rules were done in Power BI before publishing the reports

Data integration brings together data from different sources across an organization,creating a unified view.In my experience , enriching the data with its source information helps in debugging errors further.This ensures that BI users have access to a comprehensive dataset, enabling more accurate analysis.ETL processes clean and transform data, improving its quality by addressing issues such as inconsistencies, errors, and missing values. Clean data is crucial for accurate and reliable BI reporting.

In addition to the mentioned data integration and ETL tools it's worth noting that cloud providers such as Azure and AWS (also GCP) also offer their own robust orchestration tools for data integration and ETL workflows. Azure provides services like Azure Data Factory and Azure Logic Apps, while AWS offers AWS Glue and AWS Step Functions, among others. These cloud-based orchestration tools offer scalability, reliability, and integration with other cloud services, making them valuable options for organizations looking to streamline their BI data systems and platforms. Leveraging these cloud providers' services can provide additional flexibility and efficiency in managing data integration and ETL processes within a cloud environment.

Consider data lineage and maintain documentation for the ETL processes to ensure traceability. Opt for ETL tools that integrate well with your existing systems and are scalable to meet future data needs. Implement error-handling mechanisms to catch and resolve data issues automatically

Data visualization is integral to the success of data /BI projects. It aids in communication, quality assurance, debugging, monitoring, decision-making, documentation, user adoption, and performance tracking. Choosing the right visualization tools and techniques is crucial for maximizing the benefits of data engineering efforts.Visualization makes it easier for both technical and non-technical stakeholders to understand patterns, trends, and insights within the data.

Always consider the objectives of the dashboard and which users it intend to serve. Generally, dashboards are meant to have a more "exploratory" than "explanatory" look and feel. Only after users discover some insights that could inform actions, the "explanatory" look and feel becomes more relevant to tell a story. Try not to develop both types for a general dashboard!

The *best* selling point of a BI system is going to be its visual appeal. Sure, everything should work as it should. However - in terms of the UI, your designer should have a *very* strong grasp of what it is that makes visualization systems tick, and what engages your clients. There's a certain "look" that dashboards have when they truly capture your clients attention..

Ensure the visualizations are interactive to allow users to explore data on their own. Consider mobile responsiveness and accessibility features to make the data insights more inclusive. Set up automated delivery options like scheduled reports or real-time alerts based on certain KPI thresholds to keep stakeholders informed.

This aspect of BI is a vital ingredient to thr subject matter and if not handled with extreme caution and expertise can make business decision makers lose sight of key information capable of turning the Business tide. Data Visualization requires a certain kind of skillset, like being able to choose the right type of visual, report/dashboard layout and background, and generally blending colors in ways that doesn't disengage the audience. More importantly, the ability to convey the most important information with the vital few visuals is a major plus and there are variety of tools to help convey data storytelling.

Selecting dashboard charts and data presentation should be optimised to use fast through different kinds of data filters. Probably if you are directly accessing data from any reporting database it would not be the fast and user friendly dashboard sometimes. due to lake of network and huge volumes of data from sources.

To optimize dashboard performance, it is important to choose the right charts and visualizations, use data filters, cache data, and use a data warehouse. Other factors that can affect performance include the size and complexity of the data, the number of users, and the network bandwidth. Additionally, using a lightweight visualization library, avoiding too many charts and graphs, using a responsive design, optimizing the data queries, using a caching solution, and monitoring the performance of the dashboard can also help improve performance.