DAX : Building a monthly salesreport with estimation in PowerPivot (Part I)

Introduction 

In this blog post I would like show you step-by-step how to build a report in PowerPivot with DAX expressions. For my current customer I have to build a report that calculates the daily sales and a comparison with a budget. I've gathered the following requirements:

• Calculate monthly sales to determine how much of this product has been sold per company, day and month.
• Extrapolate monthly sales (estimation) and compare this with the budget.

The following restrictions are relevant here:

• The working days must be taken into consideration.
• Holidays must be taken in consideration.
• The targets are monthly based.

This blog post is based on the paragraph entitled "Working days" from the book "The definitive guide to DAX" by Marco Russo and Alberto Ferrari.

The datamodel

A proper data model is designed with a sales fact table and a calendar dimension table. Rob Collie refers to these as data lookup tables and  data tables, but I prefer dimension and fact tables.

Calendar dimension 

For designing and filling the calendar dimension I would like to use a previous blogpost of mine in which I discuss building a calendar dimension in Excel. For this blogpost, I haveextended this example with the following fields:

• DayInWeek : = WEEKDAY(B2)
• WorkingDay : = IF(OR(WEEKDAY(B2)=1, WEEKDAY(B2)= 7), "N", "Y")
• Holiday : Manually entered values Y for a holiday like Easter and N if not.
• MonthNumber : = MONTH(1&LEFT(G10,3))

Below is a snippet of the calendar dimension where I included the holidays Easter and King's day (in the Netherlands).

This example was created with Excel but you can also generate this in SQL Server with a stored procedure for instance. This I would normally prefer.

Sales sample data

For example, I have created some sales data in the workbook. I have gathered some data for two months - March and May - because there are some holidays in these months and therefore it's possible to test the holiday scenario.

In this example, there are products sold on weekends and weekdays.

The PowerPivot data model

The next step is building the PowerPivot datamodel. This can do done by using CTRL-T of the table in Excel. Then give the table a name and add this to the data model. 

Don't forget to mark the calendar table as a date table in order to use time intelligence functions.

Building the measures

Normally, the next step would be to build the explicit measure layer in PowerPivot. In this layer the data source and the rest of the DAX expressions are separated from each other. But, for the sake of this example, I will not do that. I have created the following DAX Measures:

NumOfDays:=COUNTROWS(Calendar)

NumOfWorkingDays:=CALCULATE([NumOfDays], Calendar[WorkingDay] = "Y")

NumOfWorkingDaysIncHolidays:=CALCULATE([NumOfWorkingDays], Calendar[Holiday] = "N")

SalesAmount:=SUMX(Sales, Sales[SalesQuantity] * Sales[Unitprice])

DailySales:=DIVIDE([SalesAmount], [NumOfDays])

WorkDailySales:=DIVIDE([SalesAmount], [NumOfWorkingDays])

HoliWorkDailySales:=DIVIDE([SalesAmount], [NumOfWorkingDaysIncHolidays])    
 

These measures make a distinction between days, workingdays and holidays.

Let's take a look at the pivot table. For sorting the months I added an extra column in the calendar dimension MonthNumber (=MONTH(1&LEFT(G10,3))) and G10 is the Month name. Nice trick.

For instance, April has 30 days, of  which  22 are workingdays. However, when the holidays are subtracted we can see that there are really only 20 days for labor.

Now, let's take a look at the SalesAmount in the pivot table:

I have set the settings of the PivotTable to show the rows even when there is no data for a month. Now you can see that the Grand Total of Daily Sales is much lower than the average for April and May. If we divide 416.68 (the total annual sales) by 365 days we get 1.14, but that is not right. Thus it's better to divide it by something else. We have to neglect the days there was no data in the sales table.

       
NumOfDays:=IF([SalesAmount] > 0, COUNTROWS(Calendar))
 

But nothing happens. It only clears the values if you have a larger calendar dimension than I have. Marco Russo and Alberto Ferrari call this a "granularity mismatch" in their book "The Definitive Guide to DAX". The numbers are accurate at the month level but inaccurate at the Grand Total level. The problem is how to determine the right granularity level. This is a business rule and it depends on how you build the pivot table in your report.

