DEV Community: Nancy Mikia

SQL Subquery and CTEs( Common Table Expressions)

Nancy Mikia — Tue, 21 Apr 2026 00:06:55 +0000

What is a subquery?
Subquery is a query nested inside another query statement. It allows the query to be modular which would otherwise require multiple queries to achieve the same result. It runs first and the result is used as input by another query.
When to use it?
It is an essential tool when used in the following scenarios:

Filtering row data using the results from another query
Applying aggregation functions such as SUM or AVG dynamically
Simplify queries by breaking them into smaller, readable and maintainable queries instead of using joins or complex external code.
Compare data between tables to fetch specific insights

Types of subqueries

Scalar subquery
It returns a single value,such as one row and column. It is commonly used with comparison operators such as >,<, =
Use case: Find books that cause more than average

SELECT title,price FROM books WHERE price > (SELECT AVG(price) FROM books);

Multi-row subquery
It returns more than one value, i.e. multiple rows as result. It is often used with operators such as ANY, ALL or IN.
Use case: Books ordered by Customer John

SELECT title, author FROM books WHERE book_id IN ( SELECT book_id FROM orders WHERE customer_id IN ( SELECT customer_id FROM customers WHERE first_name = 'John' ));

Correlated subquery
It is dependent on the outer query for its values, i.e. it references columns from the outer query. It is executed once for each row of the outer query making it slower for larger datasets.
Use case: Customer who placed atleast 1 order of books

SELECT first_name, last_name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id

Table subquery( derived tables)
It returns a complete table. It is commonly used in the from clause.
Use case: Top 3 most expensive books

SELECT title,author,price FROM ( SELECT title,author,price, RANK() OVER (ORDER BY price DESC) AS price_rank FROM books ) AS ranked_books WHERE price_rank <= 3;

What are Common Table Expressions(CTEs)
CTEs are temporary, named results in SQL that can be referenced in a single statement. It is allowed to simplify complex queries making them reusable, readable and maintainable, and helps work with hierarchical data using recursions. It is distinctly created using the WITH clause.

Types and Use Cases of CTEs

Non-Recursive CTEs
These are used once within a single query to simplify logic. Ideal for breaking down complex joins, aggregations, or temporary calculations.
Use case: Find the list of customers and their total orders

WITH customer_order_count AS ( SELECT customer_id, COUNT(order_id) AS total_orders FROM orders GROUP BY customer_id) SELECT c.first_name, c.last_name, co.total_orders FROM customers c JOIN customer_order_count co ON c.customer_id = co.customer_id;
Recursive CTEs
Used when working with hierarchical data (e.g., organization charts, folder structures, family trees). A recursive CTE repeatedly references itself until a termination condition is met.
Use case: Generates a sequence of dates starting from the earliest order date upto 5 days ahead

-- Recursive CTE: Generate sequential dates WITH RECURSIVE date_sequence AS ( -- Anchor member: start from the earliest order date SELECT MIN(order_date) AS order_date FROM orders UNION ALL -- Recursive member: add 1 day until 5 days ahead SELECT order_date + INTERVAL '1 day' FROM date_sequence WHERE order_date < (SELECT MIN(order_date) + INTERVAL '5 days' FROM orders) ) SELECT order_date FROM date_sequence;
Common Use Cases:

Breaking complex queries into readable, logical parts.
Creating temporary result sets reused within a query.
Traversing hierarchical or self-referential data structures.
Simplifying code compared to nested subqueries or procedural loops.

Comparison between subqueries and CTEs

Conclusion
Subqueries and CTEs are essential tools for writing efficient and maintainable SQL code. Subqueries shine in simple filtering and dynamic aggregation, while CTEs offer superior readability and flexibility for complex, multi-step, or recursive logic. Choosing between them depends on the problem: use subqueries for straightforward tasks, but prefer CTEs when your query requires clarity, modularity, or repeated logic. Together, they form the foundation for clean, powerful, and scalable SQL development.

Publishing a Power BI Report and Embedding It into a Website

Nancy Mikia — Mon, 06 Apr 2026 02:29:47 +0000

