Step-by-Step Process for Creating Views in a Fabric Data Warehouse

Creating Views in a Fabric Data Warehouse helps you set up and show data in a way that fits your business. You can make a view by writing a SQL query that picks the data you need. This lets you make hard tables easier, control who sees the data, and help with better analysis. Use views to keep private information safe and only give users what they need.

Key Takeaways

• Views are like pretend tables. They show new data but do not save it. This makes data easier to use and handle.

• You can make views by writing easy SQL queries. Then you save them. This helps make hard data simple. It also keeps private data safe.

• Only Admin, Member, or Contributor roles can make or change views. Viewers can only look at the data.

• Use views to choose who can see certain data. You can add security like row-level and column-level rules.

• Always update your schema after you make or change views. This lets you see your changes. Use views in Power BI to make better reports.

Views Overview

What Are Views?

A view is like a pretend table in your Fabric Data Warehouse. It does not keep any data inside it. Instead, it shows results from a SQL query each time you use it. This way, you always see the newest data. You do not have to copy or move anything.

Here is a quick summary of what makes up a view:

To make a view, you write a SQL query. When you save it as a view, you can use it like a table. For example, you might want to show just employee names and departments. You can do this with a view. You do not have to change the real table.

Tip: Views help you keep your data neat and simple to use. You can make many views for different jobs without changing your main tables.

Why Use Views?

Views give you many good things when you work in a data warehouse:

• Simplify Complex Queries: You can turn a long or hard SQL query into a simple view. This makes your work easier and quicker.

• Abstract Logic: You can hide how your data is set up. People only see what you want them to see.

• Secure Sensitive Data: You can use views to show only some columns. For example, you can hide salary or private details from people who should not see them.

• Promote Reusable Logic: You can use the same view in many reports or dashboards. This saves time and keeps your work the same.

Some ways you might use views are:

• Showing just the columns needed for a report

• Hiding private data from some people

• Making a summary, like average salary by department

Views help you control who sees what, keep your data safe, and make your work easier. You can use them in many ways to help your business.

Creating Views

Creating Views in Microsoft Fabric Data Warehouse helps you organize and present your data for analysis. You can follow these steps to create a view, from accessing your warehouse to using the view in your reports.

Accessing the Warehouse

Before you start, make sure you meet these requirements:

• You have a Microsoft Fabric-enabled workspace.

• You have the right license, such as Power BI Premium or an F64 trial.

• You are signed into the Power BI Service at https://app.fabric.microsoft.com

• .

• You have access to a SQL analytics endpoint or Warehouse in a Premium capacity workspace.

• Your user account has Contributor, Member, or Admin permissions.

• Your connection uses Microsoft Entra ID for authentication.

• TCP port 1433 is open, and your firewall allows Power BI service tags.

Note: Only Admin, Member, and Contributor roles can create or modify views. Viewer roles can only read data.

Writing the SQL Query

You need to write a SQL query that selects the data you want in your view. Use the SQL editor in the Data Warehouse Explorer or connect with tools like SQL Server Management Studio.

The recommended syntax for Creating Views is:

CREATE OR ALTER VIEW [schema_name].[view_name] AS
SELECT column1, column2
FROM [schema_name].[table_name]
WHERE condition;

Here are some practical examples you can use:

• Count the number of trips for each medallion:

CREATE OR ALTER VIEW dbo.TripCountsByMedallion AS
SELECT m.medallion_id, COUNT(t.trip_id) AS trip_count
FROM dbo.Medallions m
JOIN dbo.Trips t ON m.medallion_id = t.medallion_id
GROUP BY m.medallion_id;

• Show the number of passengers per trip:

CREATE OR ALTER VIEW dbo.PassengerCounts AS
SELECT trip_id, passenger_count
FROM dbo.Trips;

• Analyze trips by hour on working days:

CREATE OR ALTER VIEW dbo.TripsByHour AS
SELECT t.trip_id, d.date, ti.hour
FROM dbo.Trips t
JOIN dbo.Dates d ON t.date_id = d.date_id
JOIN dbo.Times ti ON t.time_id = ti.time_id
WHERE d.is_holiday = 0 AND d.is_weekend = 0;

Tip: Use simple SELECT statements for better performance. Avoid complex joins and nested queries when possible.

Saving as a View

After you write your query, you need to save it as a view. You can do this in the Data Warehouse Explorer:

