Search This Blog

Loading...

Sunday, August 10, 2014

The 'KeyColumns' #0 has NullProcessing set to 'UnknownMember', but the dimension doesn't have UnknownMember set to 'Visible' or 'Hidden'

SQL Server 2005-2014

 

By default, SSAS provides an automatically created member with the name ‘unknown’ for each dimension. This member is intended to be the home for all facts that don’t fit to a real member (provided from the data source). In the example above, fact data that does not match any of the listed product categories could be mapped to the unknown-element.

image

I’m saying ‘could’ and not ‘is’ because the rules for mapping fact data to the unknown-element can be configured in the dimension properties.

But using this mechanism has certain drawbacks:

  • Processing time increases a lot if one row is encountered which has to be mapped to unknown
  • Only one text (for example ‘unknown’, can be configured) for all attributes in the dimension
  • Cases are hard to find since you don’t see this mapping in the underlying data warehouse tables

 

In a good data warehouse design, ETL takes care of the correct mapping of fact data to its dimensions by using surrogate keys. Each join is then an inner join. In order to do so, dimension tables usually contain a row for the unknown element. Frequently, the surrogate key –1 is used for this row.

But following this best practice results in the SSAS dimension showing two elements for ‘unknown’: The dimension entry and the automatically created entry.

image

So, why does SSAS dimension have this build-in unknown element by default? If we build almost all SSAS cubes based on a good data warehouse design where the dimensions maintain their own unknown element, there is no need for an automatically created unknown element anymore. But since SSAS cube’s wizard is intended to work with most types of data structures, the unknown element is there by default. Without having ETL enforced surrogate keys you just cannot be sure, that every fact row maps to its dimensions.

So, as explained above, we want to remove this default unknown element in almost all SSAS cube development projects. This can be easily done in the properties dialog of the dimension:

image

There are four available options for the unknown member:

visible The unknown-member of the dimension exists and is visible
hidden The unknown-member of the dimension exists and is hidden
none The unknown-member of the dimension does not exist
automatic null The unknown-member of the dimension exists und is visible, if there are violations of the referential integrity (fact keys not found in in dimension).

Again, if we take care of the surrogate keys in the ETL process, there is no need for a dimension unknown element at all. So, the best option is, to disable it (UnknownMember set to none).

However, because of other default settings, you’re getting the following error when trying to deploy your SSAS model afterwards:

The 'KeyColumns' #0 has NullProcessing set to 'UnknownMember', but the dimension doesn't have UnknownMember set to 'Visible' or 'Hidden'

If you’re getting this error for the first time, it might not be clear, where to fix it, especially since there are two changes that need to be made:

 

1. Adjusting the dimension key attribute

If you look at the dimension, you’ll notice the red hash-line below the key attribute of your dimension.

image

In order to fix this, you’ll need to open the properties of that attribute. Now navigate to the key columns setting and expand the view for each of the columns (since you may have more than one column bindings for the attribute’s key) as shown in the following screenshot:

image

Here you can set the NullProcessing option to “Error”. The default is “UnkownMember” but since we just disabled this, this causes the error.

Remember to do this for each key column of this attribute.

 

2. Adjust null processing in the dimension usage.

The second place to modify is the dimension mapping. Therefore open the cube and go to the dimension usage tab. You’ll notice the red hash-line at the attribute (in my example, the Product ID):

image

In order to fix this, click the button near to the attribute (the one labeled with “-“) to open this dialog:

image

Click advanced to edit the properties of the mapping:

image

In the lower part of the dialog, you can set the “Null Processing” from “UnknownMember” to “Error”.

After this change you should be able to deploy the cube again.

Sunday, July 6, 2014

How to collect your customers’ data without scaring them off

Business Intelligence
In a recent survey, Abe Selig from Software Advice studied the perception of users about companies collecting their data. Obviously we’re currently in a dilemma here: On the one hand companies are gathering more and more data in order to be competitive. Where in the past marketing and promotions, for example were based upon “averaging” the customer needs (“our customers like to buy big cars”) we’ve now moved to more individualized and more targeted promotions and product recommendations. The better we can address our customer, the better the chances are for selling our product or for building up a long term customer relationship. And since customers have individual differences, companies need to address them individually. This requires knowledge (data) about the customers. The customers on the other hand are sensitive about their data being collected and because of the recent news about stolen passwords, hacking of major services (for example the recent Heartbleed bug in OpenSSL) and the NSA eavesdropping they might even be scared if someone collects their data. In order to gain a better picture of this situation, Software Advice asked a random sample of 385 respondents in the US what they think about their data being collected.
"Our survey found that the majority of respondents were in favor of stronger laws and regulations governing the way companies use CRM or business intelligence platforms to collect data about their customers. Just as the experts who were interviewed in the piece explained, I think a lot of this stems from previous bad experiences people have had or bad experiences they've heard about."
-- Abe Selig, Business Intelligence researcher at Software Advice

The results are not much surprising, however they clearly show the dilemma mentioned above. About three quarters of the respondents assume or suspect companies to collect customer individual data. And if asked for more legal regulations, almost three quarters agree that there should be stronger laws and regulations in place to protect privacy.
And also not surprising, younger people (age 25-35) tend to be more open about their data being collected than older people (55-64). But in general, asking for the kind of collected information the respondents would be comfortable with, shows that this will continue to be a very controversial topic in the future. While gathering data about the current location would only be acceptable for about 5% of the respondents, even likes/dislike would only be accepted by about 12%.
ALL respondents comfort level of data collection

