January 2010 - Posts
Not too many people know that they can query internal performance and resource related data for SSAS using DMVs. Yes, I’m talking about similar DMVs to the much more known relational database DMVs, only these DMVs can be queried by connecting to an SSAS instance, open a new MDX or DMX query window (either way will work for you) and type a simple SELECT statement which retrieves the information from the DMVs. These DMVs are actually metadata that is kept internally as schema rowsets and you can simply query them as if they were plain relational tables. There are actually quite a few interesting DMVs that you can use to get a real-time view of your Analysis Services server status.
To look at the list of all available DMVs that you can query, run the following SELECT query:
SELECT * FROM $system.dbschema_tables WHERE Table_Schema = '$SYSTEM'
A few of the more interesting DMVs are:
SELECT * FROM $system.DISCOVER_CONNECTIONS
This DMV holds information regarding connections made to the Analysis Service instance.
SELECT * FROM $system.DISCOVER_OBJECT_ACTIVITY
This DMV holds accumulative information regarding Analysis Services objects’ resource usage. It gives you a list of all objects and how much CPU each consumed, how much read and write activity occurred and even whether access to it incurred an aggregation hit or miss.
SELECT * FROM $system.DISCOVER_Commands
This is the semi equivalent to the relational sp_who2 system procedure and lists all active connections and their associated queries.
There are plenty more DMVs that you can use, simply use the first SELECT statement mentioned above to identify the DMVs that you need to add to your personal SSAS scripts arsenal and use at will. Note that the data in the DMVs is being cleared when you restart SSAS service (this is true to some extent, as object and data version information is kept, for example), so be aware when you perform an analysis on object usage etc. that you look at a long-enough snapshot of the DMV.
-Yaniv
(This is post number 10 for week number 9 of my SQL blog challenge)
PowerPivot is a combination of Excel and Analysis Services, enabling users to create complex and high-data-volume reports and enjoy the flexibility and usability of Excel alongside the high-performing SSAS engine. In this post I’ll be taking a closer look at a couple of DAX expressions. The DAX expression language is a special expression language which was added to the PowerPivot the Excel 2010 products, and enables the user to query multiple tables (as opposed to the traditional Excel expression language).
I’m going to use two new DAX functions, the RELATED() and the RELATEDTABLE() functions, to illustrate the very basic capabilities of the DAX expression language.
The RELATED function will link data from a table to another table, using the relationships created when the PowePivot model was created. We touched on Table Relationships in a previous blog post. So ,for example, If I want to add a calculated measure to the FactInternetSales fact table which displays the average yearly income of customers who bought specific products, I'll need to first add a new column to the FactInternetSales sheet and I’ll be using the following DAX expression to retireve the Yearly Income data from the DimCustomer table: RELATED(‘DimCustomer’[Yearly Income]). Next, I’ll add this as a measure to the Excel PivotTable report and change the aggregate function to AVERAGE, replacing the default SUM function. This is the end result of the report:
The new RELATEDTABLE() function returns a table, as opposed to a cell. There are several scenarios where you would want to use this function, specifically when you would want to show aggregations of measures from a table, but looking at a different, related table. For example, if you want to display the total sales for each customer, you could use this function to display this figure using the following expression: =SUMX(RELATEDTABLE('FactInternetSales'), 'FactInternetSales'[SalesAmount])
Note the usage of the new DAX function SUMX. It performs a summation of values, but unlike the SUM function which expects to receive a list of values, the SUMX function expects to receive a table as an input and an expression. Here’s how the new calculated column will look like:
-Yaniv
(This is post number 9 for week number 8 of my SQL blog challenge)
Continuing the PowerPivot series, I’ll now attempt at creating my first PowerPivot report. After opening Excel, first thing we need to do is getting ourselves a data set to work with. To do so, I’ll click on the “data” tab and choose “From Other Sources” button and connect to my local SQL Server instance, to the “AdventureWorksDW2008” database. Running through a familiar set of wizard screens (similar to the Excel 2007 wizard), where I’m prompted to choose a database, and get the option to choose a specific table or leave the connection “table-agnostic”. I’ll choose the option to be able to pick later which tables to use and the next screen prompts me to save the connection settings and choose a friendly name to it (similar to the option you may be familiar with when you create a DSV in Analysis Services). Note that you do not have to use data sources from an Excel sheet like this, you can directly connect to a data source from a PowerPivot window as well.
Next, I need to choose the tables I’ll be using to create the PowerPivot report with. I’m choosing DimDate, DimCustomer and FactInternetSales. Each table resides on a separate Excel sheet and this is how my Excel file looks like after this operation completes:
Now I need to get these tables into my PowerPivot report. I’m clicking on the “PowerPivot” tab and choosing the “Create Linked Table” button. Doing so for each of the three Excel sheets I’ve created will eventually link the three tables to a PowerPivot window where I could start creating my report.
Before I’ll create my report, I want to let PowerPivot know the relationships that exist between the three tables. I’m clicking on the “Table” tab and the “Create Relationship” button in order to define the relationships. Then I create the relationships between the two dimension tables (customers and date) and the fact table (Internet Sales).
Now we’re ready to create our first PowerPivot report. I click on the “PivotTable” button in the PowerPivot window and choose the “Single PivotTable” option. This opens up a new Excel window with the option to choose slicers, filters and values for my report:
I create a trivial report which sums order quantity by date and gender on the vertical and horizontal slicers, and add yearly income groupings as the row labels. The report looks like this:
I can choose to filter by a specific date, a date range, and the same goes for each slicer I picked to participate in the report. It all looks very user friendly and easy to use, yet has the new slicers feature which wasn’t there in the “old” Excel 2007 pivot table interface. So we have created a basic PowerPivot report, but haven’t even begun to scratch the surface of what PowerPivot is really all about. On the next few blog posts I’ll drill down further and look at the more advanced PowerPivot options, using the new DAX expression language and examine performance aspects of the product.
-Yaniv
(This is post number 8 for week number 7 of my SQL blog challenge)
With so much talk about PowerPivot, “BI for the masses”, collaboration and, of course, world peace, I decided I’ll finally give PowerPivot a test run and see what the hype is all about. Attending the PASS summit a couple of months ago, this was by far the most talked about feature in the upcoming SQL Server 2008 R2 and Office 2010 products and it seems Microsoft is really excited with this new add-in.
So, in the coming 2-3 blog posts I’ll try to describe how this tool works, and no better place to start with then the adventures of trying to get it installed in the first place.
You need to get Office 2010 installed, this is really the only way you can use Excel with PowerPivot. Excel 2007 does not support PowerPivot. You need to register for the Beta plan and download the installation file. I only installed the Excel component, even though I was curious to look at the new Word and PowerPoint, I did not want to diverge… Installation was painless!
Next I installed the PowerPivot for Excel Add-in. Double clicking the .msi file and going through a couple of wizard screens and we’re off – again, nothing special here, and more importantly, it simply goes smoothly.
Opening Excel after the add-in installation, you’ll be prompted and asked if you want to install the new add-in. By all means, I clicked “Install"…
All goes well, and a new tab, under the name “PowerPivot” is added to the Excel ribbon:
This is pretty much it! no hassles and really an easy installation process to get PowerPivot up and running on your local machine. Note that even though the PowerPivot feature is strongly coupled (in marketing terms) with the SQL Server 2008 R2 edition, you do not need SQL Server 2008 R2 installed to use PowerPivot. PowerPivot can connect to any data source and present data in an OLAP-like manner on Excel, as long as you use Excel 2010, that’s all you need. Things are different though if you want to share your PowerPivot reports and database with others, using SharePoint and/or Analysis Services. you then do need the SQL Server 2008 R2 edition in order to accomplish this.
Next post we’ll do some basic reporting using PowerPivot and see the magic in action.
-Yaniv
(This is post number 7 for week number 6 of my SQL blog challenge)