However, how do you ensure your data is squeaky clean and free from errors, inconsistencies, and inaccuracies? That’s the question we’ll explore in this blog as we prepare for our upcoming webinar,” Data Assurance: The Essential Ingredient for Data-Driven Decision Making.”

The Data Dilemma

Data comes from various sources and often arrives in different formats and structures. Whether you’re a small startup or a large enterprise, managing this influx of data can be overwhelming. Many organizations face common challenges:

1. Data Inconsistencies: Data from different sources may use varying formats, units, or terminologies, making it challenging to consolidate and analyze.

2. Data Errors: Even the most careful data entry can result in occasional errors. These errors can propagate throughout your systems and lead to costly mistakes.

3. Data Security: With data breaches and cyber threats on the rise, ensuring the security of your data is paramount. Safeguarding sensitive information is a top concern.

4. Compliance: Depending on your industry, you may need to comply with specific data regulations. Non-compliance can result in hefty fines and a damaged reputation.

The Scrubbing Approach

One way to tackle data quality issues is through data scrubbing. Data scrubbing involves identifying and correcting errors and inconsistencies in your data. This process includes tasks such as:

1. Data Cleansing: Identifying and rectifying inaccuracies or inconsistencies in your data, such as misspellings, duplicate records, or missing values.

2. Data Standardization: Converting data into a consistent format or unit, making it easier to compare and analyze.

3. Data Validation: Checking data against predefined rules to ensure it meets specific criteria or business requirements.

4. Data Enrichment: Enhancing your data with additional information or context to improve its value.

While data scrubbing is a crucial step in data quality management, it often requires manual effort and can be time-consuming, especially for large datasets. Additionally, it may not address all data quality challenges, such as security or compliance concerns.

The Testing Approach

On the other hand, data testing focuses on verifying the quality of your data through systematic testing processes. This approach includes:

1. Data Profiling: Analyzing your data to understand its structure, content, and quality, helping you identify potential issues.

2. Data Validation: Executing validation checks to ensure data conforms to defined rules and criteria.

3. Data Security Testing: Assessing data security measures to identify vulnerabilities and ensure data protection.

4. Data Compliance Testing: Ensuring that data adheres to relevant regulations and compliance standards.

Data testing leverages automation and predefined test cases to efficiently evaluate data quality. It provides a proactive way to catch data issues before they impact your business operations or decision-making processes.

Dive into the world of data assurance and understand why it’s a standalone practice in data-driven success.

Data is the most valuable asset for any business in a highly competitive and fast-moving world. Maintaining the integrity and quality of your business data is therefore crucial. However, ensuring data quality assurance often comes with its own set of challenges.

Lack of data standardization: One of the biggest challenges in data quality management is that data sets are often non-standardized, coming in from disparate sources and stored in different, inconsistent formats across departments.

Data is vulnerable: Data breaches and malware are everywhere, making your important business data vulnerable. To ensure data quality is maintained well, the right tools must be used to mask, protect, and validate data assets.

Data is often too complex: With hybrid enterprise architectures on the rise, the magnitude and complexity of inter-related data is increasing, leading to further intricacies in data quality management.

Data is outdated and inaccurate: Incorrect, inconsistent, and old business data can lead to inaccurate forecasts, poor decision making, and business outcomes.

Heterogenous Data Sources We Work With Seamlessly

With iDAF, you can streamline data assurance across multiple heterogeneous data sets, avoid data quality issues arising during the production stage, completely remove the inaccuracy and inconsistency of sample-based testing, and increase 100% data coverage.

iDAF leverages the best open-source big data tools to perform base checks, data completeness, business validation, reports testing, and 100% data accuracy.

We leverage iDAF to carry out automated validation between target and source datasets for

1. Data Quality

2. Data Completeness

3. Data Integrity

4. Data Consistency

The Perfect Blend

So, should you choose data scrubbing or data testing? Well, the answer may lie in a combination of both.

1. Scrubbing for Cleanup: Use data scrubbing to clean and prepare your data initially. This step is essential for eliminating known issues and improving data consistency.

2. Testing for Ongoing Assurance: Implement data testing as an ongoing process to continuously monitor and validate your data. This ensures that data quality remains high over time.

Join us in our upcoming webinar, “Data Assurance: The Secret Sauce Behind Data-Driven Decisions,” where we’ll delve deeper into these approaches. We’ll explore real-world examples, best practices, and the role of automation in maintaining clean, reliable data. Discover how the right combination of data scrubbing and testing can empower your organization to harness the full potential of your data.

The post Scrub or Test: What Helps in Ensuring You Have the Cleanest Data appeared first on Indium.