"The numbers show there's a clear split between businesses, which want to collect customer data and leverage it, and their customers, who are deeply concerned about their privacy. But I also think that transparency is key here, and our survey backs that up too. We found that if companies are more upfront and open about how and why they use customer data, the fear factor is greatly diminished."     
-- Abe Selig, Business Intelligence researcher at Software Advice

However, the survey also clearly shows what can be done to prevent customers from being scared off: Customers would feel a lot less bothered about their data being collected if they are informed about the data that is collected and why it is collected. If companies inform customers about the collected data and if they use it only to improve the customer’s overall shopping experience, acceptability could be improved. Most people I know like the recommendation function in modern web shops while it feels a little bit strange to see the product you’ve just searched for in a web shop on a totally different site’s advertising. How do they know I’ve searched for this product? So, transparency is important and the customers’ benefit has to be clearly noticeable.
You can find the complete survey here: http://plotting-success.softwareadvice.com/collect-data-without-scaring-customers-0614/

Sunday, June 8, 2014

Practical table partitioning on the Parallel Data Warehouse

APS/PDW 2012

This post is about table partitioning on the Parallel Data Warehouse (PDW). The topic itself is actually quite simple but there are some differences between the SMP SQL Server compared to the Parallel Data Warehouse.

On the SMP SQL Server table partitioning was important with large tables for two reasons:

  1. Query performance
  2. Workload management

 

For the SMP SQL Server, table partitioning allows queries and other table operations (like index rebuild) to be performed on multiple cores. Therefore table partitioning was done to improve query performance. However, the PDW architecture already stores larger tables (so called distributed tables) on each compute node by distributing it to multiple tables (so called distributions) on separate files in an optimal way for the available cores (currently 8 distributions per compute node). Therefore, when working on a PDW query performance usually isn’t the main reason for us to use table partitioning. But the second reason, workload management, still applies on the PDW. For example, when loading data it’s often useful to first load into a stage table, merge new and old data into a new partition and then switch that partition to the final table. So partitioning is still important on the PDW.

Creating a partitioned table on the PDW is a little bit easier compared to the SMP SQL Server as you don’t need (and don’t see) the partition schema or partition function. The following statement is an example of creating a partitioned table:

CREATE TABLE [dbo].[MyTable1] (
    id int
)
WITH (DISTRIBUTION = replicate,  PARTITION (id range right FOR VALUES (10,20,30,40)));

In this query and throughout the remaining blog post, I’m only referring to a range right partition function. This is my preferred option as I think it’s more intuitive although both ways are almost identical and all partitioning is usually handled by automated tasks. So it isn’t really important. However, range right means that the partition boundary is in the same partition as the data to the right of the boundary (excluding the next boundary). So for a range right partition function, the left boundary is included while the right boundary is not, for example a partition with boundaries 10 and 20 contains data with values greater or equal to 10 and less than 20 (for integer values: 10, 11, 12, … 18, 19).

By specifying four boundaries in the create table statement from above, we have actually created five partitions as shown in the following table:

Partition Number Range From Range To Formula for id
1   10 id < 10
2 10 20 10 ≤ id < 20
3 20 30 20 ≤ id < 30
4 30 40 30 ≤ id < 40
5 40   40 ≤ id

On the PDW, the partition number is important for switch operations as well as for index rebuild operations. For example, in order to perform an index rebuild on partition 3 you write run this code:

ALTER INDEX «index name | ALL» ON MyTable1 REBUILD PARTITION = 3

The product documentation (aps.chm) contains a query to return “…the partition numbers, boundary values, boundary value types, and rows per boundary for a partitioned table”:

SELECT sp.partition_number, prv.value AS boundary_value,
lower(sty.name) AS boundary_value_type, sp.rows
FROM sys.tables AS st
JOIN sys.indexes AS si
    ON st.object_id = si.object_id AND si.index_id <2
JOIN sys.partitions AS sp
    ON sp.object_id = st.object_id AND sp.index_id = si.index_id
JOIN sys.partition_schemes AS ps
    ON ps.data_space_id = si.data_space_id
JOIN sys.partition_range_values AS prv
    ON prv.function_id = ps.function_id
JOIN sys.partition_parameters AS pp
    ON pp.function_id = ps.function_id
JOIN sys.types AS sty
    ON sty.user_type_id = pp.user_type_id
        AND prv.boundary_id = sp.partition_number
WHERE st.object_id =
    (SELECT object_id
     FROM sys.objects
     WHERE name = 'MyTable1')
ORDER BY sp.partition_number

Let’s try the query with our table from above. Here is the output:

01

Some observations may be confusing here. The first thing is that each partition is reported to contain 200 rows although we have just created the table and therefore expect the table to be empty. However, the reported rows are taken from the sys.partitions system view. In the documentation for the sys.partitions view you find the following remark about the number of rows:

Approximate average number of rows in each table partition. To calculate this value, SQL Server PDW divides the number of rows in the table by the number of partitions in the table.

SQL Server PDW uses statistics, which might be out-of-date, to determine the total number of rows. The statistics are from the most recent run of UPDATE STATISTICS on the table. If UPDATE STATISTICS has not been run on the table, the statistics won’t exist, and SQL Server PDW will use 1000 as the default total number of rows. To display the number of rows in each partition within each distribution, use DBCC PDW_SHOWPARTITIONSTATS (SQL Server PDW).

So, the number of rows is just estimated here, and since we don’t have created statistics for the table, PDW assumes the table to contain 1000 rows. But wait, 1000 rows divided by 4 partitions gives 250, not 200, right? Well, remember that we actually have 5 partitions although the meta data query from above only lists 4. I’m getting back on this soon.

Statistics are easy to create, so let’s do this first:

create statistics stat_id on MyTable1(id)

Here is the result when running the meta data query again:

02

So, now the number of rows seems to be correct. But be carefull: This still is only an approximation and you cannot expect the approximation to be accurate.

The other thing to be puzzled about with the output of the meta data query may be, that it only reports 4 partitions although we first figured out, that there should be 5 partitions. And also the boundary value may be confusing. For partition number one, we found out that it contains all data rows with id less than 10 (not equal). So the boundary value from the output is the excluded right boundary of our range right partitioning – confusing.

Stephan K√∂ppen already posted some useful queries for the PDW (see his post here). Using his partition query gives a much better result. I made some minor changes to the query and for this blog post, here’s the version I’m using here:

create table #Partitions
with (LOCATION = USER_DB, distribution=replicate)
as
SELECT
p.partition_number PartitionNr
, cast(coalesce(lag(r.value,1) over (order by p.partition_number),-2147483648) as int) RangeFromIncluding
, cast(coalesce(r.value,2147483647) as int) AS [RangeToExcluding]
FROM sys.tables AS t
JOIN sys.indexes AS i ON t.object_id = i.object_id
JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN  sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions AS f ON s.function_id = f.function_id
LEFT JOIN sys.partition_range_values AS r ON f.function_id = r.function_id and r.boundary_id = p.partition_number
WHERE i.type <= 1 
and t.name='MyTable1'

create table #PartitionData
with (LOCATION = USER_DB, distribution=replicate)
as
select id, count(*) [rows] from MyTable1 group by id

-- show partitions and number of rows
select PS.PartitionNr, PS.RangeFromIncluding, PS.RangeToExcluding, coalesce(Sum([rows]),0) [rows]
from #Partitions PS left join #PartitionData GT on PS.RangeFromIncluding<= GT.id and PS.RangeToExcluding>GT.id
group by PS.PartitionNr, PS.RangeFromIncluding, PS.RangeToExcluding

drop table #Partitions
drop table #PartitionData

 

If you’re only interested in the partitions, the blue part of the query is enough. The query uses the lag windows function to retrieve the lower boundary. The remaining query is used to obtain the exact number of rows for each partition. Please note, that the boundary information resulting from my modifications is only valid for a range right partition function. Here is the result:

03

As you see, this corresponds exactly to the five partitions from the first table above. The upper boundary of partition 5 should be increased by one to be 100% correct but this would conflict with the maximum integer value. If you like, just return null for the lower boundary of partition 1 and the upper boundary of partition 5 and observe this in the comparison with the existing data.

Also when reading the above query part that is printed in black you should adopt the method for counting the rows per partition to your needs. The method I’m using here proved to work fine for discrete values (integer ids). Since we usually partition by an integer column (for example a data written as yyyymmdd, 20140501 for May 1, 2014) this approach works fine for most of our workloads.

Next thing of interest is the partition number. As I wrote earlier in this post, the partition number is used for partition switch operations or for example for index rebuilds. It’s important to understand that the partition number is always a consecutive range of numbers starting with the number one. Even if you merge two partitions into one, the number is still consecutive.

For example, let’s merge partitions 3 and 4. In the merge partition statement we only need to specify the boundary. In a certain sense, this boundary is removed to form the new partition. In our case, partitions 3 and 4 share the boundary value 30, so the merge statement looks like this:

ALTER TABLE MyTable1 MERGE RANGE (30);

Here is the result using the modified meta data table from above:

04

As you can see, the partition number is still consecutive and the partition ranging from 40 to infinity now has the number 4 instead of 5.

If you specify a boundary, that doesn’t exist you’ll get an error message:

ALTER TABLE MyTable1 MERGE RANGE (35);

A distributed query failed: Node(s):[201001-201002]The specified partition range value could not be found.

Splitting a partition works very similar to a merge. Again, you can think of a split as of inserting a new boundary. For example, let’s split at the value 35 (which is in partition 3):

ALTER TABLE MyTable1 SPLIT RANGE (35);

Here’s the result:

05

Again, the partition numbering is still consecutive and the former partition 4 now becomes partition 5 because we split partition 3.

Now let’s validate our boundaries by inserting some lines of data:

insert into MyTable1 values(0)

06

As expected, the value 0 is written to partition 1 as –infinity ≤ 0 < 10.

truncate table MyTable1
insert into MyTable1 values(20)

07

The value 20 goes to partition 3 as 20 ≤ 20 < 35.

Now we’re going to insert 5 values which should fit the constraints for partition 4:

