Usage prerequisites

Abstract

Describes the prerequisites that are necessary before you can use the reporting client.

This topic describes the prerequisites for using the reporting client:

Design models

The Reporting Foundation supports two types of objects: facts and dimensions. The following diagram shows the Product Sales fact as an example:

Follow these rules when you design facts and dimensions:

Fact types must implement the IFact interface from Sitecore.Reporting.Core.
Dimension types must implement the IDimension interface from Sitecore.Reporting.Core.
A fact can reference any number of dimensions, including 0 (none).
A fact cannot reference another fact.
Dimensions cannot reference facts or other dimensions.

The IFact interface contains the attributes required in all fact types. :

Id: the numeric ID of the fact row (auto-increment).
DateTime: the date of the event.
LastUpdatedOn: the last update date.

The IDimension interface contains the attributes required in all dimension types. :

Id: the numeric ID of the dimension (hash of the dimension key).
DimensionKey: a unique identifier that identifies the dimension (for example country code for country). This property is used for querying.

Create CLR types

When you have designed a model with facts and dimensions, create CLR (Common Language Runtime) types for this model. The code below creates CLR types for the design model example shown in the previous section:

// Product Sales fact
public class ProductSales : IFact {
    public long Id { get; set; }
    public DateTime DateTime { get; set; }
    public DateTime LastUpdatedOn { get; set; }
    public double Revenue { get; set; }
    public int Count { get; set; }
    public double TaxesPaid { get; set; }
    public float DiscountPercentage { get; set; }
    public Product Product { get; set; }
    public long ProductId { get; set; }
    public Country Country { get; set; }
    public long CountryId { get; set; }
    public Store Branch { get; set; }
    public long BranchId { get; set; }
}

//Product dimension
public class Product : IDimension {
    public long Id { get; set; }
    public string DimensionKey { get; set; }
    public string Name { get; set; }
    public string Category { get; set; }
}

//Country dimension
public class Country : IDimension {
    public long Id { get; set; }
    public string DimensionKey { get; set; }
    public string Name { get; set; }
}

//Store dimension
public class Store : IDimension {
    public long Id { get; set; }
    public string DimensionKey { get; set; }
    public string Address { get; set; }
}

Follow these rules when you create CLR types:

A fact must reference dimensions by dimension and dimension object (for example, Country, Product).
The of the referenced dimension must have the same name as the dimension object + . It does not have to be the same as the dimension type name (for example, Country and CountryId, Branch and BranchId).
The dimension key defines how you query and filter results when you use this dimension. For example, if you want the country code to identify the country dimension and use this key for querying the data by using USA for United States of America, store the country code in the dimension key.

Create JSON type representation

This step is to have a JSON model representing your reporting types, as the following figure demonstrates.

[
    {
        "Name": "ProductSales",
        "ReportingType": "Fact",
        "Properties": [
            {
                "Name": "Id",
                "Type": "System.Int64"
            },
            {
                "Name": "DateTime",
                "Type": "System.DateTime"
            },
            {
                "Name": "LastUpdatedOn",
                "Type": "System.Nullable`1[[System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]"
            },
            {
                "Name": "Revenue",
                "Type": "System.Double"
            },
            {
                "Name": "Count",
                "Type": "System.Int32"
            },
            {
                "Name": "TaxesPaid",
                "Type": "System.Double"
            },
            {
                "Name": "DiscountPercentage",
                "Type": "System.Single"
            },
            {
                "Name": "ProductId",
                "Type": "System.Int64"
            },
            {
                "Name": "CountryId",
                "Type": "System.Int64"
            },
            {
                "Name": "BranchId",
                "Type": "System.Int64"
            }
        ],
        "NavigationProperties": [
            {
                "Name": "Product",
                "Type": "Product"
            },
            {
                "Name": "Country",
                "Type": "Country"
            },
            {
                "Name": "Branch",
                "Type": "Store"
            }
        ]
    },
    {
        "Name": "Product",
        "ReportingType": "Dimension",
        "Properties": [
            {
                "Name": "Id",
                "Type": "System.Int64"
            },
            {
                "Name": "DimensionKey",
                "Type": "System.String"
            },
            {
                "Name": "Name",
                "Type": "System.String"
            },
            {
                "Name": "Category",
                "Type": "System.String"
            }
        ],
        "NavigationProperties": []
    },
    {
        "Name": "Country",
        "ReportingType": "Dimension",
        "Properties": [
            {
                "Name": "Id",
                "Type": "System.Int64"
            },
            {
                "Name": "DimensionKey",
                "Type": "System.String"
            },
            {
                "Name": "Name",
                "Type": "System.String"
            }
        ],
        "NavigationProperties": []
    },
    {
        "Name": "Store",
        "ReportingType": "Dimension",
        "Properties": [
            {
                "Name": "Id",
                "Type": "System.Int64"
            },
            {
                "Name": "DimensionKey",
                "Type": "System.String"
            },
            {
                "Name": "Address",
                "Type": "System.String"
            }
        ],
        "NavigationProperties": []
    }
]