Data wrangling can be a time-consuming and labour-intensive process, but it is necessary for achieving reliable and accurate results. In this blog post, we’ll explore various techniques and tools that are commonly used in data wrangling to prepare data for machine learning models.

1. Data integration: Data integration involves combining data from multiple sources to define a unified dataset. This may involve merging data from different databases, cleaning and transforming data from different sources, and removing irrelevant data. The goal of data integration is to create a comprehensive dataset that can be used to train machine learning models.
2. Data visualization : Data visualization is the process of creating visual representations of the data. This may include scatter plots, histograms, and heat maps. The goal of data visualization is to provide insights into the data and identify patterns that can be used to improve machine learning models.
3. Data cleaning: Data cleaning is the process of identifying and correcting errors, inconsistencies, and inaccuracies in the data. This step includes removing duplicate values, filling in missing values, correcting spelling errors, and removing duplicate rows. The objective of data cleaning is to ensure that the data is accurate, complete, and consistent.
4. Data reduction: Data reduction is the process of reducing the amount of data used in a machine learning model. This may involve removing redundant data, removing irrelevant data, and sampling the data. The goal of data reduction is to reduce the computational requirements of the model and improve its accuracy.
5. Data transformation: Data transformation involves converting the data into a format that is more suitable for analysis. This may include converting categorical data into numerical data, normalizing the data, and scaling the data. The goal of data transformation is to make the data more accessible for machine learning algorithms and to improve the accuracy of the models.        

Also check out this blog on Explainable Artificial Intelligence for a more ethical AI process.

Let’s look into some code:

Here we are taking a student performance dataset with the following features:

1. gender
2. parental level of education
3. math score
4. reading score
5.  writing score

For data visualisation, you can use various tools such as Seaborn, Matplotlib, Grafana, Google Charts, and many others to visualise the data.

Let us demonstrate a simple histogram for a series of data using the NumPy library.

Pandas is a widely-used library for data analysis in Python, and it provides several built-in methods to perform exploratory data analysis on data frames. These methods can be used to gain insights about the data in the data frame. Some of the commonly used methods are:

df.descibe(), df.info(), df.mean() , df.quantile() , df.count()

(- df is pandas dataframe)

Let’s see df.descibe(), This method generates a statistical summary of the numerical columns in the data frame. It provides information such as count, mean, standard deviation, minimum, maximum, and percentile values.

For data cleaning, we can use the fillna() method from Pandas to fill in missing values in a data frame. This method replaces all NaN (Not a Number) values in the data frame with a specified value. We can choose the value to replace the NaN values with, either a single value or a value computed based on the data. 

For Data reduction we can do Sampling, Filtering, Aggregation, Data compression.

In the example below, we are removing the duplicate rows from the pandas drop_duplicates() method.

We will examine data normalisation and aggregation for data transformation; we are scaling the data to ensure that it has a consistent scale across all variables. Typical normalisation methods include z-score scaling and min-max scaling.

    Here, we’re using a StandardScaler to scale the data.  

Use the fillna () method in the Python pandas library to fill in missing or NaN (Not a Number) values in a Data Frame or a Series by using the mean value of the column.

Transform the categorical data in the ‘gender’ column into numerical data using one hot encoding. We will use get_dummies(), a method in the Pandas library of Python used to convert categorical variables into dummy or indicator variables.

In conclusion, data wrangling is an essential step in the machine learning process. It involves cleaning, transforming, and preparing raw data for analysis to ensure that the data used in a machine learning model is accurate, consistent, and error-free. By utilising the techniques and tools discussed in this blog post, data scientists can prepare high-quality data sets that can be used to train accurate and reliable machine learning models.

The post Data Wrangling 101 – A Practical Guide to Data Wrangling appeared first on Indium.

What is Power BI and .pbix files?

Power BI is a market leading business intelligence tool by Microsoft for Cleaning, Modifying and Visualizing raw data to come up with actionable insights. Power BI comes with its own data transformation engine called power query and a formula expression language called DAX (Data Analysis Expressions).

DAX gives power BI the ability to calculate new columns, dynamic measures, and tables inside Power Bi desktop.

By default, Power BI report files are saved with .pbix extension which is a renamed version of a ZIP file which contains multiple components, such as the visuals, report canvas, model metadata, and data.

What is Power BI .pbit file

.pbit is a template file created by Power Bi desktop which is also a renamed version of a ZIP file that contains all the Meta data for the Power BI report but doesn’t contain the data itself. Once we extract .pbit file we get a DataModelSchema file along with other files which contain all the Meta data of a Power BI desktop files.

