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.
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:
- 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.
- Create a simple Data Discovery cube from this table and set up the member property relationship as you do so.
- Use Global dimensions to combine the new Data Discovery dimension with the corresponding dimension from your existing main Data Warehouse.
- 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.
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:
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.:
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:
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:
- Created a folder called 'GlobalDimensions' in my C:\ProgramData\TARGIT\ANTServer\Settings folder on my TARGIT server.
- Created a text file called 'GlobalDimensions.xml' in this folder.
- 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:
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.
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
Hi Ole,
I just tried to use this Feature with SSAS Cube, but no success. I created an extra Dimension Attribute which contains the E-Mail Adress. But where must I set the member property as you described in your Article?
"With the required knowledge, you can create member properties in SSAS projects".
Is it the usage type? Any other article that helps in this topic?
BR Marc
Hi Marc,
Just having two separate attributes - one with customer names and another with their email addresses - is not enough.
In SSAS, to define email adresses as a member property to another attribute you can see here how to do it: https://community.targit.com/hc/en-us/articles/360017294298-Member-Properties-Related-Attributes
BR / Ole
Hi Ole, great! Thanks a lot. I didn't found this article. I will check and give you feedback.
Thanks in Advance.
BR Marc
Hi Ole, I just checked and build the Dimension in exactly this way.
But the 3rd Option Button in Batch Reporting Configuration ist still grey and not choosable.
Any Idea?
Dimension:
Batch Reporting:
Hi Ole, I just checked and build the Dimension in exactly this way.
But the 3rd Option Button in Batch Reporting Configuration ist still grey and not choosable.
Any Idea?
Dimension:
Batch Reporting:
Hmm...
Just tried my own recipe, and I end up with this (Phone No as member property Customer Name):
(Used Phone No as an example, as I didn't have emails in these data.)
BR / Ole
Hi Ole, thanks for your help. I*m not sure why, but when I deployed the AS Database again it works fine. Now I wait for the result of the job run.
Great Work !!!
Hi Ole,
Isn't it possible to use email member property in notification agents?
BR/Anne-Mette
Hi Anne-Mette,
No. Currently it is only possible to use email member properties in connection with batch jobs.
BR / Ole
Please sign in to leave a comment.