truncate table MyTable1
insert into MyTable1 values(35)
insert into MyTable1 values(36)
insert into MyTable1 values(37)
insert into MyTable1 values(38)
insert into MyTable1 values(39)

08

All of these values satisfy the constraint 35 ≤ x < 40 and therefore all the values are written to partition 4.

Ok, these were just some examples to see how data is written to the different partitions of our table.

To complete this post, I finally like to show a partition switch. Therefore we need to create a table of the same structure:

  • same columns, same data types, same nullable settings (take care when creating computed columns in a CTAS statement)
  • same table geometry (heap, clustered index, clustered columnstore index)
  • same distribution method (both tables replicated or distributed by the same key)
  • same indexes and constraints
  • partitioned by the same column (but the partitions itself may differ)

 

Generating the script for our table after the merge/split operation gives this result:

CREATE TABLE [dbo].[MyTable1] (
    [id] int NULL
)
WITH (DISTRIBUTION = REPLICATE,  PARTITION ([id] RANGE RIGHT FOR VALUES (10, 20, 35, 40)));

Now, replacing MyTable1 with myTable2 we can create a table of exactly the same structure:

CREATE TABLE [dbo].[MyTable2] (
    id int
)
WITH (DISTRIBUTION = replicate,  PARTITION (id range right FOR VALUES (10, 20, 35, 40)));

We can now switch the 5 rows of data from above. Since they are all stored in partition 4 we can switch them using this statement:

alter table MyTable1 switch partition 4 to MyTable2 partition 4

This is where we finally needed the partition number. We can now check the rows in table MyTable2:

select * from MyTable2

09

As you can see, all 5 rows are moved (switched) over to table MyTable2.

A common scenario for loading data into the appliance is to first load new data into a stage table of the same layout as the final fact table. Then our meta data query from above helps, by running it against both tables, using the boundaries as the join conditions. This results in the source partition and matching destination partition together with the number of rows in each of them. For example, if your workload contains only new or updated data you can now load the data as follows:

  • If the source partition contains no rows at all, quit
  • If the destination partition is empty switch the source partition directly into the destination partition and quit
  • Otherwise blend/merge the source and destination partition data into a new table with corresponding boundary values (this requires three partitions), then move the existing data out of the destination partition and finally move the merged data into the destination partition.

 

Summary: Partitioning on the PDW is still useful for workload management but usually not to increase query performance. With the query presented here, it’s quite easy to find the partitions together with their boundaries and number of contained rows. This information can be used to decide about a good partition switching strategy.

Sunday, May 11, 2014

Practical Clustered Columnstore Index on Parallel Data Warehouse (part 2)

PDW 2012 | APS 2012

In part 1 of this topic, I showed how basic insert operations in a CCI table are handled internally. As we saw, inserting of large batches causes the CCI to compress the data immediately (apart from the remaining rows that are still kept in the delta store) while small batches are causing closed row groups which are compressed asynchronously in the backgroud. Now I like to focus more on insert operations and on the distributions within the CCI table.

Step

Description

9

What happens for an update-operation on an already compressed row? Let’s try:

update CCITest set val='Test X' where val='Test del'

Since the compressed rows in the columnstore cannot be physically updated, the CCI marks the existing row as ‘deleted’ and inserts the new values as a new row. Therefore you see one deleted row (I included that column from the meta data query here) and one new row in the open row group (now containing 51425 rows instead of 51424 before):


image_thumb4

All subsequent update or delete operations to this row are now written to the delta store. Deleted rows cause some kind of fragmentation in the CCI. While one deleted row is nothing to worry about, you should carefully monitor the number of deleted rows in your CCI tables. If you find many deleted rows you should defragment the table using CTAS (as CTAS is always the answer…) or index rebuild. CTAS copies the data over to a new table thus resolving fragmentation. This still does not guarantee that the data is in compressed form after the CTAS. After all, CTAS goes through the delta store mechanism we’ve been discovered before and as we’ve seen, this may result in row groups that are still open and waiting for more rows of data to arrive. The other option for defragmentation of a CCI table is to run an index rebuild:

alter index all on CCITest rebuild partition = 1
-- or alternatively: partition=ALL

One thing to consider is, that this statement requires an exclusive lock on the table. Currently we’re using partition switching to move new data into our fact tables and therefore we’re running index rebuild/reorganize operations before switching the partition into the final fact table.

10

Ok, back to start. How many open, closed, compressed row groups can we have in the CCI table? While we will only find at most one open row group per partition and distribution, we can have as many closed and compressed row groups as needed. Take a look at the following example (starting with a truncate to reset our table):

truncate table CCITest
declare @i int =1
while @i<50 begin
insert into CCITest select top 100000 1, 'Test' from MyLargeTable
set @i=@i+1
end

Immediately after the statement is executed the result from the meta data query will look like this:

clip_image00121_thumb2

Since we inserted in small batches, the open row group is used until it reaches the limit of 1,048,576 rows. After that, it is closed and a new open row group is created.

Again, after a while, the tuple mover process begins picking up the closed row groups. The following screenshot was taken while the tuple mover still processes the table:

clip_image0023_thumb2

And finally, when the tuple mover is finished, the closed row groups are all compressed:

clip_image0033_thumb2

11