1. Highlight your SQL SELECT statement in the editor.

2. Click the Save as view button.

3. Choose the schema where you have permission to create views.

4. Provide a name for your view.

5. Review your SQL statement for errors.

6. Confirm to create the view.

If you face errors saving through the UI, you can run the CREATE OR ALTER VIEW statement directly in the SQL editor. This method works even if the UI has issues.

Note: Sometimes, saving a view through the UI may not work due to internal errors. Using T-SQL commands is a reliable workaround.

Naming and Confirming

When you save your view, give it a clear and descriptive name. Add a description if the option is available. After saving, check the Explorer pane to confirm your view appears.

If you do not see your new view, refresh the schema:

1. Open the Schema explorer pane.

2. Find your data source and schema.

3. Click the ellipsis (three dots) next to the schema.

4. Select Update schema.

5. Your new view should now appear in the Explorer pane.

Tip: Always refresh the schema after Creating Views to see your latest changes.

Using the View

Once your view appears in the Explorer, you can use it like a table. Run queries against your view using T-SQL in the SQL editor. You can also use your view in Power BI reports and dashboards.

For example, to see the data in your new view:

SELECT * FROM dbo.TripCountsByMedallion;

You can join views with other tables or views for more advanced analysis. If you attach your view to the default semantic model, you can use it in Power BI as a data source.

Note: Views in the Data Warehouse support T-SQL queries. If you create views in a Lakehouse, they use Spark SQL and do not appear in the SQL endpoint.

Creating Views helps you organize your data, simplify queries, and control access. You can use these steps to build views that fit your business needs.

Features and Limitations

Supported Features

When you use views in Microsoft Fabric Data Warehouse, you get many helpful tools. These tools help keep your data safe and neat. Here are some main things you can do:

• Object-level security lets you pick who can see or change a view.

• Column-level security helps you hide some columns from certain people.

• Row-level security means users only see rows for their role.

• Dynamic data masking hides private data in results, so users see only what they should.

• Audit logging keeps track of who looks at or changes your views.

• Full T-SQL support lets you use normal SQL commands to make and manage views.

You can put your data into schemas and tables, just like other cloud data warehouses. Fabric also lets you run queries across databases with OneLake. You do not have to copy data between databases.

Tip:

Limitations

There are some things you cannot do with views in Fabric Data Warehouse:

• You cannot use materialized views or indexed views.

• ORDER BY is not allowed unless you use TOP or OFFSET-FETCH.

• You cannot use nested common table expressions (CTEs) or procedural logic in views.

• You cannot add parameters to views.

• Some advanced features from other systems may not work because Fabric uses distributed processing.

Best Practices

You can use these tips to get the best results with views:

• Give each view a name that makes sense.

• Use views to make hard queries easier for your reports.

• Add row-level and column-level security to keep data safe.

• Refresh your schema after you make or change a view to see updates.

• Use views as sources in Power BI for easy reports and analysis.

• Keep your SQL queries simple for better speed.

Note: Using views in Power BI makes reports easier to handle and keeps data safe. Always check your security before you share views with others.

---

You can start Creating Views in Microsoft Fabric Data Warehouse by following these steps:

1. Set up a warehouse in your workspace.

2. Open the SQL editor and write a query for your view.

3. Run the query to create the view.

These steps help you transform data without making extra copies. You can make reports easier and keep your data safe. Try Creating Views to organize your data better. Explore more ways to optimize views and connect them with analytics tools like Power BI.

FAQ

How do you update an existing view in Fabric Data Warehouse?

You can update a view by running a CREATE OR ALTER VIEW statement with your new query. This command replaces the old view with your changes. Always check your results after you update.

Can you delete a view if you no longer need it?

Yes, you can remove a view by running this command:

DROP VIEW [schema_name].[view_name];

This deletes the view but does not affect the data in your tables.

Do views affect the performance of your queries?

Views do not store data. Each time you use a view, Fabric runs the query behind it. Simple views run fast. Complex views with many joins or filters may slow down your results.

Can you use views in Power BI reports?

Yes! You can connect Power BI to your Fabric Data Warehouse and use views as data sources. This helps you build reports with only the data you need.

What should you do if your new view does not appear in the Explorer?

Refresh the schema in the Explorer pane. Click the three dots next to your schema and select Update schema. Your new view should now show up.