Later in this blog we will be using these .pbit and DataModelSchema files to extract Power BI desktop Meta data.

What is the Meta data in a Power BI Desktop file

Regarding what you see in the Report View in a Power BI desktop, meta data is everything. You can think of all the information as meta data, including the name, source, expression, data type, calculated tables, calculated columns, calculated measures, relationships and lineage between the model’s various tables, hierarchies, parameters, etc.

We will mainly concentrate on extracting Calculated Measures, Calculated Columns, and Relationships in this blog.

Extraction of Meta data using Python

Python was used to process and extract the JSON from the.pbit file and DataModelSchema. We first converted JSON to a Python dictionary before extracting the necessary Meta data.

Below are the steps we will need to achieve the requirement:

1. Exporting .pbix file as .pbit file

There are two ways to save our power BI desktop file as .pbit file.

• Once we are in Power BI desktop, we have an option to save our file as power BI template(.pbit) file
• We can go to File–>Export–>Power BI Template and save the .pbit file at the desired directory.

2. Unzipping .pbit file to get DataModelSchema file

We can directly unzip the .pbit file using the 7z-Zip file manager or any other file manager. Once we Unzip the file, we will get a folder with the same name as that of the .pbit file. Inside the folder we will get the DataModelSchema file, we will have to change its extension to .txt for reading in python.

3. Reading .pbit and Data model schema file in python

We have an option to directly read the .pbit file in python using the dax_extract library. Second option to read the text file in python and using the JSON module convert it into a Python dictionary. Code can be found in the GitHub repository link given at the end of this file.

4. Extracting Measures from the dictionary

The dictionary that we get consists details of all the tables as separate lists, Individuals tables have details related to the columns and measures belonging to that table, we can loop on each table one by one and get details of columns, Measures etc. Below is an example of the Python code can be found in the GitHub Repository link given at the end of this file.

5. Extracting calculated columns from the Dictionary

Like how we extracted the measures we can loop on each table and get details of all the calculated columns. Below is the sample output of the Python code can be found in the GitHub Repository link given at the end of this file.

Also Read:  Certainty in streaming real-time ETL

6. Extracting relationships from the dictionary

Data for relationships is available in the model key of the data dictionary and can be easily extracted. Below is the sample output of the Python code can be found in the GitHub Repository link given at the end of this file. 

7. Saving Extracted data as an Excel file

All the extracted data can be saved in empty lists and these lists can be used to derive a Pandas data frame. This Pandas data frame can be exported as Excel and easily used for reference and validation purposes in a complex model. Below snapshot gives an idea of how this can be done.

Conclusion

In this blog we learnt about extracting metadata from .pbit and DataModelSchema file. We have created a Python script that allows users to enter the file location of .pbit and DataModelSchema file and then metadata extraction along with excel generation can be automated. The code can be found on the below GitHub also sample excel files can be downloaded from below GitHub link. Hope this is helpful and will see you soon with another interesting topic.

The post Power BI Meta Data extraction using Python appeared first on Indium.

We’ll delve into this subject in more detail and offer a thorough tutorial on how to create high-performance Excel generation in this blog post. For data analysis, reporting, and visualisation, Excel is a crucial tool. However, creating Excel files can be time- and resource-consuming, especially when working with large datasets and altering Mendix’s column layout.

As a result, we will examine Excel generation with dynamic entities and columns in more detail in this post using a straightforward Java action and the Apache POI Java library. We’ll look at how to generate Excel files on the fly based on shifting requirements and Mendix data structures.

You will have a clear understanding of how to create Excel sheets with dynamic entities and columns in Mendix by the end of this post, giving you the ability to handle various data structures with ease. Prepare to increase your understanding of and proficiency with Excel generation!

To generate Excel sheets with dynamic entities and columns, follow the 10 steps outlined below. These steps will guide you through the process and help ensure that your Excel generation is optimized for high performance and efficiency.

Let us start with a simple domain model for Student Excel Generation

Create the simple Mendix application with the above domain model and make sure the following POI jars have been added to your user lib folder of the project as shown below.

Find the jars in https://mvnrepository.com/

Step 1: Create a new java action for generating Excel with two parameters.

1. List of Dynamic Entity Objects (Type Parameter)
2. File Document object

The output of the java action is an excel-file-document.

a. First create a type of parameter called DynamicEntity under the Type parameters tab of java action.

b. Now create two parameters, one by selecting the List as type and the DynamicEntity type parameter as Entity, and the other one is File Document Object.

Step 2: Click deploy for the eclipse to edit the Java action and remove the line which has the sentence “Java action was not implemented”.