Now let’s try something different. As I said before, row groups are created per distribution and partition. Now we’re going to perform 16 inserts on a 2 compute node appliance (each with 8 distributions thus giving 16 distributions in total). I adjusted the id (hash column) in a way that we’re hitting all of the 16 distributions:

truncate table CCITest
insert into CCITest select top 100000 0, 'Test' from MyLargeTable
insert into CCITest select top 100000 1, 'Test' from MyLargeTable
insert into CCITest select top 100000 2, 'Test' from MyLargeTable
insert into CCITest select top 100000 3, 'Test' from MyLargeTable
insert into CCITest select top 100000 4, 'Test' from MyLargeTable
insert into CCITest select top 100000 5, 'Test' from MyLargeTable
insert into CCITest select top 100000 6, 'Test' from MyLargeTable
insert into CCITest select top 100000 7, 'Test' from MyLargeTable
insert into CCITest select top 100000 8, 'Test' from MyLargeTable
insert into CCITest select top 100000 9, 'Test' from MyLargeTable
insert into CCITest select top 100000 10, 'Test' from MyLargeTable
insert into CCITest select top 100000 11, 'Test' from MyLargeTable
insert into CCITest select top 100000 12, 'Test' from MyLargeTable
insert into CCITest select top 100000 13, 'Test' from MyLargeTable
insert into CCITest select top 100000 14, 'Test' from MyLargeTable
insert into CCITest select top 100000 15, 'Test' from MyLargeTable

As you see, for each distribution a new row group in status ‘open’ is created.

clip_image00123_thumb3

Think about this for a moment. Since we already showed that we can load about 1 million rows in an open row group (as long as we’re using small batches of up to 100,000 rows) and could have about 16 million rows in open row groups (one million per distribution) per partition as a worst case.

So, if you’re using CCI on smaller tables (for example a dimension table) or on tables with many partitions, you can easily face situations, where most of the table’s rows are uncompressed. Again, it’s important to monitor the amount of uncompressed rows and to perform an alter index reorganize (for the closed row groups) or alter index rebuild (if there are too many open row groups).

12

Speaking about monitoring. A good start is the meta data query from above. However, you could also try running dbcc pdw_showspaceused. Let’s try:

dbcc pdw_showspaceused(CCITest)

What you can see here is, that dbcc pdw_showspaceused does only count compressed rows (first column reads 0 rows in each of the 16 distributions). Therefore, pdw_showspaceused is not a good tool for determining uncompressed rows in a CCI table.

clip_image002_thumb3

13

After running an index rebuild on the table, dbcc pdw_showspaceused shows the correct number of rows since all rows are now stored in compressed row groups:

alter index all on CCITest rebuild partition = 1
dbcc pdw_showspaceused(CCITest)

clip_image0026_thumb2

 

Summary

CCI is a powerful feature in the PDW 2012 appliance. However, you should monitor your CCI tables carefully for open/closed row groups, because the data in such row groups is not yet compressed and depending on the table layout (for example the number of partitions) you may find a lot of rows here. Maintenance tasks (index reorganize, index rebuild) or a smart loading strategy (for example by performing the rebuild before the partition with the new data is switched into the final table) are counter measures to consider here. If your CCI table also has to handle delete or update operations you may also have to deal with fragmentation (number of deleted rows in the CCI). Consider using CTAS and partition switching patterns rather than running delete or update statements that involve a lot of rows. If this is not possible, monitoring fragmentation is important. Then, from time to time, you will have to defragment the CCI table (CTAS each partition to a new table or use index rebuild).

Saturday, May 3, 2014

Practical Clustered Columnstore Index on Parallel Data Warehouse (part 1)

PDW 2012

Together with the parallel query engine, the clustered column store index (CCI) gives the Parallel Data Warehouse (PDW) an outstanding query performance. As with SQL Server 2014, data is natively stored in compressed format, giving these advantages:

  • less size needed for storing large amounts of data
  • less IO needed to read it
  • outstanding query performance because it is memory optimized (xVelocity)

 

What makes the CCI especially useful is that the table remains writable, so you can perform any kind of loading operations (inserts, partition switching) on the table without any need to recreate the column store index afterwards. In fact, the column store index IS the table (therefore the name clustered column store index).

However, when data is written to the CCI it is not always being compressed immediately. In short, newly inserted data is split up into row groups. The status of a row group may be

OPEN This means that the rows are actually stored in a row store (a ‘normal’ table, so called delta store). PDW still waits for more rows to arrive before the rows are compressed into the CCI storage.
CLOSED This means that this current row store buffer (delta store) is full (at approximately 1 million rows of data). This buffer will be compressed asynchronously by a background process (‘tuple mover process’).
COMPRESSED This means that the row group is really compressed in the CCI (final stage).

You can find more information on this including some illustrations in the PDW help file (look for ‘clustered columnstore index’).

In order to explain the nature of CCI write operations on the PDW, let’s go through some practical tests inserting, deleting and updating rows under different scenarios. You can find a similar example in the PDW help file but I wanted to cover more aspects here.

After each operation we want to see how the delta store looks like. I’m using the query from the metadata queries section of the chm file for this purpose:

-- Show size of columnstore index
SELECT
  CSRowGroups.pdw_node_id, CSRowGroups.partition_number, CSRowGroups.row_group_id, CSRowGroups.state_description, CSRowGroups.total_rows,
  CSRowGroups.deleted_rows, CSRowGroups.size_in_bytes
