Feeds:
Posts
Comments

As explained in the How To Export A timeXtender Project, it is possible to export a project definition (the meta data) into a XML file. This would not be very useful, unless it can be imported again :-)

The following explains how to import a project definition from a file

How to import a project

  1. Click the timeXtender ribbon menu icon in the upper left corner
  2. Select the Import/Export menu option
  3. Select the Import Project… menu option
  4. In the dialog that open, click the ellipsis button
  5. Select a file and click the Open button
  6. Click the OK button to start the import

Project Import Dialog

 

A note on version control

The project that is persisted in a file contains a single version of the project.

Every project is assigned a unique id when it is created (a global unique identifier, or in short a guid). During import this id is compared to existing project in the repository and if a project with the same id exists, you will see the following prompt

Project Import Dialog II

Save project as latest version

This will save the imported project definition into the repository, essentially “overwriting” the existing project as the latest version of this project.

 

Save project as a new project

Consider this like a save as function, that allows you to save a copy of the imported project with a new name and id, side by side with the original project in the same repository

Next you will be see the following prompt

Project Import Dialog III

Then you will be asked to specify a new name for the imported project

Project Import Dialog IV

 

The connection manager

Following a successful import, a prompt will ask if you want to run the connection manager. This provides a wizard style interface to set up all connections on the imported project

Project Import Dialog V

In timeXtender there is a feature called multiple environments, that automates the process of deploying project definitions between environments such as from development to test or from test to production.

If you ever need to manually copy or move a project definition between environments, the import and export features can be used.

The following is a very short guide that shows how to export a project.

How to export a project

  1. Click the timeXtender ribbon menu icon in the upper left corner
  2. Select the Import/Export menu option
  3. Select the Export Project… menu option
  4. In the dialog that opens, specify a location and name for the file
  5. Click the OK button

The export file is created in XML format, thus it is recommended to use a .xml file extension.

Project Export Dialog

 

A note on version control

The export function will persist the project “as is” in memory to the file system, thus the file will not contain any history about previous versions of the project.

If you follow the Kimball dimensional modeling approach, most dimensions will be star dimensions, some might be snowflake. Sometimes a business process will not fit this model, where one fact is associated with a single dimension member. This is when many to many dimensional modeling can be applied.

The example for this post will be the joint bank account scenario (accounts with multiple owners). I know this has been used over and over again, but hey this gives you the chance to make a direct comparison of the implementation in timeXtender with BIDS.

The joint accounts example

I will look at Jane and John, a couple with both personal and joint accounts

  • Jane has a personal checking account (balance 1000)
  • John has a personal checking account (balance 1000)
  • Jane and John have a joint savings account (balance 1000)

In this example I want the balance of the joint saving account to appear under both Jane and John. The main purpose here is to show how the many to many model affects the aggregation over the dimensions (the total is not equal to simply the sum of all members).

The model can be further refined by i.e. adding a ownership calculation to split the balance, but this will not be he focus of this post.

The following Excel view of the finished cube shows that Jane and John have a balance of 1000 each on their personal checking accounts, and both have access to the 1000 on the joint savings account. At the same time, the total balance of all accounts is still only 3000.

 

M2M Excel Pivot

 

The data model

The data model for the joint accounts can be seen below. In a real life scenario I would use surrogate keys, but to keep the focus on solving the main problem here, I will be using natural keys.

By examining the structure it appears that there is no direct relationship between the fact and the customer dimension tables (fact Account Balance and dim Customers). This relationship can only be established using the many to many join table (factLess CustomerAccount).

 

M2M DW Tables

The OLAP model

I trust that you already know the basics of how to create dimensions and cubes in timeXtender, thus I will only cover the specifics needed to add the many to many relationship.

The OLAP model will consist of the following

  • Account dimension
  • Customer dimension
  • Account Balance cube with fact tables (measure groups)
    • fact Account Balance (for balance measure)
    • factLess CustomerAccount (intermediate fact w/dummy measure)

Prior to adding the many to many relationship, the model will look this in timeXtender

image

 

Please notice that the Customer dimension is not related to the fact Account Balance fact table

M2M Dimension Relations

 

Adding the many to many relationship

The steps to add the many to many relationship are

  1. Right-click the Customer dimension under cube dimensions. On the context menu, select the Add Many To Many Relation
  2. In the dialog that appears, select the fact and factless tables as shown below

M2M Many 2 Many Join

 

The many to many relationship now appears under the cube dimension

 

M2M OLAP Model Complete

Agile and rapid are some of the most frequently used buzz words in the data warehousing community these days, so I thought it was about time to put these in context of timeXtender.

