BI Administration: The Business Intelligence Management Data Mart

Firstly, I’d like to thank everyone who attended my session this morning for Pragmatic Works Training on the T’s. I always enjoy presenting, Today’s topic was about management and administration of business intelligence solutions. The theme of the overall presentation was that although there are many ways to collect the meta data around our business intelligence solution, the data itself still lends itself to a dimensional structure, that we can use to model into various facts and dimensions. Below is a data model that represents performance between ssis, ssas, and ssrs. These are key performance metrics surrounding these 3 development tools. As developers/administrators, it is our responsibility to ensure that we maintain and monitor this information to adhere to SLA’s and ensure timely performance. A overall diagram of the architecture would look like the below:

 

image

 

A complete recording of the webcast is available here, and the complete solution and database backup is available here.

Power Pivot: Adding tooltips to your Power View report

In Excel 2013, did you know that you can make descriptions available to users as they’re browsing the model in Power View? I recently discovered this, and thought it was a really nice feature that you can use to add some extensibility to your Power View reports. In this blog post, I’d like to walk through and show how easy it is to get this set up for use in your environment.

 

Let’s start with a blank Excel workbook, and open up the Power Pivot window:

 

image

 

 

Let’s import some data into Power Pivot. For this example, I’m just going to import in the Internet Sales table from Adventure Works:

 

image

 

Now that it is loaded, find the UnitPrice column and right click on it. Look for the Description field.

image

 

Select it, and the Description dialog window will appear. Type in “The price per unit that was charged.” and click OK. That’s it! Now let’s see it in action. Switch back over to excel view, and insert a Power View sheet.

 

image

 

Expand the Internet Sales table, and hover over the Unit Price column that you added the description to.

 

image

 

Your description now shows as a tooltip for report users to navigate and get additional information around. What’s even better, is this works exactly the same way in a tabular database when you’re building it. To demonstrate, open up the Adventure Works tabular database in 2012, and find the Internet Sales table again. Find the Unit Price column, right click and go to description.

 

image

 

In description, type in “The price per unit that was charged.” and click OK. Then build and deploy your solution. After it finishes deploying, open up Excel and connect to your tabular database.

 

image

 

After you click Finish, select Power View report under the Import Data dialog box and click Ok.

 

image

 

After your Power View sheet, open up the table list and find the Unit Price column again. Notice that your description can be implemented as a tooltip this way too.

 

image

 

 

That’s all for now! Short blog post, but very useful!

SSAS Clearing the cache

In SSAS, when you’re trying to do performance tests, it’s important to know what performance is from the cold cache. You definitely don’t want a warm cache messing with your measures that you’re trying to get metrics on! An important item of consideration is that SSAS is NOT like the database engine side of SQL Server, in that you run one command and you’re done. Since SSAS has 3 separate distinct parts (the formula engine, the storage engine, and the file system) you want to ensure that you clear the cache in all 3 places in order to get a clean run.

Clearing the formula engine and the storage engine is pretty simple, just open up an XMLA query window and run the following command: (I had some issues getting wordpress to take the “object” tag, so be sure to insert the “<” back in prior to running. Sorry!)

<ClearCache xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;

Object>

<DatabaseID>Your Database Name Here</DatabaseID>

/Object>

</ClearCache>

In addition to this, there is also the small matter of the actual physical files that are created for the cache. Just running the above is going to clear out half of your cache, but remember that since Analysis Services creates the files physically, just clearing out the logical layer isn’t going to get us the right results. Luckily, we have an app codeplex project for that! It’s an AS Stored Procedure project located here. Download it, install the Assembly into Analysis Services, and then run your Analysis Services mdx script like normal!

Once you’ve registered the assembly, the actual script is below. Each of these calls needs to be executed separately.

//checks the size of the Windows File System Cache

call ASSP.GetFileSystemCacheBytes()

Running this should yield something that looks like the below:

image

//clears the size of the Windows File System Cache

call ASSP.ClearFileSystemCache()

And you’ll get a window that looks like the below:

image

Sorry, nothing exciting here. :) Just a note that the command completed successfully.

Now let’s run our first command again:

//checks the size of the Windows File System Cache

call ASSP.GetFileSystemCacheBytes()

Our new results windows looks like:

image

Notice how much of an improvement we gained. This was just using my local multidimensional Adventure Works project. An important item to note is that the asstoredprocedures codeplex project is not offically supported by Microsoft, as it’s a codeplex project. The only other alternative that I know of that you could use to clear the file system cache is to use the fsutil.exe utility, which is supported by Microsoft. However, this utility requires that 1) Analysis Services is on it’s own separate drive and mountpoint and 2) you would have to run it outside of regular business hours, as the utility causes disruption to anyone currently using the cube. In my opinion, this assembly with the right command does the exact same thing and with a much less hassle and no un-mounting required.

So in conclusion, when clearing the cache in Analysis Services, keep in mind that you need to clear it everywhere, not just the formula and storage engine.

Dynamic Security in Tabular database

Tabular databases, like multidimensional, have the ability to create roles for additional security against our databases for individual business users. What’s more, it’s even easier to set up. Like other things in tabular, gone are the 9 or 10 different tabs, basic/advanced toggling etc. Roles in tabular and DAX use two functions to establish and manage security: the USERNAME DAX function and the LOOKUPVALUE DAX function. Lookupvalue is quickly becoming one of my favorite DAX functions, but that’s a different story. :) Something important to consider when

First we’re going to go into our database and add a table that we can use to map to what we’re securing by. For this example, I’m going to map between the employee dimension and the sales territory dimension. To keep it simple, though, I’m going to list out the employee names, rather than having a EmployeeKey/Sales Territory key bridge table. Start by creating a table called Dynamic Security with the employeekey, names, and userlogon fields. My script is below:

CREATE TABLE dbo.UserSecurity
    ([EmployeeKey] [int] IDENTITY(1,1) NOT NULL,
    [SalesTerritoryKey] [int] NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [UserName] [varchar](50) NULL,
    )
GO

 

Then let’s input some values into it.

INSERT INTO [dbo].[UserSecurity]
VALUES    (1, ‘Administrator’,”,’DATA0\administrator’)
INSERT INTO [dbo].[UserSecurity]
VALUES    (2, ‘Administrator’,”,’DATA0\administrator’)
INSERT INTO [dbo].[UserSecurity]
VALUES    (3, ‘Bob’,’Williams’,’DATA0\bob’)
INSERT INTO [dbo].[UserSecurity]
VALUES    (4, ‘Steve’,’Smith’,’DATA0\steve’)

 

Now that we have our table created, we can do the actual security implementation. Open up the tabular database that you wish to apply the security too, and import your table. We’ll select and open our existing connection:

image

 

Then select the user security table that we created in Management studio and click Finish.

image

 

Once it’s imported, we then want to join it to the appropriate tables so we can relate to our security table. For this example we’re just joining to the Sales Territory. A snapshot of this piece of the model looks like:

image

 

Right click on the User Security table and click “Hide from Client Tools”. Now we can create our role. Scroll up to the top and click on Roles: (screenshot below if you need it)

image

 

Create a New Role, and give it a name. In this case, I’m calling it “Sales Territory Users”, giving read access under the permissions. Once you’ve done that, click on the Members tab, and add the users we have in our table. Below is a screenshot of what mine looks like with the 3 members I have in my table:

 

image

Go back to the Row Filters tab, and find the User Security table listed. Under the DAX Filter column, type in the following:

= FALSE()

Now find the Sales Territory table, and type in the following formula:

=’Sales Territory’[SalesTerritoryKey]=LOOKUPVALUE(UserSecurity[SalesTerritoryKey], ‘UserSecurity’[UserName], USERNAME(), UserSecurity[SalesTerritoryKey], ‘Sales Territory’[SalesTerritoryKey])

What this formula does is to say, “for each value in the UserSecurity[SalesTerritoryKey] column, find the current logged in Windows user name, and match it to the UserSecurity[SalesTerritoryKey] column. And only show rows where they match”

 

My Row FIlters section looks like the following:

 

image

 

Click Ok, and it will validate and finish your changes. Then go up to “Analyze in Excel”. Select “Other Windows user” and pick one of the users you have in your list. For this example, I picked Bob:

 

image

 

Click Ok, and then drag over your sales territory in the pivot table that appears. Drag over a measure so you get some data, and WHAMMY! some dynamic security awesome-ness:

 

image

Tabular Hierarchies Across Multiple Tables

Recently I was looking at the Adventure Works database and I noticed how Product, ProductCategory, and ProductSubCategory are their own separate tables. It got me to thinking about how someone might want to take attributes from each of those tables and create one hierarchy for reporting when you are looking at it. In DAX, this can be accomplished by using the RELATED() function. By using the related function in our table with our lowest level attributes, we can bring in the higher levels and create our hierarchy. This is really useful, especially if our data source isn’t necessarily a data warehouse and may be something more transactional with many tables split all over the place.

 

The Related function excepts one argument, which is the name of the table/column that we want to pull back. Let’s start with a simple diagram of our 3 tables:

 

image

 

Since our Product dimension is our lowest level of granularity of these attributes, what we want to do is bring the Product SubCategory and Product Category fields into the product dimension. Change your view to the data view in the product dimension and add a calculated column to pull in the subcategory:

=RELATED(DimProductSubcategory[EnglishProductSubcategoryName])

You can see our RELATED function at work. What we’re telling the function to do is to find the relationship that exists between product and product subcategory, and bring back the product sub category name. Once you click enter, rename the column to sub category name. Next we want to bring in the product category. Add a new calculated column, only this time our RELATED function is going to point 1 column farther, into the Product Category table. The RELATED function is really useful for this kind of analysis, especially since it can traverse multiple tables. For any old school excel pros out there, take that vlookup! Below is our new column with both Product Sub Category and Product Category added:

image

 

Hide your key column attribute, and then go to diagram view and hide your subcategory and category tables. To hide the entire table, right click on the table itself and select hide from client tools. Create a new hierarchy in your dimension where you added your related columns to, and added the category and subcategory to them. The final design looks like the below:

 

image

 

And that’s it! Now we have all of our related attributes in one table that we can create hierarchies off of. This is very useful for multiple snowflakes in a warehouse or transactional databases.

Discretization Buckets in Tabular

A little while ago I blogged about how to created discretization buckets in a multi-dimensional cube. The nice thing about it is that it was just a few properties that you needed to modify, and was pretty straightforward. To the flip side of that I see a lot of companies wanting to take advantage of the new tabular Analysis Services model and the performance you get from it. The good news is that you can do the same thing in tabular, but they’re not properties you set. To set it up, first you’ll need to identify what is going to create your buckets. Are you going to use a linked table? SQL view? Or maybe just a hard coded SQL Select statement. For this example, I’m going to assume that the business user wants the ability to change them easily, so I’m going to use a linked table. I’m also using the Adventure Works 2012 tabular demo project. One way to approach this dilemma is that you could just create an if statement in the table that groups your items for you, but what if a business user wants to change the values dynamically? Re-writing your calculation every time would be difficult, and frustrating to the end user. They want the ability to modify it as business needs change.

 

First, create your table in excel. When you’re done, save it and give a name. It should look something like:

 

image

 

You can see from the picture, nothing fancy. Just a quick table, took me longer to write this sentence than create it. :)

 

Then go into SSDT, and in your project select “Import from Data Source.” If you created a view in your source or are pulling your table from another source, this is where you would configure it. Assuming you’re using the Excel table, select Excel File from under the text files options:

 

image 

 

Click Next, and navigate to the file path from your earlier. Make sure to give your imported table a friendly name, as it will just name “Excel” by default:

 

image 

 

Click Next, and then click Finish. It should import your table in a few seconds. After you’ve imported it, and you view the model in diagram view, it should not be related to anything. This is OK, as there isn’t anything to join it to, as there are no columns in any of the tables that would join to it. The calculation we’re going to write here in a minute is going to give us what we’re looking for. :)

 

image

 

In order to solve this and give us our proper values, we’re going to take the values from the banding table and bring it directly into our Fact Internet Sale table.  Going to the Internet Sales table, we’re going to create a calculated column (the filter context) with the following formula:

=CALCULATE(VALUES(‘Banding Matrix’[Band]),’Banding Matrix’[High Value] >= EARLIER(‘Internet Sales’[Unit Price]),’Banding Matrix’[Low Value] < EARLIER(‘Internet Sales’[Unit Price]))

What this calculation is doing is taking the value (the name) of the pricing band that we’ve specified where the value of the unit price column is between the low and the high amounts we’ve specified. But this formula has 1 problem. Since our key business requirement here was that users can input the band values themselves, what if one the values in our fact table is missed? What we really need to do is format this in a way where we can protect the formula against an error. Using the IFERROR() function can help us here. So a better formula is:

 

=CALCULATE(IFERROR(VALUES(‘Banding Matrix’[Band]), "No Band Found"),’Banding Matrix’[High Value] >= EARLIER(‘Internet Sales’[Unit Price]),’Banding Matrix’[Low Value] < EARLIER(‘Internet Sales’[Unit Price]))

Now we are well protected and business users are happy. :) Before you finish, click on the Banding configuration table tab and select “Hide from client tools”. Preview the model in Excel, pick any measure that you want out of the fact table, and select your new price banding matrix. The view below is showing me total sales amount by unit price group (band).

 

image

 

Now if a user wants to change the values for their groupings or just wants to know what they are, they have the control. There is no dependency on you or anyone else in IT to change it for them. Viva La Self Service BI Revolution!

Tabular Databases: The workspace server was not found

Sometimes, due to environment changes or some other reason, you want to change the workspace database that you are developing your tabular model against. I recently had a need to do this as my old tabular database instance was no longer active. When I went to open up the bim file, I received the following error message:

 

image

 

To fix it, click on the bim file once to select it and then go to properties (F4). The last property on the page is called “Workspace Server” and specifies where the workspace database server is. Change this field to the name of your new server and then the following confirmation screen will appear:

 

image

 

Simply click “Apply Change” and you’re done. Short and simple.

Partitions in Tabular

Continuing my tabular kick that I’ve been on lately, tabular databases allow partitioning of tables to break down larger fact tables into smaller pieces for the engine to manage. This can be very beneficial for us, especially if we have large groups of data that don’t change much from day to day. It is very wasteful to reprocess something if it’s never going to change, and creates a lot of unneeded pressure on the server to reprocess something. We can alter the table to only process current data when needed. To access the partitioning dialogue box, there are 2 ways that it can be accessed. The first is through the menu bar, by going to Table->Partitions, but an easier way (one less mouse click) is to just click on the partitions button from the main design screen, located towards the top left corner (I’ve circled it in this diagram)

 

image

 

After we click it, we get an options box that looks like the below:

image

At the top we have a table selector, that we can use to select which table we want to partition. For this, I’m going to use the classic FactInternetSales table in Adventure Works 2012 DW. Directly underneath that is a list of the current partitions that are associated with that table. We are going to split the 1 existing partition on this table into 2, 1 for orders before 2006 and then another one for everything after. To start, we’re going to modify the existing partition by changing it from being table bound (as it is by default) to being query-bound. To do this, on the right hand side about half-way down you’ll see the toggle, as indicated by the icons in red  below:

 

image

Click on the SQL icon, and you will get the query window. Assuming you are not importing every column from that table into your model (which you shouldn’t be anyway), you will have a query that lists out all of the columns, ending with the FROM clause. Here, we’re just hard-coding to before a certain date, basing it on the order date, so our where clause would be WHERE OrderDateKey < 20051231. Give the partition a new name, and that will do it for the first one.

 

After we’ve modified the query for the original table, click on New at the top underneath the partitions list. Click New to add a new partition to the list, and click down underneath in the SQL view. Change your less than sign to a greater than sign, and then give it a new name. Clicking on either partition allows to toggle between them and check your work. You’re final partitions list should look like the below:

 

image 

Click Ok, do a complete Deploy and Process, and you’re ready to go.

Creating KPI’s for your tabular SSAS Database

Tabular databases give us the ability to create KPI’s for business metrics, which can be used to visually represent performance of a business metric of actual performance to target for quick display to a business unit or manager. If you’ve created KPI’s in Power Pivot, creating KPI’s in a tabular project is very similar. To create our KPI, we’re going to need at least 2 different pieces of information: what our actual performance is and what our target performance is. Most of the time target performance is defined by comparing this time period to last year (or the same time period within last year). This tells us if our business is doing better than it was a year ago, or worse. Let’s start with our Adventure Works tabular database by creating 2 measures: the sum of our internet sales, and the sum of out internet sales last year. On a side note, make sure your date dimension is marked as the date dimension, or your time intelligence function won’t work.

 

Our internet sales measure looks like:

Sales:=SUM(‘Internet Sales’[SalesAmount])

Wow, that was easy. Next is our Sales for last year.

LastYearSales:=CALCULATE(SUM(‘Internet Sales’[SalesAmount]), DATEADD(‘Date’[FullDateAlternateKey], -1, YEAR))

Now we have the 2 measures that we need. Right click on the first measure we created, about sales, and select “Create KPI”

image

 

Once the screen opens, you’ll then get the KPI screen, which looks like:

image

 

First we want to make sure that the name of our measure is the value. This is the value at the very top of the screen. Under the “Define Target Value” section, we’re selecting what we’re measuring against. In this case, it’s the measure called “Last Year Sales.” Additionally, if we wanted to measure our performance against an absolute value “like “10 million dollars in sales” we would just select the absolute value radio button and hard code the value in the field.

 

image

 

Underneath is where we specify what percentage to the target value we want to measure to. Just drag the slider bar until it is at the desired percentage of target that you want to see. Select the icons you want to use, and then click ok. Tabular will process it for a second and then indicate on the screen next to the measure that there is a KPI now associated with it.

image

 

And that’s it. Super simple to set up, visually super cool.

Perspectives in Tabular

A really nifty feature that I personally have always liked in traditional SSAS multi-dimensional models are perspectives. Perspectives are subsets of the cube that can be used to just show certain portions of it. (like views in the transactional world) If you have a large cube of multiple measure groups, it sometimes makes sense to limit what is viewed by a business user at a certain time. After all, if I’m reporting off of a cube and only need 1 small piece of information out of a measure group, why pull back everything? Tabular databases offer this same functionality. In the Adventure Works tabular database I’m currently working in, I have two “measure groups”, consisting of Internet Sales and Reseller Sales. In this example, I am going to create a perspective so when I am connecting to my model in excel I can only select internet sales if that’s what I’m reporting on:

 

I start by going up to Model on the menu bar, and selecting “Perspectives”

image

 

Once I click on Perspectives, I get the Perspectives window, which looks like:

 

image

 

Clicking on “New Perspective”, I get a new column which enables me to give my perspective a name and then select which tables or fields I want to see. In this example, I only want to see the Internet Sales, date, and Sales Territory tables:

image

 

Then I click Ok and reprocess. Now when I connect in excel I can see just my perspective as an option:

 

image

 

Pretty cool!