FROM sys.objects AS o
JOIN sys.indexes AS i
    ON o.object_id = i.object_id
JOIN sys.pdw_index_mappings AS IndexMap
    ON i.object_id = IndexMap.object_id
    AND i.index_id = IndexMap.index_id
JOIN sys.pdw_nodes_indexes AS NI
    ON IndexMap.physical_name = NI.name
    AND IndexMap.index_id = NI.index_id
JOIN sys.pdw_nodes_column_store_row_groups AS CSRowGroups
    ON CSRowGroups.object_id = NI.object_id
    AND CSRowGroups.pdw_node_id = NI.pdw_node_id
AND CSRowGroups.index_id = NI.index_id   
WHERE o.name = 'CCITest'
ORDER BY 1,2

 

Step

Description

1

For our tests, let’s first created a distributed table. Although not recommended, I choose the same key here for partitioning and distribution.

create table CCITest (
id int NOT NULL,
val nvarchar(20) NOT NULL
)
with (
distribution = hash(id), clustered columnstore index,
partition (id range right for values (100) )
)

Checking the row groups using the meta data query from above shows no results (no row groups are created in advance). This is also what we see after a truncate table operation:

clip_image001[3]

2

We now insert one row of data into one partition of the table:

insert into CCITest values(1,'Test')

As expected, this row goes to the delta store causing an open row group. Please note, that this row is not yet compressed but waiting for more rows to arrive (in order to efficiently use CCI compression).

clip_image001[5]

3

Let’s insert another row of data into another partition of the table:

insert into CCITest values(100,'Test')

Since row groups are created per distribution and partition, we have created a second open row group. Both row groups are waiting for more rows of data before they are going to be compressed.

clip_image001[7]

4

Now we delete the recently inserted row of data:

delete from CCITest where id=100;

Since the row is not yet compressed, it is simply removed from the delta store:

clip_image001[9]

5

Ok, now let’s insert 100,000 rows of data into the first partition of this table. I’m using a table “MyLargeTable” here which can be any table containing at least 100,000 rows:

insert into CCITest select top 100000 1, 'Test' from MyLargeTable

Together with the row from above, this gives 100,001 rows in the first row group. Please note, that the row group still isn’t compressed (status ‘OPEN’).

clip_image001[11]

6

Let’s empty the table (using a truncate) and then insert 200,000 rows of data into the first partition:

truncate table CCITest
insert into CCITest select top 200000 1, 'Test' from MyLargeTable

What you see here, is that a 200,000 rows insert causes the CCI to automatically (and synchronously) compress the data. Status ‘COMPRESSED’ means that the data is now stored natively in column store optimized format. So if more than 100,000 rows (or more exactly more than 102,400 rows) are written to a row group in a batch, the rows are automatically and synchronously compressed.

clip_image001[13]

7

We empty the table again (truncate) and now insert 10 times 100,000 rows of data into the table (I tag one row of the first insert for later use):

truncate table CCITest
insert into CCITest select top 99999 1, 'Test' from MyLargeTable
insert into CCITest select top 1 1, 'Test del' from MyLargeTable
insert into CCITest select top 100000 1, 'Test' from MyLargeTable
insert into CCITest select top 100000 1, 'Test' from MyLargeTable
insert into CCITest select top 100000 1, 'Test' from MyLargeTable
insert into CCITest select top 100000 1, 'Test' from MyLargeTable
insert into CCITest select top 100000 1, 'Test' from MyLargeTable
insert into CCITest select top 100000 1, 'Test' from MyLargeTable
insert into CCITest select top 100000 1, 'Test' from MyLargeTable
insert into CCITest select top 100000 1, 'Test' from MyLargeTable
insert into CCITest select top 100000 1, 'Test' from MyLargeTable

Although we inserted many more rows than before, now the rows are not compressed but we now have one open row group with 1,000,000 rows of data:

clip_image001[15]

The difference to the example from above where we inserted 200,000 rows causing an immediate compression is that we now have smaller batches. Remember that our table now contains 1 millions rows, all being in uncompressed state (“slow” access). Please note that since we’ve always used the same id (hash column) all these rows are stored in the same distribution.

8

In order to find out, if this goes on and on for ever, we insert another 100,000 rows of data into the table

insert into CCITest select top 100000 1, 'Test' from MyLargeTable

What we now observe is that the open row group was closed after reaching 1,048,576 rows. The remaining rows are inserted into a freshly created open row group:

clip_image001[17]

Status ‘CLOSED’ means uncompressed row storage. Compression is now being performed asynchronously using a background task (‘tuple-mover process’) which runs every 5 minutes. So after a short moment, our table looks like this:

clip_image001[19]

As you can see, the tuple mover has now compressed the ‘CLOSED’ rowgroup. If you do not want to wait, you could also run this statement:

alter index all on CCITest reorganize partition = 1

In part 2 of this post, I’m continuing the examples showing update operations and also showing how row groups are created per distribution (and partition).

Sunday, April 20, 2014

Parallel Data Warehouse (PDW) and ROLAP – Part 2

PDW 2012