Those who know our tool and company are well aware, that timeXtender was created from day one for agile implementation of ETL and data warehouses and that the tool is a rapid development tool.

Just to set expectations right, this and following posts will not be an attempt to cover in complete any specific agile methodology like scrum, just as it will not be a complete coverage of the Kimball dimensional modeling approach.

Agile methodologies

In software development there are several iterative and incremental software development methodologies commonly referred to as agile software development methodologies, including scrum and extreme programming.

In agile methodologies, teams are usually cross-functional and will most often include a customer/business representative. There is no fixed team size, but they are generally quite small from 3-5 people.

User stories is one of the artifacts commonly used in agile development and one that I will emphasize for use with agile dimensional modeling.

Dimensional modeling

Kimball encourages using the four-step dimensional design process to consistently consider four steps in a particular order. In short the steps are

  1. Select the business process to model
  2. Define the grain of the fact table
  3. Define the dimensions for the fact
  4. Define the numeric measures of the fact

Consistency in the model using conformed dimensions and facts is key to a scalable data warehouse. The star schema(s) that is the result of the dimensional design process is usually considered the smallest deliverable and often represents several days or weeks of work for developers to build.

Agile + Dimensional Modeling

The ever changing requirements for data warehouses today makes it natural to adopt elements from agile methodologies, to be able to keep up with the changing requirements. If you are not able to deliver in very fast, short iterations, chances are that your dimensional models will be outdated before they get implemented.

I do not remember a single project, where the customer did not have a few requirements, that was key to the business and represented urgent needs. It usually does not take much analysis and discussion to agree, that these early identifiable requirements should be implemented first.

It is about getting actionable information as fast as possible, mainly because it serves a urgent business need, but in todays economy projects are also under pressure to deliver return on investment faster than ever before.

Agile + Iterative

Agile and iterative really goes hand in hand, as agile development promotes a set of techniques using iterative development for rapid delivery. As agile traditionally focuses only on the development part of the data warehouse lifecycle, I suggest to focus more on being agile rather than using agile. This will include being agile and using iterative techniques in the requirements specification phase also.

At a high level the agile approach can be illustrated by the following Iterative Lifecycle

In the next few posts, I will introduce user stories and prototyping around dimensional modeling as a way to remain true to a scalable dimensional model, while using a deliver fast, deliver often approach.

At the BI Day conference in Belgium last week, one of our partners mentioned that he had just been with one of his consultants at a customer, and had seen a cool timeXtender feature that he did not know existed. As he is most likely not the only one, let me tell you about the timeXtender remote control.

As we are speaking about the BI-Day conference, I cannot help pointing your attention to the fact that two out of four vision awards for business impact were won by timeXtender powered solutions. Cisco won in the private sector category and Fleet Support won the EMEA category.

What is the Remote Control?

The remote control is a small system tray application, that enables remote execution of a timeXtender project without the need to install the full client. It is also known as “The CEO button”, because of its simple interface that allows execution with a single click.

Prerequisites

The remote control uses the multiple environments feature of timeXtender for remote execution, thus you need to be on either corporate or enterprise edition of timeXtender and have a server environment running.

System tray icon state

The remote control appears as a led light icon in the system tray (right side of the Windows taskbar). The icon will appear in one of the following colors

led_green

Application ready
Last execution was successful

led_yellow

Project is being executed

led_red

An error occurred during last execution

Step 1 – Configuration

Righ-click the icon and choose the settings menu option. In the settings dialog, choose a server connection. The server settings will match those of an environment configured and running in the timeXtender server service.

Remote Control Settings Entered

Step 2 – Select project and package

Use the Projects and Packages menu option to display the list of projects and execution packages available on the server. Select a package and use the Set as Default button.

Remote Control Projects and Packages

Step 3 – Execute the project

To execute the execution package selected above, use the Start update menu option. This will connect to the server, load the project and execute the execution package. While the package is executing, the led will change to yellow and subsequently into green or red, depending if it was a successful or failed execution.

A very common question we get at timeXtender, is why SSIS has to be installed on the same machine as timeXtender. The easy and straight forward answer to this is “as per design by Microsoft”. If you would like a few more details about this limitation, please read on.

SSIS vs. DTS

Data Transformation Services (DTS) is the predecessor to SSIS. The DTS component was a redistributable client component, and did not require a separate server license to be used from any client machine.

SSIS is not redistributable and this has been the case since it first came out with SQL Server 2005. You need a full server license on any machine on which you want to run a SSIS package. If you follow the reference link below, you will see the following

