Jet from insightsoftware

Composite Dimension Key Level
Composite Dimension Key Level Overview There are occasions when you may need to specify more than one field to create unique values in the cubes. Some examples of this are: Two or more companies share a numbering series for a master record (customers, vendors, etc.) but each value is unique to that company A value is only unique when 2 or more fields match, such as cust...
Table Relations and Relationship Types
Table Relations and Relationship Types The feature referred to in this article applies to the Jet Data Manager version 16.4.3 and higher Overview Sometimes, it's desirable to be able to create multiple foreign key relationships within a Jet Data Manager project. For the example below, we're going to link the Customer table in the standard NAV 2015 project to the Posted S...
Physical Foreign Keys Setup in the JDM
Physical Foreign Keys Setup in the JDM Overview The Jet Data Manager can store physical foreign keys in the data warehouse. While the JDM already knows how tables are related, Physical Foreign Keys will store this information in the data warehouse, enabling other applications that read from the data warehouse to know the relationships between tables as well. This feature is useful wh...
Advanced Dimensions (NAV 2013 and Newer) with the JDM
Advanced Dimensions (NAV 2013 and Newer) with the JDMRelated Articles... Overview Starting with NAV 2013, the advanced dimension structure in the database was drastically changed to be easier to use and take up much less disk space.  This article explains how to add Advanced Dimensions from NAV 2013 to a project using the Jet Data Manager . Process Unlike earlier versi...
Working with Option Values in the JDM
Working with Option Values in the JDM Overview This document walks you through the process of using the Jet Data Manager to replace option values with their descriptive text values. What is an Option Value? An option value is a number that corresponds to a text description. For example 1 corresponds to G/L Account, 2 Item, etc. The option value doesn't mean much ...
Slowly Changing Dimensions (History)
Slowly Changing Dimensions (History) Overview Slowly Changing Dimensions (SCD) enable an organization to track how dimension attributes change over time.  For example, it is possible that an item may be associated with a particular product group code but that it is later reclassified into a different product group.  The organization wants to be able to analyze the historical sales data that...
Jet Analytics CDATA Connector Types
Jet Analytics CDATA Connector Types The article refers to Jet Analytics version 2019 and higher. Overview The Jet Data Manager can connect to a wide variety of data sources through CDATA connectors.  See the article on ADO.NET Data Providers for an overview and comprehensive list. Data Providers More in-depth information is available for many of the suppor...
Table Inserts
Table Inserts Overview The Jet Data Manager allows you to write your own Custom Table Inserts . This gives you more flexibility compared to the Table Inserts feature already available in the product. Adding a Custom Table Insert To add a Custom Table Insert to a table, follow the steps below. On the Data tab, in a data warehouse, right click on the table you...
Add a Week Level to a Date Dimension
Add a Week Level to a Date Dimension Overview The Jet Analytics standard project contains a Date dimension that contains the following attributes: Year Quarter Month Day In order to incorporate a Week level into the date dimension we have to modify the project. Modify the Date Table 1. In the staging database, right-...
Advanced Dimensions (NAV 2009 or Older) in the JDM
Advanced Dimensions (NAV 2009 or Older) in the JDMRelated Articles... Overview This article explains how to add Advanced Dimensions from NAV 2009 and older to a project using the Jet Data Manager . Dimension Tables First we need to create a new dimension table in the Data Warehouse database that we will use later on to link to our fact tables. Second ...
Implementing a 4-4-5 Calendar
Implementing a 4-4-5 Calendar Overview The 4-4-5 calendar is primarily used for the analysis across accounting periods equally.  The same applies to the other well-known 4-5-4 and 5-4-4 calendar variants. Process It is strongly recommended that only users with an advanced knowledge of SQL attempt the steps contained in this article.  If this customization needs to ...
Hierarchy Tables in the Jet Data Manager
Hierarchy Tables in the Jet Data Manager Overview The Jet Data Manager allows one to create a hierarchy table.  Hierarchy tables are often used to describe parent-child relationships within a single table.  These tables can then be modeled as dimensions in an OLAP database.  In this article we will be building a hierarchy table that describes the relationship between salespersons and...
Creating a Named Set
Creating a Named Set Overview A Set is an expression that defines one or more dimension members.  These sets are created using the MDX (Multidimensional Expressions) query language.  Using the CREATE SET statement one can alias the set for reuse. This article provides an example, constructing a named set and applying it to a project. Process Create your Set Th...
Using SQL Unpivot
Using SQL Unpivot Overview There are many instances when the data supplied to you is not in a format that is conducive to database import.  In many situations, this is because the data supplied was originally produced within a Pivot-table, but can also occur in rolling-window reporting with things like months being placed across columns.  This formatting is quite common in a variety of ...
Adding Dimensions to a Project (AX 2012)
Adding Dimensions to a Project (AX 2012) Overview The dimension structure in AX 2012 and later has changed significantly from AX 2009 and earlier.  The dimension structure now follows a dimension set methodology and key dimensions are no longer posted into the transaction tables as they were previously, but rather there are lookup tables that contain the dimension information and the transa...
Cube Perspectives
Cube Perspectives Overview Cubes can be very complex for users to explore. A single cube can represent the contents of a complete data warehouse, with multiple measure groups in a cube representing multiple fact tables, and multiple dimensions based on multiple dimension tables.  Such a cube can be very complex and powerful, but daunting to users who may only need to interact wit...
Dimension Display Folders
Dimension Display Folders Overview Within the Jet Data Manager, the individual attributes on a dimension can be organized in folders. This is very helpful when the dimension contains many levels (attributes). On the dimension node, a default folder can be entered. The default folder will be used for all attributes that does not have another folder assigned. Process ...
Dimension Hierarchy Properties
Dimension Hierarchy Properties Overview In the Jet Data Manager , it is possible to manage dimension hierarchy properties to have flexible control over the hierarchies. This article outlines how to set this and what the properties represent. Process To set dimension hierarchy properties, first right-click the dimension hierarchy that you wish to set the properties f...
Default Measure Setup
Default Measure Setup Overview The default cube measure is used every time you drag and drop a dimension member in your browser/excel without using a measure. This can result in bad performance if the wrong default measure is chosen. Process The default measure is a toggle function and can be applied to one of the standard or derived measures in the cube.
Copy Table Structure in the JDM
Copy Table Structure in the JDM Overview A structural copy of a table can be useful in many scenarios.  It can be used as a destination of a “Table Insert” on the staging area and it is very useful on the data warehouse level if you need a second copy of a table with many data movements for each field. This feature enables you to create a structural copy of a staging or data ware...
Junk Dimension Automation
Junk Dimension Automation Overview The Junk Dimension contains a row for all distinct combinations of the Junk Dimension attributes along with a key that identifies the specific combination. The Junk dimension attribute fields can be removed from the fact-table and replaced with the single-field reference to the junk dimension table. Example In this example, the fact table con...
Setting the default member of a dimension
Setting the default member of a dimension Overview When a default member is not specifically set, Analysis Services will elect the first member in a hierarchy as the default member. For all dimensions this will be the All Member unless the All Member has been hidden (see Hiding the AllMember in an OLAP Dimension ). This document will describe how to set the defau...
Hiding a cube dimension
Hiding a cube dimension Overview Dimensions in a cube can be hidden using the XPATH XMLA capabilities in Jet Data Manager. This is especially relevant if dimensions have been added solely for drill-through purposes. By hiding the dimension, we can ensure that the front-end user does not (by mistake) drag a large drill-through dimension into a query which may subsequently bring the OL...
Stored Procedures, User Defined Functions, and Script Actions
Stored Procedures, User Defined Functions, and Script Actions Overview In some cases you need to achieve a task that is not possible through the standard features in the user interface. In these cases you can create your own stored procedures, user defined functions, or even whole script actions to achieve your tasks. This document will describe how to set up stored procedures, user ...
Adding Dimension Properties
Adding Dimension Properties Dimension Properties will not work with a Tabular Model Overview It is possible to set up dimension properties in an OLAP cube dimension that allows end users to see certain properties that are associated with a dimension value without having these properties available in the dimension hierarchy.  This allows users to access dimension properties in a repor...
Referenced dimension setup
Referenced dimension setup Overview It is possible in Analysis Services to reference a dimension through another dimension. This way you can avoid creating a snowflake dimension or having to add the attributes to the data warehouse table as lookups. Process Add the dimension you want to reference to the cube dimensions In this example we want to reference the Count...
Database Schemas
Database Schemas Overview It is possible to define database schemas in Jet Data Manager. This is useful when users are accessing the data warehouse and you want to limit the number of tables the users see since only the Valid tables are relevant for reporting users. Setting up a Schema at the Datasource level (this feature is available in JDM version 2017 and higher) ...
Implementing Multi Currency
Implementing Multi Currency Overview We will look at how to implement multi currency using the Currency business function. Prerequisites Before you can implement the business function, you have to insure a proper exchange rate table in your data warehouse. This table has to have the following fields: Company, Currency Code, Date, Exchange Rate. It also has to have a record for...
Hiding the AllMember in an OLAP Dimension
Hiding the AllMember in an OLAP Dimension Overview By default, an All Member is created when creating a dimension.  The All Member holds the aggregated value of all members in the hierarchy in the dimension. From a design (as well as performance) point of view, this is desirable and works with almost any dimension.  However, in some rare cases, the All Member does not make sense, i.e. Budge...
Many to Many Dimensions
Many to Many Dimensions Overview Data structures do not always conform to the snowflake or star schema model where one fact is associated with a single dimension member. For example, in a typical cube analyzing sales data, a single sales transaction is associated with a single customer, a single product, and a single point in time. Data structures can be more complex. For example, co...
First Page
Back
For an optimal Community experience, Please view on Desktop