JSON model representation explanation:

A .json file contains array of objects, and each object contains the following properties:

Name: Name of the type
ReportingType: Either fact or dimension
Properties: describes all the properties of a type each property contains (Name and Type)
NavigationProperties: describes all the properties with relations to other objects (that is, fact to dimensions). Each property contains name and type.

You must copy the .json files to XConnect in the following path: ~/App_Data/Config/Sitecore/Reporting/Models.

Note

For properties, Type has to be the full type name, However for navigation properties the type should match the dimension type in the JSON file.

Create SQL objects

After creating the CLR types and the JSON Model Representation file SQL Objects has to be created, for each type (that is, Fact or Dimension) a three SQL Objects has to be created (Table, SQL Table-Type, Stored Procedure) as follows:

/* Tables */

CREATE TABLE [dbo].[Dimension_Product](
[Id] [bigint] NOT NULL,
[DimensionKey] [nvarchar](max) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Category] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Dimension_Product] PRIMARY KEY CLUSTERED ([Id] ASC)
)

CREATE TABLE [dbo].[Dimension_Country](
[Id] [bigint] NOT NULL,
[DimensionKey] [nvarchar](max) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Dimension_Product] PRIMARY KEY CLUSTERED ([Id] ASC)
)

CREATE TABLE [dbo].[Dimension_Store](
[Id] [bigint] NOT NULL,
[DimensionKey] [nvarchar](max) NOT NULL,
[Address] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Dimension_Product] PRIMARY KEY CLUSTERED ([Id] ASC)
)

CREATE TABLE [dbo].[Fact_ProductSales](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [DateTime] [datetime] NOT NULL,
    [LastUpdatedOn] [datetime] NULL,
    [Revenue] [float] NOT NULL,
    [Count] [int] NOT NULL,
    [TaxesPaid] [float] NOT NULL,
    [DiscountPercentage] [real] NOT NULL,
    [ProductId] [bigint] NOT NULL,
    [CountryId] [bigint] NOT NULL,
    [BranchId] [bigint] NULL,
 CONSTRAINT [PK_Fact_ProductSales] PRIMARY KEY CLUSTERED ([Id] ASC),
 CONSTRAINT [FK_Fact_ProductSales_Dimension_Product] FOREIGN KEY (ProductId) REFERENCES [dbo].[Dimension_Product] ([Id]),
 CONSTRAINT [FK_Fact_ProductSales_Dimension_Country] FOREIGN KEY (CountryId) REFERENCES [dbo].[Dimension_Country] ([Id]),
 CONSTRAINT [FK_Fact_ProductSales_Dimension_Store] FOREIGN KEY (BranchId) REFERENCES [dbo].[Dimension_Store] ([Id]),
 ) 

 /* Types */

 CREATE TYPE [dbo].[Product_Type] AS TABLE(
[Id] [bigint] NOT NULL,
[DimensionKey] [nvarchar](max) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Category] [nvarchar](max) NOT NULL
)

CREATE TYPE [dbo].[Country_Type] AS TABLE(
[Id] [bigint] NOT NULL,
[DimensionKey] [nvarchar](max) NOT NULL,
[Name] [nvarchar](max) NOT NULL
)

CREATE TYPE [dbo].[Store_Type] AS TABLE(
[Id] [bigint] NOT NULL,
[DimensionKey] [nvarchar](max) NOT NULL,
[Address] [nvarchar](max) NOT NULL
)