Step 3: Now start writing the code between the begin user code and the end user code section.

Step 4: Create a workbook that helps to create the Excel file in .xlsx.

XSSFWorkbook workbook = new XSSFWorkbook ();

Step 5: Now, create a blank Excel sheet with the sheet name.

XSSFSheet sheet = workbook.createSheet(“Student Data”);

Step 6: Add the below code snippet to apply some styles to the header of the excel sheet.

CellStyle style = workbook.createCellStyle();

Font headerFont = workbook.createFont();

headerFont.setColor(IndexedColors.WHITE.index);

style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());

style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

style.setFont(headerFont);

Step 7: Add the below code snippet to create headers for Excel by creating row and respective cells for each attribute in an entity. The below snippet generates a header row with dynamic columns.

// Row Creation

XSSFRow row;

int rowid = 0;

row = spreadsheet.createRow(rowid++);

// Cell Creation

IMendixObject TopStudent = DynamicEntity.stream().findFirst().get();

for (int i = 0; i < TopStudent.getMembers(getContext()).size(); i++)

{

Set<?> columns = TopStudent.getMembers(getContext()).entrySet();

Object[] columnsarray = columns.toArray();

String[] column = columnsarray[i].toString().split(“=”);

String header = column[0];

Cell headercell = row.createCell(i);

headercell.setCellValue(header);

headercell.setCellStyle(style);

}           

Step 8: Below snippet helps to create each row for each record of the dynamic entity containing each column.

for (IMendixObject student: DynamicEntity) {

// Row creation for each student record

row = spreadsheet.createRow(rowid++);

int cellid = 0;

// Cell creation for each student attribute

for (int i = 0; i < student.getMembers(getContext()).size(); i++)

{

Set<?> columns = student.getMembers(getContext()).entrySet();

Object[] columnsarray = columns.toArray();

String[] column = columnsarray[i].toString().split(“=”);

String header = column[0];

if (!(student.getValue(getContext(), header)==null))

{

Cell cell = row.createCell(cellid++);

cell.setCellValue(student.getValue(getContext(), header).toString());

}

}

}

Step 9: Write the created workbook with the help of the following snippet:

ByteArrayOutputStream bytearraystream = new ByteArrayOutputStream();

workbook.write(bytearraystream);

// Convert to ByteArray

byte[] barray = bytearraystream.toByteArray();

InputStream is = new ByteArrayInputStream(barray);

workbook.close();

// Store the input stream to file document object.

Core.storeFileDocumentContent(getContext(), __StudentExcel, is);

StudentExcel.setHasContents(true);

Step 10: Finally, we reached the end of the code. Add the below return statementto complete the custom java action.

return __StudentExcel;

Now create a microflow and call the Java action by passing any entity. Then run the application and trigger the microflow to see the generated file in 5 seconds, which contains all records of the specified entity with all the attribute values.

Please click below to experience faster Excel generation.

https://excelgeneration-sandbox.mxapps.io/index.html?profile=Responsive

The excel file has been generated super-fast! It will look like the one below.

Conclusion

In conclusion, generating Excel sheets with dynamic entities and columns can be a challenging task, but with the help of a simple Java action and the Apache POI Java library, developers can create optimized Excel files that meet the needs of their users. By following the ten steps outlined in this guide, developers can create efficient and effective Excel generation solutions within their Mendix applications, even when working with large datasets and complex data structures.

The post High Performance – Excel Generation (Dynamic Entity & Columns) appeared first on Indium.

With the use of interactive dashboards, personalised graphics, and intuitive data visualisations, Domo lets you create your own stories. Furthermore, it provides data governance and role-based access controls to guarantee that users can only access the information that they are authorised to see. To further safeguard your data visualisations, you can quickly and easily set up single sign-on and multi-factor authentication.

This guide is intended to assist beginners in learning the fundamentals of Domo and creating powerful data visualizations that drive business results.

Getting Started with Domo

To get started with Domo, you must first create an account. To do so, visit the Domo Website and register for a free trial. After you’ve created your account, log in to access the Domo dashboard. Domo’s dashboard is the primary interface for interacting with data in the software. It displays a summary of all your data connections, reports, and dashboards. To make a new report or dashboard, go to the dashboard and click the “Create” button.

Given the volume of the client’s data, the current procedure made it necessary for the SAS Data Admin to manually intervene and made it necessary for key decision makers to wait three days before viewing useful processed data.

Workflow for Domo Data Visualization

The workflow for data visualisation in Domo typically includes the following steps:

1. Data Connection