We have to use an iterator: SUMX and SUMX iterates over the values and sums them by a certain level of granularity. In my case I would like to use a monthly granularity.

DailySales:=DIVIDE([SalesAmount], SUMX(VALUES(Calendar[MonthName]), [NumOfDays]))

WorkDailySales:=DIVIDE([SalesAmount], SUMX(VALUES(Calendar[MonthName]),[NumOfWorkingDays]))

HoliWorkDailySales:=
DIVIDE([SalesAmount], SUMX(VALUES(Calendar[MonthName]),[NumOfWorkingDaysIncHolidays])) 
 

And this results in the following pivottable:

And I manually calculated the Grand Total of the Sales measures and the numbers are correct.

So where are we now?

In the beginning of this blog post I listed gathered some requirements. Let's see what we have done so far:

• Monthly sales. How much have we sold of this product or per company.
• Extrapolate of monthly sales and compare this to the target.
• Take the working days in consideration.
• Take the holidays in consideration.
• The targets are monthly based.

In the next section I would like to give some thoughts on extrapolation of the current month sales, This way we can determine whether the monthly sales targets and actual sales are on target.

Estimated Sales

For this reason, I have entered some extra data to the Sales table until 'today'.

Furthermore, I have added an extra column to the Calendar dimension, WorkingDayInMonth and this is a running number in the Calendar dimension that is raised by 1 of the day is a working day and not a holiday. Below is an example from the month December for which I have added some test data for the WorkingDayInMonth column.

By doing so, I can lookup the current working day that depends on the current day of the month. For instance, it's now December 14th and the current working day is 11. It is now possible to calculate the sales amount that is earned by the company and then calculate the extrapolated sales amount for the working days left in the current month.

I have added some extra measures to the Power Pivot workbook.

Today:=DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))

WorkingToday:=LOOKUPVALUE(Calendar[WorkingDayInMonth], Calendar[CalendarDate], Calendar[Today])

SalesEstimation:=IF(MONTH(NOW()) = MAX(Calendar[MonthNumber]), 
 DIVIDE(([SalesAmount] * [NumOfWorkingDaysIncHolidays]), [CurrentWorkingToday]),[SalesAmount]) 

The measure 'Today' is used by the other measure 'WorkingToday' for looking up in the Calendar dimension. The WorkingToday is the current working day in the current month. Perhaps you would like to limit this measure by the current month scope, but I will leave that up to you.

The measure SalesForecast calculates the estimated sales in the current month by using the following pseudo-formula:

EstimatedSales = SUM(SalesAmount/WorkingDaysSofar x NumOfWorkingDaysIncHolidays)

And this is the result of the calculation (I renamed SalesForecast to SalesEstimation):

At this point there is only one problem and that is the SalesEstimation on the Grand Total level.

In order to avoid confusion,  I added an extra IF to the measure :

SalesEstimation:=
IF(COUNTROWS(VALUES(Calendar[MonthNumber])) = 1, 
  IF(MONTH(NOW()) = MAX(Calendar[MonthNumber]), 
 DIVIDE(([SalesAmount] * [NumOfWorkingDaysIncHolidays]), [CurrentWorkingToday]),[SalesAmount]),
 BLANK()
)

Resulting in

Conclusion 

This blog post describes an implementation of a Daily Sales Report for a month with an estimation of sales amount in the current month.

Greetz
Hennie

Windows 10 : We couldn't update the system reserved partition

Introduction

Today, I thought it was a good idea for upgrading my laptop from Windows 7 to Windows 10. I neglected the upgrade pop up window for too long now. Let's do it! Well, that's not as easy as I expected upfront because of this error message: "We couldn't update the system reserved partition". I'm not sure but on some sites I've read that it could be the result of migration from HDD to SSD with the Samsung migration software.

In this blogpost I'll describe the problem, the solution and the conclusion.

The error message

In my particular case I recieved the following error: "We couldn't update the system reserved partition." and this is depicted below:

I opened Disk Management and I saw the following information:

In this screenshot the data volume is a System Primary Partition and I assumed that this one is the one with the problem. It's only 1% free and it's a system partition.

The solution

