This article assumes that you have a basic understanding of how to use Power BI Desktop. If you\u2019re new to Power BI and Power BI Desktop, you might want to review the other articles I\u2019ve written on the topic:<\/p>\n
- \n
- Working with SQL Server data in Power BI Desktop<\/a><\/li>\n
- Power Query Formula Language in Power BI Desktop<\/a><\/li>\n
- More Power BI to your Elbow<\/a><\/li>\n
- Importing Excel Data into Power BI Desktop<\/a><\/li>\n<\/ul>\n
You can also refer to Microsoft documentation for more specifics about Power BI and Power BI Desktop. With that in mind, let\u2019s start by looking at how to import SQL Server metadata and system data into Power BI Desktop.<\/p>\n
Importing SQL Server data into Power BI Desktop<\/h1>\n
When you import data from SQL Server into Power BI Desktop, you specify the instance, database, and data you want to retrieve. You can retrieve individual tables and views directly, or you can define T-SQL queries to retrieve exactly the data you need.<\/p>\n
Writing queries provides greater flexibility because you can declare variables, define temporary tables, execute stored procedures, call functions, and use other T-SQL elements. To demonstrate how this works, we\u2019ll base our first example on the following T-SQL script, which uses the sp_spaceused<\/strong> system store procedure to retrieve space usage information about each user table in the AdventureWorks2014<\/strong> database:<\/p>\n
SELECT IDENTITY(INT, 1, 1) TableID, \r\n s.name SchemaName, \r\n t.name TableName,\r\n (s.name + '.' + t.name) FullName\r\nINTO #tables\r\nFROM sys.tables t INNER JOIN sys.schemas s \r\n ON t.schema_id = s.schema_id\r\nWHERE s.name <> 'dbo'\r\nORDER BY s.name, t.name;\r\n\r\nDECLARE @count INT = (SELECT COUNT(*) FROM #tables);\r\nDECLARE @i INT = 1;\r\nDECLARE @FullName NVARCHAR(128) = '';\r\nDECLARE @results TABLE(\r\n TableName NVARCHAR(128),\r\n NumRows CHAR(20),\r\n ReservedSpace VARCHAR(18),\r\n DataSpace VARCHAR(18),\r\n IndexSpace VARCHAR(18),\r\n UnusedSpace VARCHAR(18));\r\n\r\nWHILE (@count >= @i)\r\nBEGIN\r\n SET @FullName =\r\n (SELECT FullName FROM #tables WHERE TableID = @i);\r\n INSERT @results EXEC sp_spaceused @FullName;\r\n SET @i = @i + 1;\r\nEND\r\n\r\nSELECT t.SchemaName, t.TableName, r.NumRows, \r\n r.ReservedSpace, r.DataSpace, r.IndexSpace, r.UnusedSpace\r\nFROM @results r INNER JOIN #tables t\r\n ON r.TableName = t.FullName;\r\n<\/pre>\n
The script takes a number of steps in order to return usage information about each table:<\/p>\n
- \n
- Creates the #tables<\/strong> temporary table to hold the schema and table names, along with a unique identifier for each row.<\/li>\n
- Declares the @count<\/strong> variable to hold the number of rows from the temporary table.<\/li>\n
- Declares the @i<\/strong> variable that will be used as the index value to loop through the temporary table.<\/li>\n
- Declares the @FullName<\/strong> variable that will be used to hold the schema\/table name pair when looping through the temporary table.<\/li>\n
- Declares the @results<\/strong> table variable to hold the returned values from the sp_spaceused<\/strong> system store procedure.<\/li>\n
- Constructs a WHILE<\/strong> loop to iterate through the temporary table and run the sp_spaceused<\/strong> system store procedure against each table.<\/li>\n
- Uses a SELECT<\/strong> statement to join the temporary table with the @results<\/strong> variable to return the data we\u2019ll import.<\/li>\n<\/ol>\n
There are other approaches you can take to get this information, such as using cursors or the undocumented stored procedure sp_msforeachtable<\/strong>, but the T-SQL script we have here gives us what we need for this article.<\/p>\n
We can now use this script when creating a connection to the SQL Server database. To create the connection, click the Get<\/strong> Data<\/strong> down arrow on the Home<\/strong> ribbon, and then click SQL<\/strong> Server<\/strong>. When the SQL<\/strong> Server<\/strong> Database<\/strong> dialog box appears, specify the SQL Server instance, target database, and T-SQL script, as shown in the following figure. (You must click Advanced<\/strong> options<\/strong> for the SQL<\/strong> statement<\/strong> box to appear.)<\/p>\n
![\"_screenshots5\/st_PowerBI5_ImportData_SqlServerDB.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-6.png\")
<\/p>\nWhen you click OK<\/strong>, a new window should appear, displaying a sample of the data returned by the T-SQL script, as shown in the following figure.<\/p>\n
![\"_screenshots5\/st_PowerBI5_ImportData_LoadDataDB.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-7.png\")
<\/p>\nIf the data looks like what you would expect, click Load<\/strong>. Power BI Desktop will import the data and make the query available in the query window. The following figure shows the query as it appears when you first import the data.<\/p>\n
![\"_screenshots5\/st_PowerBI5_ImportData_Query1.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-8.png\")
<\/p>\nThe results might be difficult to view here, but if you import the data into your own instance of Power BI Desktop, you\u2019ll see that the columns specific to data usage amounts are string values that provide the amounts in kilobytes, with the KB<\/strong> included.<\/p>\n
To be able to work with the data more effectively, I removed the KB<\/strong> and the space that preceded it from each column, then converted the columns to a numerical value. I also renamed the query to TableUsage<\/strong>, as shown in the following figure.<\/p>\n
![\"_screenshots5\/st_PowerBI5_ImportData_Query2.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-9.png\")
<\/p>\nNotice that the Applied<\/strong> Steps<\/strong> section of the Query<\/strong> Settings<\/strong> pane now shows each transformation related to removing the KB<\/strong> and converting the fields. There are, of course, numerous other changes and transformations we can make, such as filtering data, changing the field names, adding calculated columns, or taking any number of other steps, but what we\u2019ve done here will suffice for now.<\/p>\n
After modifying the query, be sure to apply and save your changes. If you go to the Data<\/strong> tab, you should find the table associated with your query, as shown in the following figure.<\/p>\n
![\"_screenshots5\/st_PowerBI5_ImportData_DataView1.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-10.png\")
<\/p>\nThat\u2019s all there is to importing SQL Server metadata and system information into Power BI Desktop. The key is in writing a query that returns the information necessary to create various types of visualizations.<\/p>\n
Visualizing table usage statistics<\/h1>\n
Once we have the data in Power BI Desktop, we can go to the Report<\/strong> tab and begin adding visualizations. Often a good place to start is with a Matrix visualization, which allows us to display hierarchical data in a readable format, as shown in the following figure.<\/p>\n
![\"_screenshots5\/st_PowerBI5_TableUsage_Matrix.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-11.png\")
<\/p>\nWhen I set up the visualization, I first added the SchemaName<\/strong> field as a row, and then added the TableName<\/strong> field as a row. This allowed me to preserve the data hierarchy. I then added the remaining fields as values (measures). From these values, Power BI Desktop was able to render the data and provide the aggregated totals. I then specified a style for the matrix, changed the title, and modified its format.<\/p>\n
For this article, I won\u2019t be going into the specifics of formatting the visualizations, but know that you can take advantage of the assortment of options available to each visualization. I encourage you to play around with the formatting and labelling so each visualization looks exactly the way you want it.<\/p>\n
Now let\u2019s add a Stacked Bar Chart visualization. To preserve the hierarchy, I added the SchemaName<\/strong> field as an axis, and then added the TableName<\/strong> field as an axis. Next, I added the DataSpace<\/strong> and UnusedSpace<\/strong> fields as values (measures). The first measure provides data about how much space has been used, and the second indicates how much of the allocated space is unused, as shown in the following figure.<\/p>\n
![\"_screenshots5\/st_PowerBI5_TableUsage_StackedBarChart1.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-12.png\")
<\/p>\nThe visualization shows space usage information for five schemas, with each bar indicating the amount of space used and the amount unused. When taken together, we get the total amount of allocated space.<\/p>\n
If we hover over a section of a schema\u2019s bar, a pop-up message appears, providing specifics about the underlying data. In this case, the pop-up message shows that the Sales<\/strong> schema is using 20,696 KB of space.<\/p>\n
To view more detailed information, right-click the schema\u2019s bar, and then click See<\/strong> Records<\/strong>. A new window appears, providing details about each table in the schema, as shown in the following figure.<\/p>\n
![\"_screenshots5\/st_PowerBI5_TableUsage_StackedBarChart2.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-13.png\")
<\/p>\nBecause we added both SchemaName<\/strong> and TableName<\/strong> as axis fields, Power BI Desktop automatically detects the hierarchy and provides tools within the visualization for drilling down into the data. For example, if you select the down arrow in the upper right corner and then click the Sales<\/strong> bar, the visualization will display data specific to that schema, with a bar for each table, as the following figure shows.<\/p>\n
![\"_screenshots5\/st_PowerBI5_TableUsage_StackedBarChart3.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-14.png\")
<\/p>\nNow let\u2019s try the Area<\/strong> Chart<\/strong> visualization, only this time, we\u2019ll use the ReservedSpace<\/strong> and DataSpace<\/strong> fields as our measures. This provides another way to visualize how much of the allocated space for each table is being used. Once again, we start with schema view at the top of the hierarchy, as shown in the following figure.<\/p>\n
![\"_screenshots5\/st_PowerBI5_TableUsage_AreaChart1.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-15.png\")
<\/p>\nAs you can see, the Person<\/strong> schema has been allocated much more space than it is using, so let\u2019s dig down into that schema, where we can quickly see that it is the Person<\/strong> table that\u2019s the big player here, as the following figure shows.<\/p>\n
![\"_screenshots5\/st_PowerBI5_TableUsage_AreaChart2.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-16.png\")
<\/p>\nThe nice part about the drilling capabilities in Power BI is that we can drill down into individual components, as we did above, or drill down for the entire data set. In other words, we can see all tables in all schemas at one time, as shown in the following figure.<\/p>\n
![\"_screenshots5\/st_PowerBI5_TableUsage_AreaChart3.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-17.png\")
<\/p>\nWith this and other types of visualizations, we can quickly see information about our databases in a format that is easily accessible and understood by a wide range of users. Of course, it will be up to you to determine what data might be of value to which users. For example, a DBA might want a quick way to apprise developers of database usage patterns, without having to get into the nitty gritty of what\u2019s going on inside of SQL Server. In such cases, a picture can be worth a thousand words.<\/p>\n
Visualizing column data<\/h1>\n
We are not, of course, limited to just visualizing usage patterns. For example, we might want to pull column information into the mix, using a query such as the following:<\/p>\n
SELECT s.name SchemaName,\r\n t.name TableName,\r\n c.name AS ColumnName, \r\n dt.name AS DataType\r\nFROM sys.schemas s JOIN sys.tables t \r\n ON s.schema_id = t.schema_id\r\n JOIN sys.columns c \r\n ON t.object_id = c.object_id\r\n JOIN sys.types dt\r\n ON c.user_type_id = dt.user_type_id\r\nWHERE s.name <> 'dbo'\r\nORDER BY SchemaName, TableName, ColumnName;\r\n<\/pre>\n
All we\u2019re doing here is grabbing the names of the schemas, tables, columns, and data types in the AdventureWorks2014<\/strong> database. Once we get the data into Power BI Desktop, we can again start with a Matrix visualization to provide users with easy access to the entire data set. As you can see in the following figure, Power BI Desktop again takes care of our hierarchy, starting with the schema, then tables, and finally columns and data types.<\/p>\n
![\"_screenshots5\/st_PowerBI5_ColumnTypes_Matrix.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-18.png\")
<\/p>\nSuppose we now want to visualize the number of columns per table, even though our table appears to include no field that can serve as a measure. We can start with a Stacked Bar Chart visualization and then add ColumnName<\/strong> as a value field. Power BI Desktop treats the value as a row count to create the measure, giving us results similar to those shown in the following figure.<\/p>\n
![\"_screenshots5\/st_PowerBI5_ColumnTypes_StackedBarChart1.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-19.png\")
<\/p>\nAt the top of the hierarchy, we have the number of columns per schema, but as we saw earlier, we can drill down into individual schemas or view all tables together. For example, the following figure shows the tables in the Production<\/strong> schema, with each bar indicating the number of columns.<\/p>\n
![\"_screenshots5\/st_PowerBI5_ColumnTypes_StackedBarChart2.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-20.png\")
<\/p>\nYou can also view details about a specific table by right-clicking the table\u2019s bar and then clicking See<\/strong> Records<\/strong>. For example, the following table shows details about the Product<\/strong> table.<\/p>\n
![\"_screenshots5\/st_PowerBI5_ColumnTypes_StackedBarChart3.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-21.png\")
<\/p>\nPower BI Desktop provides a wide selection of visualizations for rendering different types of data, so feel free to experiment. It\u2019s very simple to switch from one visualization to the next, as long as it\u2019s consistent with the type of data you\u2019re trying to display. Often it just takes a single click to make the switch. For example, you can easily change a Stacked Bar Chart visualization to a Stacked Area Chart visualization, as shown in the following figure.<\/p>\n
![\"_screenshots5\/st_PowerBI5_ColumnTypes_StackedAreaChart1.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-22.png\")
<\/p>\nIf we were to drill down into the Production<\/strong> schema in this visualization, we would see the following information. In this case, details about the Product<\/strong> table are displayed.<\/p>\n
![\"_screenshots5\/st_PowerBI5_ColumnTypes_StackedAreaChart2.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-23.png\")
<\/p>\nYou might even want to use the Pie Chart visualization to display column information, as shown in the following figure.<\/p>\n
![\"_screenshots5\/st_PowerBI5_ColumnTypes_PieChart1.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-24.png\")
<\/p>\nAs with the other visualizations, we can drill down into to the next layer of the hierarchy. For example, if we drill down into the HumanResources<\/strong> schema, we get the following view of the tables and column counts.<\/p>\n
![\"_screenshots5\/st_PowerBI5_ColumnTypes_PieChart2.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-25.png\")
<\/p>\nYou might not be particularly interested in displaying the number of columns in each table and schema, but these examples should give you a good idea of the different ways you can render the same information with relatively little effort. It\u2019s up to you to figure out which type of data might be useful in your situation.<\/p>\n
Visualizing index data<\/h1>\n
Imagine this scenario. You\u2019re asked to look at a company\u2019s SQL Server database to try to figure out why they\u2019re running into performance issues. The company is a small start-up with a handful of developers with no SQL Server expertise but with lots of ideas of how a database works. When you look at the database, you quickly discover that it has been indexed into oblivion, with some tables configured with as many as 30 indexes.<\/p>\n
Indexes are notoriously over-utilized or in some other ways misused. You might find that visualizing information about a database\u2019s indexes to be a useful tool for providing regular updates to interested individuals about what\u2019s going on with the system. For example, you could start with a query such as the following to import index-related data into Power BI Desktop:<\/p>\n
SELECT s.name SchemaName,\r\n t.name TableName, \r\n i.type_desc IndexType,\r\n COUNT(i.type) TotalIndexes\r\nFROM sys.schemas s JOIN sys.tables t \r\n ON s.schema_id = t.schema_id\r\n JOIN sys.indexes i \r\n ON t.object_id = i.object_id\r\nWHERE s.name <> 'dbo'\r\nGROUP BY s.name, t.name, i.type_desc\r\nORDER BY SchemaName, TableName, IndexType;\r\n<\/pre>\n
All we\u2019re doing here is finding the number of indexes in each table, based on index type. If we were to add a Matrix visualization based on this data, it would look similar to the following figure.<\/p>\n
![\"_screenshots5\/st_PowerBI5_IndexTypes_Matrix.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-26.png\")
<\/p>\nWe might then add a Stacked Bar Chart visualization that gives us our index count, specifying SchemaName<\/strong>, TableName<\/strong>, and IndexType<\/strong> as our axis fields in order to preserve our hierarchy. The following figure shows the visualization at the top level of the hierarchy, with the index count for each schema.<\/p>\n
![\"_screenshots5\/st_PowerBI5_IndexTypes_StackedBarChart1.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-27.png\")
<\/p>\nIf we were to now drill down into the Person<\/strong> schema, we would find that the Person<\/strong> table has double the indexes as other tables in that schema.<\/p>\n
![\"_screenshots5\/st_PowerBI5_IndexTypes_StackedBarChart2.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-28.png\")
<\/p>\nAs we\u2019ve seen earlier, we can view details about the Person<\/strong> table by right-clicking the bar associated with that table and then clicking See<\/strong> Records<\/strong>, giving us the information shown in the following figure.<\/p>\n
![\"_screenshots5\/st_PowerBI5_IndexTypes_StackedBarChart3.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-29.png\")
<\/p>\nAnother way we can visualize this information is to drill down into the Person<\/strong> table within the visualization, giving us the following view of the indexes.<\/p>\n
![\"_screenshots5\/st_PowerBI5_IndexTypes_StackedBarChart4.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-30.png\")
<\/p>\nIf we want to instead use a Stacked Area Chart to view the data, the schema level would look similar to that shown in the following figure.<\/p>\n
![\"_screenshots5\/st_PowerBI5_IndexTypes_StackedAreaChart1.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-31.png\")
<\/p>\nDrilling into the Person<\/strong> schema gives is the following visualization, which shows the Person<\/strong> table as the clear winner in terms of the number of indexes.<\/p>\n
![\"_screenshots5\/st_PowerBI5_IndexTypes_StackedAreaChart2.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-32.png\")
<\/p>\nAnd, of course, we can drill down one more layer to the indexes themselves, giving us a full picture of the indexes defined on the Person<\/strong> table.<\/p>\n
![\"_screenshots5\/st_PowerBI5_IndexTypes_StackedAreaChart3.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-33.png\")
<\/p>\nThere is a wide range of information you can retrieve about indexes in SQL Server and a variety of ways you can visualize that information in Power BI Desktop. Again, it depends on your specific needs and the people who will be viewing this information.<\/p>\n
Visualizing server state data<\/h1>\n
Any information that you can retrieve from a SQL Server is fair game for visualizing in Power BI Desktop. For example, you might turn to dynamic management views to retrieve data, such as the sys.dm_xtp_system_memory_consumers<\/strong> view, which returns information about database-level memory consumers. The following SELECT<\/strong> statement uses the view to import the number of allocated bytes and used bytes per consumer:<\/p>\n
SELECT memory_consumer_desc MemConsumer,\r\n allocated_bytes AllocatedBytes, \r\n used_bytes UsedBytes\r\nFROM sys.dm_xtp_system_memory_consumers\r\nWHERE allocated_bytes > 0;\r\n<\/pre>\n
One way we can render this information is to add two Pie Chart visualizations and display them side-by-side, as shown in the following figure.<\/p>\n
![\"_screenshots5\/st_PowerBI5_MemoryConsumers_PieCharts.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-34.png\")
<\/p>\nThe visualization on the left displays the allocated bytes for each consumer, and the visualization on the right displays the used bytes, giving us quick insight into where there might be noticeable differences, such as with the System<\/strong> heap<\/strong> consumer.<\/p>\n
We can also use dynamic management views against a specific database. For example, the following SELECT<\/strong> statement targets the AdventureWorks2014<\/strong> database:<\/p>\n
SELECT OBJECT_NAME(object_id) TableName, \r\n fulltext_index_page_count IndexPages\r\nFROM sys.dm_db_fts_index_physical_stats;\r\n<\/pre>\n
The sys.dm_db_fts_index_physical_stats<\/strong> returns information about the full-text and sematic indexes in each table. In this case, the result set includes only a few rows, which we can easily render using a Pie Chart visualization and a Matrix visualization, as shown in the following figure.<\/p>\n
![\"_screenshots5\/st_PowerBI5_IndexStatistics_PieChartMatrix.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-35.png\")
<\/p>\nAs you can see, the visualizations provide a quick way to view the index-related data, which you can refresh at any time to ensure that the visualizations are displaying the most current information.<\/p>\n
Visualizing SQL Server data<\/h1>\n
SQL Server provides a wide range of system views for getting at the SQL Server data you need. Through those views, you can retrieve different types of metadata and system information from which you can create multi-page reports rich in visualizations and information. For example, the following figure shows the report page that includes the visualizations based on the memory consumer data.<\/p>\n
![\"_screenshots5\/st_PowerBI5_Visualize_ReportView1.png\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/11\/word-image-36.png\")
<\/p>\nAfter you import the SQL Server data into Power BI Desktop and create your reports, you can publish them to the Power BI service in order to make the visualizations available to other users. You can also share the Power BI Desktop files, but keep in mind that Power BI Desktop is meant primarily as a development tool, rather than an end-user consumption tool.<\/p>\n
Creating visualizations based on SQL Server metadata and system information might not be useful in every situation, but in circumstances when you need a way to quickly share or monitor critical information that can be easily digested by various types of users, you might find Power BI Desktop to be a useful tool to add to your arsenal. It\u2019s painless to install, simple to learn, and best of all, free. All you need is a little imagination to figure out the many ways to visualize the extensive amount of information available in your SQL Server instances.<\/p>\n","protected":false},"excerpt":{"rendered":"
You can easily use PowerBI Desktop to show graphically how your database is growing, which tables are taking the most space, how various parts of SQL Server is consuming memory, its use of indexes and so on. Sure, you can create graphs in SSMS, but with PowerBI, you can create reports that you can then publish to others, and which allow drill-down. It is a great way to get familiar with PowerBI Desktop as well. Rob Sheldon shows how simple it is to do.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528,143527],"tags":[],"coauthors":[6779],"class_list":["post-69003","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","category-database-administration-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69003","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=69003"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69003\/revisions"}],"predecessor-version":[{"id":92197,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69003\/revisions\/92197"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=69003"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=69003"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=69003"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=69003"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- Declares the @count<\/strong> variable to hold the number of rows from the temporary table.<\/li>\n
- Power Query Formula Language in Power BI Desktop<\/a><\/li>\n