Domo offers numerous data connection options, including databases, spreadsheets, cloud-based applications, and APIs. To connect to a data source, go to the dashboard’s “Data” tab and then click on “Connections.”

Once you’ve selected your data source, follow the on-screen instructions to connect to it. You can also use Domo’s data connectors to bring in data from popular sources such as Salesforce, Google Analytics, and more.

2. Data Transformation

After connecting to your data source, you can begin transforming the data. To do so, go to the “Data” tab and then click “Transform.”

Domo offers a simple drag-and-drop interface for data transformation. Data transformation options include filtering, merging, and pivoting. You can also use the “Calculated Fields” feature to create custom calculations based on your data.

3. Data Visualization

Once your data has been transformed, you can start visualizing it. To do so, go to the dashboard’s “Visualize” tab and then click “Create.”

Domo provides a wide range of visualisation options, such as bar charts, line charts, pie charts, and more. You can also create custom visualisations based on your data by using the “Custom Visualizations” feature. Simply drag and drop the data elements you want to visualise into the visualisation builder to create a visualisation. To further customise your visualisation, you can add filters, calculated fields, and other data elements.

4. Creating Dashboards

Dashboards are an effective way to communicate insights and share data with others. To create a dashboard, go to the dashboard’s “Visualize” tab and click on “Dashboards.”

After you’ve created your dashboard, you can add visualizations, text, and images to create a comprehensive and interactive presentation of your data. You can also use the “Widgets” feature to add interactive elements to your dashboard such as charts, graphs, and maps.

5. Sharing and Collaborating

One of Domo’s most important features is its ability to share and collaborate on data. To share a report or dashboard, simply click the “Share” button.

You can share your report or dashboard with others by emailing them a link or embedding it in a web page. You can also limit who has access to the report or dashboard and what actions they can perform on it, such as viewing, editing, or commenting on it. This facilitates your teams to deliver data-driven collaboration and decision-making.

Business intelligence and data analytics depend heavily on data visualisation because this is how customers will see the outputs and outcomes they need. Check out this blog to learn more.

Domo vs. Tableau vs. Power BI

When comparing top BI tools, many factors must be considered. Keeping track of all business affairs is becoming increasingly difficult for any company, as large volumes of data pose a challenge to companies of all sizes. However, each of these three tools is capable of solving them in its own way.

Domo: How Can It Benefit Your Business?

1. Better Decision Making: By providing a centralized platform for data analysis, visualization, and collaboration, Domo enables businesses to make data-driven decisions, leading to improved outcomes and increased efficiency.

2. Improved Insights: With its powerful visualization options and ability to integrate with a wide range of data sources, Domo helps businesses gain new insights into their data, enabling them to identify trends, patterns, and opportunities.

3. Increased Productivity: By automating manual data collection and cleaning processes, Domo saves businesses time and resources, freeing up employees to focus on more strategic tasks.

4. Better Collaboration: With its sharing and collaboration features, Domo makes it easy for teams to work together on data, improving communication and collaboration among team members.

5. Improved Data Management: With its centralized platform for data management and analysis, Domo makes it easier for businesses to keep track of their data, reducing the risk of data loss or corruption.

6. Increased Flexibility: Domo’s cloud-based platform and ability to integrate with a wide range of data sources makes it easy for businesses to adapt to changing needs, increasing their ability to respond quickly to new opportunities.

7. Better Data Security: With its robust security measures, Domo helps businesses protect their data and ensure the privacy of their information.

Overall, Domo helps businesses turn data into insights and drive results, enabling them to make informed decisions, improve outcomes, and stay ahead of the competition.

Conclusion

When comparing Domo vs. Tableau vs. Microsoft Power BI, consider who will be using these tools.

Power BI is designed for the joint stakeholder, not the data analyst. As a result, the interface is more reliant on drag and drop and automatic features.

Tableau is equally powerful, but the interface isn’t entirely intuitive, making it more difficult to use and learn. Domo, on the other hand, is ideal for businesses looking for an all-in-one cloud-based data and analytics solution. It is used by many customers to supplement existing solutions. Given its flexibility and breadth, domo is a good choice for any organization looking to get more value from its data.

Domo is a powerful data management, analysis, and visualisation tool. Domo’s user-friendly interface, extensive data connections, and powerful visualisation tools make it simple for businesses to turn data into insights and drive results. Domo is a valuable tool for any organisation looking to harness the power of data, whether you are a beginner or an experienced data analyst.

We’ll get to the next interesting topic soon.

I hope this was useful.

Happy Reading…!!!!!

The post Domo for Dummies: A Guide to Creating Powerful Data Visualizations with Domo appeared first on Indium.