Email member property in Excel sheet for batch scheduled job

When you enable batch processing for a batch scheduled job, the general functionality is to get a number of automated "sub reports" on basis of the main report. The batch job will run through the list of batch members and produce a separate report from each member - using the batch member as a filter for each individual sub report.

Now, these sub reports can be send to a dedicated folder, e.g. resulting in one PDF file per sub report. Or they can be send to an email address, e.g. your own email address. You will then receive X number of emails with one sub report attached to each.

However, the full advantage of batch scheduled jobs is probably achieved when you can combine the individual batch members with individual email addresses via a so-called member property.

In the example below, when the scheduled job is run, Alvaro Bennett will receive an email with a report containing Alvaro Bennett data only; Annunziata Singh will receive an email with a report containing Annunziata Singh data only, etc.

mceclip0.png

A member property is an addition to an existing dimension. So, in the above example, I have created an Email member property to the Salesperson dimension. With the required knowledge, you can create member properties in SSAS projects, in InMemory projects etc. In this article I will show you how you can create the necessary dimension/member property relation via Data Discovery and use it for batch scheduling on reports from your main data warehouse.

The steps you need to take, are these:

  1. Create an Excel sheet (or CSV file or similar) with a table with two columns: One with all dimension members and another with their corresponding email addresses.
  2. Create a simple Data Discovery cube from this table and set up the member property relationship as you do so.
  3. Use Global dimensions to combine the new Data Discovery dimension with the corresponding dimension from your existing main Data Warehouse.
  4. Use the new Data Discovery dimension - including its email member property - in a batch scheduled job.

Excel sheet with email adresses

The Excel sheet simply contains a table with a full list of the required dimension members and their email addresses.

mceclip1.png

Store this Excel sheet in a place where it can be read from Data Discovery, and in a place where it is easy for you to maintain if something needs to be changed, removed or added.

Data Discovery data source and cube

Next steps are to add this sheet as a Data Discovery source and to design a Data Discovery cube from it.

First, you will need to add the Excel file as a Data Source:

mceclip2.png

Here, the Excel file is located on the C drive of the server, but it could also be a shared network drive, a URL or a Google docs etc.:

mceclip3.png

Once the Excel file has been loaded, you can create a Data Discovery cube from it. It is very simple: Simply add the Excel data source to the cube. From the attribute settings, you should expand the primary attribute ('Salesperson' in my example) and add the Email attribute as a Member Property:

mceclip4.png

Global dimensions

In my example, I now have a Data Discovery cube with at Salesperson dimension. From my main Data Warehouse, I have a Sales cube that also contains a Salesperson dimension. The two dimensions are identical in structure and in content, so I will be able to define a so-called Global dimension from them.

The purpose of defining a Global dimension is that you can use either of the original dimensions to filter data from all included cubes. In my example, I will use the Salesperson dimension (with the Email member property) from the Data Discovery cube to filter (batch) data from my main Data Warehouse.

There is a thorough article on Global dimensions here: https://community.targit.com/hc/en-us/articles/360016985417-Global-Dimensions

In short, for my purpose, I have done this:

  1. Created a folder called 'GlobalDimensions' in my C:\ProgramData\TARGIT\ANTServer\Settings folder on my TARGIT server.
  2. Created a text file called 'GlobalDimensions.xml' in this folder.
  3. Added the following content to this file:
<?xml version="1.0" encoding="utf-8"?>
<globalDimensions>
<globalDimension id="global_salesperson">
<metadata database="DemoData" cube="Sales" dimension="Salesperson" hierarchy="Salesperson" />
<metadata database="DataService" cube="SalespersonsEmails" dimension="Salesperson" hierarchy="Salesperson" />
</globalDimension>
</globalDimensions>

Note: In this example, the database called "DemoData" is to be regarded as my main Data Warehouse.

Tip: Use 'Decorations' in the TARGIT Management client to see the correct names of your databases and dimensions in your main Data Warehouse.

Use the Data Discovery dimension for the batch job

Now you can use the Salesperson dimension (with the Email member property) from the Data Discovery cube to batch a report based on data from your main Data Warehouse:

mceclip0.png

 

Was this article helpful?
0 out of 0 found this helpful

Comments

2 comments
  • Hi Ole Dyring!

    To accomplish this are we required to use Data Discovery, or can we simply link related dimensions together using the GlobalDimensions.xml file and then reference whichever dimension in batch scheduling?

    I read the article here, but am still not clear on this.

    0
  • Hi Andrew,

    The main point of this article is how to achieve the 'Email to: Member property' option in batch scheduling.

    A 'Member property' is a term within data modelling that can be translated into 'having multiple entries of information on the same dimension attribute'. In my example, a salesperson can be associated with an email address. He or she could also be associated with a phone number, a department, a social security number etc. All of these can be applied as member properties to the Salesperson dimension attribute.

    Data modelling tools such as SQL Server Analysis Services or TARGIT's Data Model Editor supports member properties, and typically this is also where you would set up emails as a member property to e.g. Salespersons. Maybe your dimensions already has been set up in this way, and then you don't have to do anything else - you can just use the email member property right away within your batch scheduled job.

    This article, however, covers the situation where you don't have the email member property in your Data Warehouse, and, more importantly, you don't have access to the Data Warehouse to implement the email member property.

    BR / Ole

    0

Please sign in to leave a comment.