Composite key partitioning in Tabular Model (and SQL Server)

• luca_allieri

  SSC Enthusiast

  Points: 152

  More actions

  June 17, 2014 at 7:22 am

  #287658

  Hi,

  I have a question about partitions in both SQL Server table and Tabular Model.

  I started to use Tabular Model recently so I'm not expert with it and I need some suggestions.

  I need to partition a table that collects daily rows for different clients.

  The natural partition key is a combination of clientID+dateID (something like CL-YYYYMMDD)

  I created a configuration table with a primary key PartitionID IDENTITY(1,1) , that contains also the field clientID and dateID

  Every day I add a new row in it and I get a partitionID for the new client and date

  Then I created a partitioned fact table using PartitionID as the partition field, using the partition function and the partition schema as well.

  The daily client data is inserted in the partitioned table using the partitionID

  Everything works fine, and the data are loaded correctly into the partitioned fact table.

  Then I created a Tabular Model where the fact table is the partitioned table, and I created tabular model partitions using something like "select <field list> from PartitionedTable where partitionID = <partitionID>"

  In this way, every day I load partitioned data in both sql server and tabular model.

  I have two dimensions, client and calendar

  Now my question is: when I browse the Tabular Model, and I'm selecting a specific dimension date and dimension client, am I using the partitionID index correctly?

  Or should I put in the tabular model partition query something like "select <field list> from PartitionedTable where clientID = <clientID> and dateID = <dateID>"?

  In this case is still working the partitionID index?

  How can I check it?

  Thank you for any suggestion on this,

  Luca