As promised in my previous post about ROLAP on PDW, here are some more tips, tricks and recommendation for using SSAS in ROLAP mode on a PDW source. Please read part 1 first as most of the tips from that post are not being repeated here.

Keep your statistics up to date

Ok, this was also in my last post but it cannot be said often enough. So here it is again. Statistics are most important when it gets to query performance. Make sure you have statistics at least on all columns that participate in join, filtering (where-clause) or aggregation (group by-clause) operations.

Clustered Columnstore Index

As I already mentioned, the clustered column store index (CCI) is the key to running SSAS in ROLAP mode on the PDW as it gives a very good performance. In fact, all big tables in our data warehouse on PDW are currently in CCI storage. CCI stores the data natively in column store compressed format thus being able to achieve the outstanding performance. However, it’s important to understand how the CCI works. In short, when data is written to the CCI, it is first written to a row store table internally. This is called the delta store and the data is stored there as a row group. From that delta store, the data is compressed either synchronously (when larger batches hit the CCI table) or asynchronously (when smaller batches are inserted causing the current row group to reach its storing limit) using the tuple mover process. And since the delta store exists for each distribution and each partition, a lot of rows of the CCI table may actually be in row store mode either waiting to be compressed (row group in status ‘CLOSED’: waiting for tuple mover) or waiting for more rows to arrive (row group in status ‘OPEN’). Imagine a two compute node appliance with 100 partitions, giving 2x8=16 distributions and therefore potentially 1600 open delta stores. Each of those may potentially contain uncompressed rows. And there may be many more row groups in status ‘CLOSED’ also containing uncompressed rows.

The more rows are in uncompressed state, the slower the queries get accessing the data. Therefore it is very important to carefully monitor the state of your CCI tables for uncompressed rows. You can find a very useful query for that in the meta data section of the help file:

-- show rowgroups and status of a table by partition


SELECT IndexMap.object_id, 
object_name(IndexMap.object_id) AS LogicalTableName,
i.name AS LogicalIndexName, IndexMap.index_id, NI.type_desc,
IndexMap.physical_name AS PhyIndexNameFromIMap,
CSRowGroups.*,
100*(total_rows - ISNULL(deleted_rows,0))/total_rows AS PercentFull
FROM sys.objects AS o
JOIN sys.indexes AS i
ON o.object_id = i.object_id
JOIN sys.pdw_index_mappings AS IndexMap
ON i.object_id = IndexMap.object_id
AND i.index_id = IndexMap.index_id
JOIN sys.pdw_nodes_indexes AS NI
ON IndexMap.physical_name = NI.name
AND IndexMap.index_id = NI.index_id
JOIN sys.pdw_nodes_column_store_row_groups AS CSRowGroups
ON CSRowGroups.object_id = NI.object_id
AND CSRowGroups.pdw_node_id = NI.pdw_node_id
AND CSRowGroups.index_id = NI.index_id
WHERE o.name = '<insert table_name here>'
ORDER BY object_name(i.object_id), i.name, IndexMap.physical_name, pdw_node_id;




In general, if a lot of rows are in row groups with status ‘OPEN’ or ‘CLOSED’ you should take action:



Row groups in status ‘CLOSED’: The tuple mover process should compress these row groups (new status ‘COMPRESSED’) in the background. However, if there are a lot of row groups in status ‘CLOSED’ the tuple may be behind. In order to help the tuple mover process you should run an alter index … reorganize to compress these row groups. Usually this works pretty fast and the table remains online for queries.



Row groups in status ‘OPEN’: If you find many rows in row groups of status ‘OPEN’ you may consider reducing the number of partitions in your table (if possible) or compress these row groups manually using an alter index … rebuild. However, the rebuild takes some time and needs an exclusive lock on the table. So, if your workload is designed to use partition switching you may want to perform the index rebuild on the source partition before switching it into the final table.



Either way, you should carefully monitor your CCI tables for the ratio of uncompressed row groups in order to have a good query performance, especially for ROLAP queries.



Dimensions in MOLAP or ROLAP?



Now that we’re having the fact tables in ROLAP, what about the dimensions? Wouldn’t it be nice to have them in ROLAP too? From what we experienced in practical use, you should have all your dimensions in classical MOLAP mode. Only if you have a very large dimension, which is difficult to process, I would go for ROLAP. The main reasons are:



First Reason: ROLAP dimensions may give false positives of the duplicate key error at query time. This effect is discussed here and can also be found on technet here (search for “false positives”). However, the solution proposed on technet was to switch error configuration to ignore error:




For a ROLAP dimension, you should disable DuplicateKey errors. This error was enabled by default in AS 2008 to try to help users catch incorrectly defined attribute relationships -- but if you are using ByTable processing or ROLAP storage, then it will only give you false positives.




I don’t think that this is a safe and good approach (see my post here on this topic), so by having the dimensions in MOLAP mode with activated key duplicate error detection, I’m sure the keys are consistent.



image



Second Reason: From my observation, SSAS generates less and better queries if the dimension is in MOLAP mode (the query still goes to the underlying table using SQL). If the dimension is in ROLAP mode, SSAS fires one additional queries to the dimension table before going to the fact tables. For MOLAP dimensions we see just one queries joining the fact table to the dimension tables.



Which resource class should I use?