Power BI is a powerful data visualization and reporting platform widely used by businesses and professionals to transform raw data into meaningful insights. In today’s data-driven world, organizations rely heavily on tools like Power BI to analyze trends, monitor performance, and support decision-making processes. Its intuitive interface, combined with advanced analytical capabilities, makes it a preferred choice for both technical and non-technical users.

At its core, Power BI enables users to connect to various data sources, clean and transform data, and create interactive dashboards and reports. Beyond visualization, it also plays a crucial role in defining relationships between datasets and applying schemas to ensure accurate and reliable analysis. Once a report is developed, it can be published to the Power BI service and embedded into websites or portals, allowing broader access to insights.

Embedding reports is particularly valuable for organizations that want to share data with stakeholders in a seamless and interactive way. Instead of relying on static reports, users can engage with live dashboards directly within websites, enhancing accessibility and usability. To successfully embed a Power BI report, there are four essential stages:

Creating a workspace
Uploading and publishing the report
Generating the embed code
Embedding the report on a website

Each of these stages plays a critical role in ensuring that the report is properly structured, accessible, and secure.

*Creating a Workspace
*
A workspace in Power BI serves as a collaborative environment where users can create, manage, and share reports and dashboards. It acts as a container that organizes content and controls access permissions.

Typically, a workspace can be personal or shared. A personal workspace is primarily for individual use, allowing users to build and manage their own reports. Shared workspaces, on the other hand, enable collaboration among team members, making it easier to distribute insights across an organization.

It is important to note that Power BI Desktop does not provide the interface for creating workspaces. To create one, users must access the Power BI service through a web browser by navigating to the Power BI website.

Steps to Create a Workspace:
Open the Power BI service in your browser.
Navigate to the Workspaces section.
Select New Workspace.

Provide a name and description for the workspace.
Assign appropriate permissions to users (Admin, Member, Contributor, or Viewer).
Save the workspace.

Once created, the workspace becomes the central location for storing and managing reports. If you already have access to an existing workspace, this step can be skipped, as reports can be directly uploaded there.

Workspaces are essential because they help maintain organization, ensure proper access control, and support collaboration among users. Without a workspace, managing reports at scale would be inefficient and prone to errors.

*Uploading and Publishing a Report
*
After creating a report in Power BI Desktop, the next step is to publish it to the Power BI service. Publishing makes the report accessible online and enables sharing, collaboration, and embedding.

Steps to Publish a Report:
In Power BI Desktop, go to File > Publish > Publish to Power BI.
Select the destination workspace where you want the report to be stored.
Click the Select button.

Wait for the publishing process to complete successfully.
Verify that the report and its dataset are available in the Power BI service.

Publishing is a crucial step because it transitions the report from a local environment to a cloud-based platform. This allows users to access the report from anywhere, collaborate with others, and integrate it into other applications.

*Generating the Embed Code
*
Once the report is published, the next step is to generate an embed code. This code allows the report to be displayed outside the Power BI environment, such as on websites or internal portals.

Embedding provides flexibility in how reports are shared. Organizations can choose between public access or restricted access, depending on their needs.

Steps to Generate an Embed Code:
Locate the report you want to share in the Power BI service.
Click on File > Publish to web.

Confirm the action to generate an embed code.
Copy the generated URL and iframe HTML code.
To manage existing embed codes, go to Settings > Manage embed codes.
Use the Get code option to retrieve previously created embed codes.

The embed code typically comes in the form of an iframe, which can be inserted directly into a website’s HTML. This allows users to view and interact with the report without leaving the website.

However, it is important to understand the implications of using the “Publish to web” option. Reports shared this way are publicly accessible, meaning anyone with the link can view them. This method is suitable for non-sensitive data but should not be used for confidential information.
Additionally, when a report is published, its underlying dataset is also uploaded. This dataset can be refreshed periodically, ensuring that the report always reflects the most up-to-date information. Organizations often configure scheduled refreshes to automate this process.

*Embedding the Report on a Website
*
The final step is embedding the report into a website or portal. This is where Power BI truly shines, as it transforms static web pages into dynamic, data-driven platforms.

There are two primary methods for embedding reports:

*1. Secure Embedding (Recommended for Organizations)
*
This method ensures that only authorized users can access the report.

Steps:
Open the report in the Power BI service.
Select File > Embed report > Website or portal.