1. I installed the Minitool Partition Wizard 9.1 Free edition.

2. And launched the application:

And here you can see the Active & Boot partition. In my case it is named "Data" but I've also seen sites where the partition is named RECOVERY.

3. Resize the C partition in order to make some space free (I found out later that this not really needed. You can grab some free space from C:).

4. The result of this step.

5. Now, extend the system reserved partition to the desired amount.

And now the final result should be this (depicted below):

The system reserved partition is now enlarged to 300 MB.

6. The next step is to apply the settings

After some nail biting restarting, flickering and a progress bar that stops sometimes, the volume is enlarged to 300 MB.

And the installation of windows 10 succeeded without any problem.

Conclusion

I think that the migration from HDD to SSD of the Samsung software changed something in the reserved partition.

Greetz,

Hennie

DAX: Using DAXStudio for building DAX expressions

Introduction

I was wondering how I could improve building DAX expressions and one way to do that is by using DAX Studio. DAX Studio gives you a richer developer experience than the standard DAX expression editor in PowerPivot.

On the blog of Marco Russo on SQLBlog.com you can find more information about using DAX Studio for building DAX expressions.

Define Measure

In order to use this technique you have to use the following pattern to make it work:

1. Define the measure.
2. Use this measure in you calculation.

Here is an example:

  
DEFINE MEASURE Sales[MTDInvoicedSales] = CALCULATE([TotalInvoicedSales], DATESMTD(Calendar[Date]))
EVALUATE
SUMMARIZE (
Sales,
Calendar[YYYYMM],
"InvoicedSales", Sales[MTDInvoicedSales]
)
 

And below depicted in a printscreen:

Conclusion

Use DAX studio when you are developing DAX Expressions with PowerPivot in Excel.

Greetz,
Hennie

PowerPivot : Backing up your measures

Introduction

A small blogpost about backing up your DAX Measures of PowerPivot in Excel 2013. Although PowerPivot is a great tool, it can happen that PowerPivot crashes, for instance when the underlying data model changes. I do find this a problem and one way to avoid a lot of rework is by backing up your DAX Measures.

Back up your DAX measures

1. The first thing to do is downloading and install the add-in "DAX Studio". If you open Excel you can find the add in on the ADD-INS ribbon.

2.Connect to the WorkBook and choose PowerPivot Model (default) when you open the DAX Studio from Excel.