Higher resource classes on the PDW (more query slots) usually improve the query performance of high workload queries at the price that less queries can be run in parallel. But for ROLAP we want many users to execute rather simple queries in parallel. What makes things worse is that some ROLAP operations are solved using multiple queries for only one pivot table. Therefore we made the best practical experience using smallrc (default) resource class for the SSAS queries.



Take care with non-aggregatable data types



For a MOLAP cube it’s fine to have a measure of type datetime (hidden, just for drill through) and set the aggregation function to ‘none’. But for ROLAP the SSAS engine still tries to fetch a sum for those measures causing the query to fail because sum aggregate is not supported for datetime. One solution is to use aggregation functions min or max for these cases.



Also, you should take care with measures of type integer. Even if you’re only using those measures as a flag on an intermediate bridge table, the SQL query may result in an integer overflow error.



Use SNAC as data base driver



As described before, PDW should be addressed through the simple SNAC driver (SQL Server Native Client) as shown in the screenshot below:



image



Miscellaneous



If you get the error “42000, Parse error at line: 1, column: 51: Incorrect syntax near @ResultCode”, you probable have proactive caching set to on which isn’t supported on the PDW yet.



image



The reason for the error is, that for proactive caching, SSAS tries to execute the stored function sp_trace_create on the data source:



DECLARE @ResultCode INT;

DECLARE @TraceId INT;


EXEC @ResultCode = sp_trace_create @TraceId OUTPUT, 1;


SELECT @ResultCode as ResultCode, @TraceId as TraceId;

Monday, April 7, 2014

Discover missing rows of data

PDW 2012 | SQL Server 2012 | SQL Server 2014

If your source data contains a subsequent number without gaps it’s relatively easy to find out if data rows are missing. The approach I’m showing here uses window functions that are available since SQL Server 2012 and SQL Server Parallel Data Warehouse 2012.

In order to have some sample data for this post, I’m using the FactInternetSales table of the AdventureWorksDW2012 database. Let’s pretend the column SalesOrderNumber of that table should not have any gaps. I convert the column data to a numeric type and use only the rows having line item sequence number equal to 1 for my sample data.

SELECT
SalesOrderNumber,
convert(int, substring(SalesOrderNumber,3,255)) SalesOrderIntNumber
FROM [FactInternetSales]
WHERE [SalesOrderLineNumber]=1
ORDER BY SalesOrderNumber

image

Usually the order number is sequentially but we find some gaps here. For example, the order following order number SO43842 is SO43918, so there are 43918 – 43842 – 1 = 75 rows missing.

Using window functions and a sub query, we can add the next number as a column to the query together with the distance:

select *, NextSalesOrderIntNumber-SalesOrderIntNumber-1 MissingRows
from
(
select
  SalesOrderIntNumber,
  lead(SalesOrderIntNumber,1) over (order by SalesOrderIntNumber) NextSalesOrderIntNumber
from
(SELECT SalesOrderNumber, convert(int, substring(SalesOrderNumber,3,255))
  SalesOrderIntNumber FROM [FactInternetSales] where [SalesOrderLineNumber]=1
) TransactionData
) TransactionDataSequence

image

As you can see, the 75 missing rows are now being reported correctly by the query. The only task left to do now is to aggregate the amount of missing rows by replacing the outer query like this:

select Sum(NextSalesOrderIntNumber-SalesOrderIntNumber-1) MissingRows
from
(
select
  SalesOrderIntNumber,
  lead(SalesOrderIntNumber,1) over (order by SalesOrderIntNumber) NextSalesOrderIntNumber
from
(SELECT SalesOrderNumber, convert(int, substring(SalesOrderNumber,3,255))
  SalesOrderIntNumber FROM [FactInternetSales] where [SalesOrderLineNumber]=1
) TransactionData
) TransactionDataSequence

 

image

As a quality measure you could show the ratio of the missing rows to the total rows (or 100% minus this ratio as a data completeness measure) and – assuming that the missing rows had an average sales amount – also the estimated missing amount. And it’s also useful to get the result on more granular level, for example per month. Here is the full query:

select

orderdatekey/100 [Month],

Sum(NextSalesOrderIntNumber-SalesOrderIntNumber-1) MissingRows,

convert(float,Sum(NextSalesOrderIntNumber-SalesOrderIntNumber-1))/count(*)
MissingRowsRatio,

convert(float,Sum(NextSalesOrderIntNumber-SalesOrderIntNumber-1))/count(*) 
* Sum([ExtendedAmount]) MissingRowsEstimatedValue

from
(
select
  SalesOrderIntNumber,
  lead(SalesOrderIntNumber,1) over (order by SalesOrderIntNumber) NextSalesOrderIntNumber,
  [ExtendedAmount], OrderDateKey
from
(SELECT SalesOrderNumber, convert(int, substring(SalesOrderNumber,3,255))
SalesOrderIntNumber, [ExtendedAmount], OrderDateKey
FROM [FactInternetSales] where [SalesOrderLineNumber]=1
) TransactionData
) TransactionDataSequence

group by orderdatekey/100
order by orderdatekey/100

image

Plotting the result over the time gives a good overview. For my example data, quality improved a lot since August 2007.

image

Conclusion: This is another example how window functions provide an elegant solution for solving analytical data tasks. And since this works perfectly on a PDW, the approach works well even with billions of rows of data.