Copy the provided link or iframe code.
Paste the code into your website’s HTML editor.

With secure embedding, users must have the appropriate permissions and licenses to view the report. This ensures that sensitive data remains protected while still being accessible to authorized stakeholders.

*2. Publish to Web (Public Access)
*
This method makes the report available to anyone on the internet.

Steps:
Select File > Embed report > Publish to web.
Review the warning message carefully.
Click Create embed code.
Copy the link or iframe code and paste it into your website.

While this method is easy to implement, it should only be used for publicly shareable data, as there are no access restrictions.

**Key Insights

Accessibility and Usability** Embedding Power BI reports significantly improves accessibility. Instead of requiring users to log into Power BI, reports can be accessed directly from websites or portals. This is especially useful for organizations that want to share insights with clients, partners, or the public.

*2. Real-Time Data Interaction
*
Unlike static reports, embedded Power BI dashboards are interactive. Users can filter data, drill down into details, and explore insights in real time. This enhances user engagement and enables more informed decision-making.

*3. Security Considerations
*
Security is a critical factor when embedding reports. Organizations must carefully choose between secure embedding and public sharing. Sensitive data should always be protected באמצעות role-based access control and proper authentication mechanisms.

*4. Integration Capabilities
*
Power BI integrates seamlessly with various platforms, including websites, applications, and enterprise systems. This makes it a versatile tool for embedding analytics into existing workflows.

*Challenges and Best Practices
*
While embedding Power BI reports offers numerous benefits, there are some challenges to consider:

Data Security Risks: Public embedding can expose sensitive information if not handled properly.
Performance Issues: Large datasets or complex reports may affect loading times.
Licensing Requirements: Secure embedding often requires Power BI Pro or Premium licenses.
Best Practices:
Always evaluate the sensitivity of your data before choosing an embedding method.
Optimize reports for performance by reducing unnecessary visuals and data complexity.
Use role-based access control to restrict access to authorized users.
Regularly review and manage embed codes to maintain security.
Conclusion

Power BI is a powerful and versatile tool that goes beyond simple data visualization. By enabling users to create, publish, and embed interactive reports, it bridges the gap between data analysis and accessibility. The process of embedding reports—starting from workspace creation to final integration into websites—empowers organizations to share insights effectively and efficiently.

Embedding transforms traditional web pages into dynamic platforms where users can interact with live data. This not only enhances user experience but also supports better decision-making by providing real-time insights.

*Key Takeaway
*
Embedding Power BI reports using iframes and secure links allows organizations to deliver interactive, data-driven experiences directly within websites and portals. By following the structured process of creating a workspace, publishing reports, generating embed codes, and embedding them, businesses can unlock the full potential of their data and make insights more accessible than ever before.

Understanding Data Modeling in Power BI: Joins, Relationships, and Schemas Explained

Nancy Mikia — Sun, 29 Mar 2026 18:50:27 +0000

Data Modeling is the process of analyzing and defining different data type that is collected or produced. It also shows relationship between those data in visual form as data is captured, stored ans used in different ways as per the owner/business. Without a well-structured data model, dashboard in power BI can produce misleading insights,Poor performance and confusion.

In this article we will cover SQL joins, Relationship in Power BI,Schemas and common modeling issues.

SQL Joins Type

Joins combines data from multiple tables based on related column. In Power BI it happens before data is loaded into the model.
Inner Join- Returns to rows where there is matching values in both tables. It can be used to match employees to departments or authors to books.

Left Join-Returns all rows from left table and matched rows from the right. It can be used to list all departments and their employees

Right Join- Returns all rows from the right table and matched rows from the left. It will show all departments including those without employees.

Full Outer Join- Returns all rows when there is a match in either table. It Links employees to their department including the unmatched employees.

Left Anti Join- Returns rows from the left table that have no match to the right. Can be used to know employees who have not attempted to attend mandatory training.

Right Anti Join- Returns rows from the right table that have no match in the left. It can be used to determine employees recently employeed and have not been enrolled to the enjoy benefits.

We create the above on Power BI: Home>Transform data>Power query editor. Use Merge queries to select join type.

Power BI relationship

Relationships are created in the model view to connect tables after they are loaded. They inform us on how tables relate, allowing for filtering without merging data. It's created by going to Model view>Manage relationship
The following are some of the key concept in relationship:

Cardinality-Defines the uniqueness of values in columns used in relationship.
One to One(1:M)- Each row in table A related to one row in table B
One to many (1:M)- Each row in table A relate to multiple row in table B
Many to many (M:M)- Multipe row in table A relates to multiple row in table B

Cross-filter direction
Single- Filters flow in one direction
Both- Filters flow both ways

The difference between Joins and Relationship is that Join combines data at power query level whereas relationship defines connection between tables in the model.

Fact Vs Dimension tables
Fact tables answers the question what. It's use for measurable quantitative data e.g Sales whereas dimension table asnwers the question who, where and when. It's used to give descriptive attributes like customer product and date. They contain the unique key.

Data Schemas

Star Schema
It's a central fact tables connected to multiple dimension tables and is used for faster queries and simpler relationships in Power BI. It also improves performance.
Snowflake Schema
This is an extension of a star schema where dimension tables are normalized into multiple related sub-tables. It's used for managing complex hierarchical data and improving the data integrity of the same.
Flat table (DLAT)
It's a single wide table containing all facts and dimensions without relationships to other tables. It's used in small datasets and for quicker analysis.

Role-playing dimension
This allow a single dimension table to be used in multiple roles. In a bakery store, a date can be used to show order date, delivery date and return date.

Common Modeling issue

Creating many to many relationships can lead to ambiquity
Measure on role-play dimension requires duplicate data tables
Circular references between tables can cause errors and make the model unusable

Mastering data modeling in Power BI requires understanding SQL joins, relationships, schemas, and best practices. By carefully structuring fact and dimension tables, choosing the right schema, and managing relationships, you can build efficient, scalable, and insightful Power BI models.

How Excel is Used in Real World Data

Nancy Mikia — Wed, 25 Mar 2026 12:29:26 +0000

When I was first employed, I often heard excel was a game changer. I didn't know what that means. At that time I didn't understand spreadsheet application could make work easier. It soon became clear that this was a tool I needed to learn.
During the first Excel class, I realised Microsoft excel is a spreadsheet application that allows users to store, organize and manipulate data in rows and column. It was used to analyze and interpret data information that is essential for a business or organization.

Why Excel matters
Excel application extends beyond classroom. Business needs excel to monitor sales, analyze customer's behavior and project future revenue. A teacher needs excel to track their student's performance. A healthcare professional needs to keep record of their patient and analyze treatment outcome. A financer needs to track their expenses, plan for their savings and make informed financial decisions.
What stood out in all this, is that the data being fed on an excel help the user in making useful insights based on facts rather than assumptions.

My beginner struggles
As a beginner, interpreting raw data was overwhelming. I remember staring at a datasetof over 100 products and wondering where to start.The endless rows felt intimidating and I struggled to see how meaningful insights could emerge from such complexity.

This came with a big revelation of the following formulars to help me understand how to go about it.

Key Features and Formulas that helped
Sorting and Filtering- These tools allowed me to identify blank cells and replaced them with N/A so my analysis wouldn't misinterpret missing values
Freeze pane- Instead of memorizing column titles while scrolling through hundredth row, I used freeze panes to keep headers visible at all time.

With my sample dataset containing discounts, prices, and ratings,the following formulas made analysis much easier:

Average: =AVERAGE(RANGE) helped me calculate the average price, rating and discount.
Sum: =(SUM(Range) game me totals,such as the number of products in the file.
IF: =IF(logical_test, value_if_true, value_if_false) flagged products with high discountsor ratings,making comparison clearer
Text function(LEFT,RIGHT): These helped clean up messy data by extracting or combining text values.

Beyond formulas, Pivot tables became invaluablefor summarizing and grouping data efficiently. Visual tools like bar charts and pie charts made pattern easier to understand at a glance.

Reflection
Learning Excel has reshaped the way I interpret data. When felt like endless data now feels like an exciting puzzle to solve. This journey has showed me how data such as personal budgeting can reveal my patterns in how I spend money and in everyday life.
I've been able to see trends, ask better questions and make decision based on evidence other than guesswork. It has given me confidence in handling data professionally and personally, and it has depeened my appreciation for the power of data in shaping the modern world.