CREATE TYPE [dbo].[ProductSales_Type] AS TABLE(
    [Id] [bigint] NOT NULL,
    [DateTime] [datetime] NOT NULL,
    [LastUpdatedOn] [datetime] NULL,
    [Revenue] [float] NOT NULL,
    [Count] [int] NOT NULL,
    [TaxesPaid] [float] NOT NULL,
    [DiscountPercentage] [real] NOT NULL,
    [ProductId] [bigint] NOT NULL,
    [CountryId] [bigint] NOT NULL,
    [BranchId] [bigint] NULL
 ) 

 /* Stored Procedures */
 GO
 CREATE PROCEDURE [dbo].[Add_ProductSales]
    @table [dbo].[ProductSales_Type] READONLY
    WITH EXECUTE AS OWNER
    AS
    BEGIN
        SET NOCOUNT ON;
            MERGE [Fact_ProductSales] AS Target USING @table AS Source
            ON
            Target.[DateTime] = Source.[DateTime]
            AND Target.[ProductId] = Source.[ProductId]
            AND Target.[BranchId] = Source.[BranchId]
            AND Target.[CountryId] = Source.[CountryId]
            WHEN MATCHED THEN
            UPDATE SET
             Target.[Revenue] = Source.[Revenue],
             Target.[Count] = Source.[Count],
             Target.[TaxesPaid] = Source.[TaxesPaid],
             Target.[DiscountPercentage] = Source.[DiscountPercentage]
            WHEN NOT MATCHED THEN

            INSERT ([DateTime],
                    [LastUpdatedOn],
                    [Revenue],
                    [Count],
                    [TaxesPaid],
                    [DiscountPercentage],
                    [ProductId],
                    [BranchId],
                    [CountryId])
            Values (Source.[DateTime],
                    Source.[LastUpdatedOn],
                    Source.[Revenue],
                    Source.[Count],
                    Source.[TaxesPaid],
                    Source.[DiscountPercentage],
                    Source.[ProductId],
                    Source.[BranchId],
                    Source.[CountryId]);
END

 GO
 CREATE PROCEDURE [dbo].[Add_Product]
    @table [dbo].[Product_Type] READONLY
    WITH EXECUTE AS OWNER
    AS
    BEGIN
        SET NOCOUNT ON;
            MERGE [Dimension_Product] AS Target USING @table AS Source
            ON
            Target.[Id] = Source.[Id]
            WHEN MATCHED THEN
            UPDATE SET
             Target.[Name] = Source.[Name],
             Target.[Category] = Source.[Category]
            WHEN NOT MATCHED THEN

            INSERT ([Id],
                    [DimensionKey],
                    [Name],
                    [Category])
            Values (Source.[Id],
                    Source.[DimensionKey],
                    Source.[Name],
                    Source.[Category]);
END

 GO
 CREATE PROCEDURE [dbo].[Add_Country]
    @table [dbo].[Country_Type] READONLY
    WITH EXECUTE AS OWNER
    AS
    BEGIN
        SET NOCOUNT ON;
            MERGE [Dimension_Country] AS Target USING @table AS Source
            ON
            Target.[Id] = Source.[Id]
            WHEN MATCHED THEN
            UPDATE SET
             Target.[Name] = Source.[Name]
            WHEN NOT MATCHED THEN

            INSERT ([Id],
                    [DimensionKey],
                    [Name])
            Values (Source.[Id],
                    Source.[DimensionKey],
                    Source.[Name]);
END

 GO
 CREATE PROCEDURE [dbo].[Add_Store]
    @table [dbo].[Store_Type] READONLY
    WITH EXECUTE AS OWNER
    AS
    BEGIN
        SET NOCOUNT ON;
            MERGE [Dimension_Store] AS Target USING @table AS Source
            ON
            Target.[Id] = Source.[Id]
            WHEN MATCHED THEN
            UPDATE SET
             Target.[Address] = Source.[Address]
            WHEN NOT MATCHED THEN

            INSERT ([Id],
                    [DimensionKey],
                    [Address])
            Values (Source.[Id],
                    Source.[DimensionKey],
                    Source.[Address]);
END

SQL object rules

Follow these rules for SQL objects:

Tables

Fact Tables have the naming convention of Fact_[TypeName].
Dimension Tables have the naming convention of Dimension_[TypeName].
Fact Tables should have an auto increment [Id] as PK.
Dimension Tables should have a PK [Id] without auto increment.

Types

Postfix Types for fact and dimensions with [TypeName]_Type.

Stored Procedures

Using Table-Valued Parameter (SQL Type).
Naming convention is Add_[TypeName].
Fact tables should compare FK for the purpose of update.
Dimension tables should compare PK for the purpose of update.