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

0 comments

Please sign in to leave a comment.