3.Query the MDSchema (you can find al kinds of DMV's on the DMV tab.

       
select MEASURE_NAME, EXPRESSION from $SYSTEM.MDSCHEMA_MEASURES
 

and a printscreen is depicted below:

4. Copy and paste the formulas into Excel
And can copy and paste the formulas in an excel sheet and you can use this also as documentation.

Conclusion

It's a good thing to back up your DAX measures when the underlying datamodel is likely to change. One another point is that you can adopt this method for documenting the worksheet.

Greetz,
Hennie

Hadoop : HDFS User Commands

Introduction

In this blogpost I'll describe some of HDFS commands in more detail that you can use to interact with the HDFS fielsystem. For this blogpost I've used Cloudera quickstart virtual machine and it's freely downloadable from the cloudera site. 

List files in HDFS

The command to list files in HDFS is ls. the command is pretty simple and it list all the files in Hadoop. It looks like this.

 hdfs dfs ls /      

And this will return the directory list of the rootfolder.

Make a directory

With mkdir it's possible to make a directory in Hadoop. Mkdir also looks pretty much on the mkdir in linux. Below an example of the command.

hdfs dfs -mkdir /user/test       
hdfs dfs -ls /user

And the result is:

Create a random local file

dd is a simple but a powerful tool to copy data from source to destination, block by block.

dd if=/dev/urandom of=sample.txt bs=64M count=16

Resulting in :

This creates a 1GB file sample.txt.

Put the file in HDFS

With the command put we can copy the file to the Hadoop filesystem.

hdfs dfs -put sample.txt /user/test

and when we take a look at the folder /user/test w'll see that file is put into this folder:

FSCK command

The system utility fsck stands for "File System Consistency Check" and is a tool for checking the consistency of a file system in HDFS. Let's try this:

hdfs fsck /user/test/sample.txt

And this results in :

HDFS administrator commands

dfsadmin gives a status of the datanodes

       
hdfs dfsadmin -report
 

Resulting in :

Conclusion

This is a small introduction into some of the commands of HDFS that you can use in your day to day work with Hadoop

Greetz,

Hennie

SQL : Sharing tables among different databases (Cross-Database Ownership Chaining)

Introduction

In a former blogpost of mine, I've described the working of ownership chaining, but this works only within a database and not between databases. In case you have multiple database, a more sophisticated approach is needed. There are several options like using TRUSTHWORTHY or certification of database objects, but I've focused on the option Cross-Database Ownership Chain in this blogpost. The reason I want to use this, is because I've several databases for a datawarehouse and I want to configure the security of some data in a so-called meta database and I don't want to let the end users looking in this 'system' database. That's why I'm investigating Cross-database Ownership Chaining.

Cross-Database Ownership Chains

As seen on SQLServerCentral Cross-Database Ownership chains are an extension of ownership chains where the objects calling and the called object resides in the same database. The only difference is that cross-database ownership crosses database boundaries. In my scenario, I evaluate the current user with USER_NAME() in a view against a table in an other database. My situation would be something like this.  

Let's see what happens when we try to query a view in a database that references a table in another database. We use the same script as in my former post about ownership chains and adjust this to a script where we have two databases. Now, let's test cross-database Ownership chaining. Let's start simple and see whether the following situation works.

Preparation

Here are the preparation scripts that I've used for testing the different scenarios. So again I've a View vwBla in CrossDatabase A that queries a table in CrossDatabaseB.

-----------------------------------------------------------------------
-- 1. Create the necessary logins.
USE [master]
GO

DROP LOGIN [WIN-QTLB6QR5HC9\TestUserA]
GO

CREATE LOGIN [WIN-QTLB6QR5HC9\TestUserA] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

-----------------------------------------------------------------------
-- 2. Create database
USE master
GO 

DROP DATABASE [CrossDatabaseA]
GO 

CREATE DATABASE [CrossDatabaseA]
GO

DROP DATABASE [CrossDatabaseB]
GO 

CREATE DATABASE [CrossDatabaseB]
GO
-----------------------------------------------------------------------
-- 3. Create the table.

USE [CrossDatabaseB]
GO

/*
DROP TABLE [dbo].[tblBla]
GO
*/

CREATE TABLE [dbo].[tblBla](
 [bla] [nchar](10) NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[tblBla]
           ([bla])
     VALUES
           ('blaaaaaaa')
GO
-----------------------------------------------------------------------
-- 4. Create the view.
USE [CrossDatabaseA]
GO

/*
DROP VIEW [dbo].[vwBla]
GO
*/

CREATE VIEW  [dbo].[vwBla] AS 
SELECT * FROM CrossDatabaseB.dbo.tblBla
GO

-----------------------------------------------------------------------
-- 5. Setup the users.
USE [CrossDatabaseA]
GO

CREATE USER [WIN-QTLB6QR5HC9\TestUserA] FROM LOGIN [WIN-QTLB6QR5HC9\TestUserA]
GO

CREATE USER [WIN-QTLB6QR5HC9\TestUserB] FROM LOGIN [WIN-QTLB6QR5HC9\TestUserB]
GO

GRANT SELECT ON [vwBla] TO [WIN-QTLB6QR5HC9\TestUserA]
GO


USE [CrossDatabaseB]
GO

CREATE USER [WIN-QTLB6QR5HC9\TestUserA] FROM LOGIN [WIN-QTLB6QR5HC9\TestUserA]
GO
 

Now I'm doubting about that I've to give access to the CrossDatabaseB in order to make this work and I don't really want this (in my real world situation). I don't want to even want the enduser know there is a CrossDatabaseB. But I haven't seen and found a solution with Cross Database Ownership chain that this supports. Please leave a comment below if you know how.

1. Test the initial setup for TestUserA

In this scenario I've tested whether the TestUserA has access to the View and the underlying table (that resides in the other database).

-----------------------------------------------------------------------
-- 1. Test the setup for TestUserA
USE [CrossDatabaseA]
GO

EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserA';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM CrossDatabaseB.dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

And this results in :

Msg 916, Level 14, State 1, Line 2
The server principal "WIN-QTLB6QR5HC9\TestUserA" is not able to access the database "CrossDatabaseB" under the current security context.
Msg 916, Level 14, State 1, Line 2
The server principal "WIN-QTLB6QR5HC9\TestUserA" is not able to access the database "CrossDatabaseB" under the current security context.

Well, this result is different from the test I've done in the former blogpost. So, the ownership chaining does not work anymore, although the same user(?) is present in both databases. In order to make sure that it really does not work I tried to get the data with PowerPivot. And it does also not work!

And in PowerPivot an error happens:

What could be the problem that causes this error? Well, I haven't found a real reason than that you should turn on cross-database ownership on. Okay, well let's do that. Off course, the best practice is to turn it on, on database level and not on server level because on server level every database is ownership crossed.

ALTER DATABASE [CrossDatabaseA] SET DB_CHAINING ON;
ALTER DATABASE [CrossDatabaseB] SET DB_CHAINING ON;

Let's test some scenario's further...

2. Test now with cross-database ownership on

Okay, the same test as with did earlier in this blogpost, but now with cross-database ownership on.

USE [CrossDatabaseA]
GO

EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserA';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM CrossDatabaseB.dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

And this surprises me:

Msg 916, Level 14, State 1, Line 2
The server principal "WIN-QTLB6QR5HC9\TestUserA" is not able to access the database "CrossDatabaseB" under the current security context.
Msg 916, Level 14, State 1, Line 2
The server principal "WIN-QTLB6QR5HC9\TestUserA" is not able to access the database "CrossDatabaseB" under the current security context.

But when I run Excel/Powerpivot as a different user it works(?!):

So, there something going on with the statement "EXECUTE AS USER" in SQL Server. It differs from the "Execute as different user" in Windows. I should investigate this further but I don't have time to do this. If someone has answer to this, please add an comment under this blog. Okay, let's try another thing and run the script for TestUserB in SQL server and to make sure, run PowerPivot as another user.

3. Test the lab situation with another user

In this scenario I'll test the cross database ownership by using another TestUser, TestUserB and sees what happens with the security. What does TestUserB see?

-----------------------------------------------------------------------
-- 2. Test the setup for TestUserB
USE [CrossDatabaseA]
GO

EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserB';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM CrossDatabaseB.dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

And this result in the following error messages:

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'vwBla', database 'CrossDatabaseA', schema 'dbo'.
Msg 916, Level 14, State 1, Line 2
The server principal "WIN-QTLB6QR5HC9\TestUserB" is not able to access the database "CrossDatabaseB" under the current security context.

And with "Run as an different user" option:

Okay, this seems to be working.

4. Test the setup with the guest user

I've read on  mssqltips.com you can give access to users to a database with the guest user with Cross Database Ownership. Now when I try this I don't seem to get it working as I would believe it should be working. This is was I tried (got this from blog of nabeelshahid).

USE [CrossDatabaseB]
GO
EXEC sp_grantdbaccess 'GUEST'
GO
GRANT CONNECT to GUEST
GO

GRANT SELECT ON [tblBla] to GUEST
GO

And running the script again for user TestUserA gives the follwoing results :

The Guest user is granted CONNECT and SELECT rights on the CrossDatabaseB where the table resides and when I query the view and the table the TestUser gets all the data from the view and from the table in the other database.  On Microsoft site I find this information about the guest user:

"In SQL Server, a special user, guest, exists to permit access to a database for logins that are not mapped to a specific database user. Because any login can use the database through the guest user, it is suggested that the guest user not be enabled."

So, it seems not a best practice to use the guest user for granting right to a database if you want shield the table from viewing the data from unwanted users. I don't understand why this is suggested by the different sites.

Conclusion

In this blogpost I've described cross-database ownership and although I recieved some unexpected results I've a basis to work from and to extend in my real world situation.

Greetz,

Hennie

SQL : Select data from a view and secure the table (Ownership Chains)

Introduction

If you want to hide tables and you want to let the user select the data with a view in SQL Server, you can apply Ownership Chains. From Technet we learn that Ownership Chains are multiple database objects that are sequentially accessed. The ownership chains are maintained by using the same owner in the referenced objects. For example, When a view that references a table has the same owner and you grant rights only to the view to another user than the owner, the data is still shown, though the user has not explicit rights on the referenced table.

In the example above you see three objects, Object1, Object2 and Object3. When an object is accessed (eg. Object2) the owner of the current object (eg. Object2) is compared with the owner of the calling object (eg. Object1). If they are equal, the permissions on the referenced object (eg. Object2) are not evaluated (!). The ownership chain remains unbroken. Suppose that the owner of Object3 has another owner, then the ownership chain is broken and an error will happen.

Ownership chain

So the basis for Ownership chaining is that all objects has the same owner and the owner controls the access of the objects. This gives an administrator a very flexible and robust security scheme. I've worked out an example to understand this principle better. Below an example of a view that references a table. Just an simple example to understand this concept.

In the script below I've tested the different scenario's for a thorough understanding of this concept. I've create a table (tblBla) and a view (vwBla) in a database (OwnershipChainingDB). The view vwBla references the table tblBla.

The following preparation steps are taken:

1. Create the necessary logins.
2. Create the database.
3. Create the table.
4. Create the view.
5. Setup the users.

-----------------------------------------------------------------------
-- 1. Create the necessary logins.
USE [master]
GO

DROP LOGIN [WIN-QTLB6QR5HC9\TestUserA]
GO

CREATE LOGIN [WIN-QTLB6QR5HC9\TestUserA] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

-----------------------------------------------------------------------
-- 2. Create database
USE master
GO 

DROP DATABASE [OwnershipChainingDB]
GO 

CREATE DATABASE [OwnershipChainingDB]
GO

-----------------------------------------------------------------------
-- 3. Create the table.

USE [OwnershipChainingDB]
GO

/*
DROP TABLE [dbo].[tblBla]
GO
*/

CREATE TABLE [dbo].[tblBla](
 [bla] [nchar](10) NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[tblBla]
           ([bla])
     VALUES
           ('blaaaaaaa')
GO
-----------------------------------------------------------------------
-- 4. Create the view.
USE [OwnershipChainingDB]
GO

/*
DROP VIEW [dbo].[vwBla]
GO
*/

CREATE VIEW  [dbo].[vwBla] AS 
SELECT * FROM dbo.tblBla
GO

-----------------------------------------------------------------------
-- 5. Setup the users.
USE [OwnershipChainingDB]
GO

CREATE USER [WIN-QTLB6QR5HC9\TestUserA] FROM LOGIN [WIN-QTLB6QR5HC9\TestUserA]
GO

CREATE USER [WIN-QTLB6QR5HC9\TestUserB] FROM LOGIN [WIN-QTLB6QR5HC9\TestUserB]
GO

GRANT SELECT ON [vwBla] TO [WIN-QTLB6QR5HC9\TestUserA]
GO
 

The following testsituations are considerated :

1. Test the setup for TestUserA.
2. Test the setup for TestUserB,
3. Test what happens when we break the owner ship chain for TestUserA.
4. Test again what happens for TestUserB.
5. Change the ownership of the other object too.
6. GRANT SELECT for TestUserA

1. Test the setup for TestUserA
In this scenario I am testing the access of TestUserA to the view and the table.

-----------------------------------------------------------------------
-- 1. Test the setup for TestUserA
USE [OwnershipChainingDB]
GO

EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserA';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

The result is :

and

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'tblBla', database 'OwnershipChainingDB', schema 'dbo'.

So, TestUserA has rights to select the data via the view (and therefore the underlying table) but has not direct access rights to the table.


2. Test the setup for TestUserB.
Okay, what happens when TestUserB tries to access the view and the table:

-----------------------------------------------------------------------
-- 2. Test the setup for TestUserB

EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserB';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

SELECT User_name()

and this results in :

and

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'vwBla', database 'OwnershipChainingDB', schema 'dbo'.
Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'tblBla', database 'OwnershipChainingDB', schema 'dbo'.

This means that TestUserB does not have any rights on the view and no rights on the table.


3. Test what happens when we break the owner ship chain for TestUserA.
In this testsituation we have changed the owner of tblBla to TestUserB. So the ownership chain has broken.

USE [OwnershipChainingDB]
GO

ALTER AUTHORIZATION ON [tblBla] TO [WIN-QTLB6QR5HC9\TestUserB]
GO

EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserA';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

and this results in :

and

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'tblBla', database 'OwnershipChainingDB', schema 'dbo'.
Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'tblBla', database 'OwnershipChainingDB', schema 'dbo'.

The Ownership chain has broken. You can also check this with the script:

 USE [OwnershipChainingDB];
GO
SELECT 'OBJECT' AS entity_type
    ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
    ,name 
FROM sys.objects WHERE SCHEMA_NAME(schema_id) = 'dbo'

resulting in :

4. Test again what happens for TestUserB.

In this scenario we test the changed ownership of TestUserB. The owner of table tblBla has changed from TestUserA to TestUserB and let's see what happens to TestUserB.

-----------------------------------------------------------------------
-- 4. Test again the setup for TestUserB
EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserB';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

and this results in :

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'vwBla', database 'OwnershipChainingDB', schema 'dbo'.

In this scenario we see that the user TestUserB has no rights on the view.


5. Change the ownership of the other object (vwBla) too.
In this scenario we change the ownership of the view vwBla to TestUserB. Now the complete chain ownership has changed from TestUserA to TestUserB.

 -----------------------------------------------------------------------
-- 5. Test again the setup for TestUserB

ALTER AUTHORIZATION ON [vwBla] TO [WIN-QTLB6QR5HC9\TestUserB]
GO

EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserB';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

and this results in :

And the ownership is changed to TestUserB.

6.  GRANT SELECT for TestUserA
And now a final test for the user TestUserA and that is that we give GRANT SELECT rights on the view and not on the underlying table.

 -----------------------------------------------------------------------
-- 6. GRANT SELECT for TestUserA
USE [OwnershipChainingDB]
GO

GRANT SELECT ON [vwBla] TO [WIN-QTLB6QR5HC9\TestUserA]
GO

EXECUTE AS USER = 'WIN-QTLB6QR5HC9\TestUserA';
GO

SELECT * FROM dbo.[vwBla]
GO

SELECT * FROM dbo.[tblBla]
GO

SELECT User_name()

REVERT;
GO

And this results in :

And a SELECT permission error occurs, because TestUserA can retrieve data via the ownership chain (view, table) but not directly on the table.

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'tblBla', database 'OwnershipChainingDB', schema 'dbo'.

Conclusion

In this blogpost I've investigated different scenarios in order to understand the ownership chain better. I hope that you also enjoy reading this blogpost as I did investigating and writing about it.

Greetz,
Hennie 

DAX : Leading zeros in DAX

Introduction

According to Wikipedia : "A leading zero is any 0 digit that comes before the first nonzero digit in a number string in positional notation.For example, James Bond's famous identifier, 007, has two leading zeros". In DAX, there is a difference between handling leading zeros when the datatype is number or it is text. You can solve this by using FORMAT and the REPL function. This is very often used in cases when the type of field of a certain sourcesystem is integer or a field that contains values without leading zeros and one field that comes from another system with leading zeros. Now, you could cast the text column to integer but that is not always possible because of dataquality issues. For instance, there are characters in the column and that is not supposed to be. Then, you have convert the other column to a column with leading zeros.

Leading zero

In case of a number field you can use the FORMAT function, this functions turns a number into a text column in PowerPivot. But you can't use the FORMAT function in case of textcolumn, nothing happens, as shown in the column "FormatonTextDoesNotWork" in the screenshot below.

This is the DAX expression that does not work on text columns:

FormatonTextDoesNotWork:=FORMAT(Table1[Number];"0000000")

So in case of a text column you can use the REPL function. This example is based on a blog of Kasper de Jonge. 

This is the DAX expression that works based on a textcolumn:

=REPT("0";10-LEN(Table1[CodeInText])) & Table1[CodeInText]

Conclusion

This is a small blog about a simple experiment with leading zeros in DAX that can be solved in different ways depending on the type of the column.

Greetz,

Hennie