“Although installing BI Development Studio lets you design packages, running packages outside the development environment requires the installation of Integration Services. Subject to the terms of your SQL Server licensing agreement, you may be able to run Setup again to add Integration Services to your installation. Integration Services is now a server component and is not redistributable to client computers in the same manner that the components required for SQL Server 2000 Data Transformation Services (DTS) were redistributable.”

Remote Execution (Lack of)

There are a number of ways to execute a SSIS package (DTEXEC.EXE, DTEXECUI.EXE and SQL Server Agent). But SSIS does not have a remote execution feature, that would allow execution of a package from a remote machine, such as an application server with tools like timeXtender. We can quite simply conclude, that the machine that executes a SSIS package, needs to have SSIS installed.

SSIS and timeXtender

SSIS does not have to be installed on every machine running timeXtender. SSIS does need to be installed on every machine that needs to deploy and execute SSIS packages. SSIS needs to be installed on the following

  • The machine running the timeXtender scheduler
  • The machine running the timeXtender server (multiple environments)
  • The machine that reads the source schema (unless using ADO.NET)

References

One of the few places the license model has been documented by Microsoft, is in The Product Level Is Insufficient topic. This is the error message you will receive, if you attempt to run a SSIS package without a license.

In this post I will show you how to use prototyping to demonstrate business requirements and get rapid and actionable feedback. How you got the business requirements to demonstrate, is not important for the purpose of this post, but assuming that you have read some of my earlier blog posts, it should be clear that I recommend taking an agile approach.

The goals for the prototype

  • Demonstrate business requirements
  • Deliver fast, using minimum resources
  • Minimize rework

I am going to build a prototype using the dimensional model approach as per best practice. Although not strictly necessary for the purpose of a prototype, I am even using surrogate keys, as I will most likely reuse the prototype for the permanent implementation after one or more very short iterations.

The four easy steps

  • Build the data warehouse star schema
  • Enter sample data in the model
  • Build the dimensional model in OLAP
  • Build a test front end in Excel using Pivot Tables

Step 1 – Build the data warehouse star schema

In a dimensional model, the business requirements can be illustrated as this star schema (also known as a star join schema). For those unfamiliar with dimensional modeling, the yellow table is called a fact table. It is the center of a star schema and represents the numeric measurements of an event and has keys (links) to the dimensions that describe the event. The blue tables are called dimension tables. In this example we can describe an event: A sale and describe it in the context of customer, product sold and the point in time at which the event took place.

Remembering that the purpose is to demonstrate the business requirements as fast as possible and with a minimum of rework, I will implement the dimensional model above with manually entered sample data in the data warehouse database. When I build the dimensional model in the timeXtender meta layer it will look like this. Please note the red fields at the bottom of the fact table. These fields are lookup fields that are used to translate the natural key joins between my dimension and fact tables into surrogate based joins.

Step 2 – Enter sample data in the model

Using the custom data feature in timeXtender, I will enter just enough data to be able to demonstrate the model. As a side note, you would be amazed at the difference it makes to your business stakeholders, if you use recognizable data compared to just more or less random values.

At this point in time, the model including the manually entered demo data only exists in the timeXtender metal model in memory. If I use the deploy and execute features at this point, timeXtender wll generate all necessary SQL code in the physical data warehouse database, load it with demo data and save the project definition to the timeXtender project repository database. The processed fact table will look like this.

  • If you do not need a multidimensional representation for your data (an OLAP cube), the data are at this point already accessible from your front end of choice.

Step 3 – Build the dimensional model in OLAP (Analysis Services)

I will use a drag and drop approach to create a very simple OLAP representation of the star schema. This will include standard dimensions, standard measures and will look like this in timeXtender

Step 4 – Build a test front end in Excel

I will use the most common of all BI front end tools to build a view on the data that I have just built. A Pivot Table is an excellent way to get a feel of the dimensions and measures of a cube.

What is next?

The Excel workbook I just created can now be delivered to my business stakeholders. They can then use this to verify the business requirements and provide instant feedback. The business stakeholders might come back with extensions or modifications to the dimensional model after seeing the first result. This might lead me down one or more additional iterations of the above steps, adjusting the model according to more detailed requirements. This is all fine and exactly why prototyping is such an efficient way to interact between data warehouse developers and stakeholders. The time I spend at this early point in the process will save me from late and costly rework.

Once the model has been verified I can continue to identify data sources needed to load actual data into the dimensional model just built. I will most likely need to make minor changes to the dimensional model on the data warehouse, such as different data types for non system fields. Except for these minor changes, the entire dimensional model including the cube can be reused and put into production after proper testing with real life data.

 

Video link

My colleague Thomas Lørup Duun has made a video on this topic available on YouTube. The video is actually only 15 minutes in real time, so the header in this post is a bit conservative :-)

%d bloggers like this: