The BI Portal offers custom value formatting, which makes support for Time Spans easy.  Here is a snapshot of the 3CX cube data, displaying Talking Durations as well as common Time Calculations, automatically done in the cube:

SSAS handles by default the selection using a logical “OR”. In the previous example, both products will be displayed since both of them contain Feature 1.   The resulting matrix from cross joining the products with the features generated in unusable number of records, even after applying the “Feature” filter.  This was an acceptable solution.  The client wanted to see ONLY the products, which had all the selected features.   In technical specs, a logical “AND” must be applied on the “Features” filter.  

I came with a solution using the “Existing” function to detect what features were selected in the filter and then count them.  The products and features dimensions were joined thru a regular relationship to a measure group called “ProductFeatures”.  The measure group contained “Feature Count” measure, which did simple count of how many features were in a given product.   If the feature count and the “existing” count were the SAME, then the product should be displayed.  So the MDX prototype was “with member [Current Feature Count] as iif(count(existing [Features]) = [Measures].[Feature Count] , [Measures].[Feature Count], NULL) select non empty [Current Feature Count] on 0, [Products] on 1 from [Features]” and worked fine in most cases.

In some cases, the above solution does not work.   The reason for that was the difference in the behavior of the “Existing” function.  If the “Feature” filter selection was placed in the “WHERE” clause of the MDX statement, then it worked as expected.  BUT if the filter selection was placed in the sub-cube clause, then the “Existing” function returned all feature members (unless the Feature dimension was placed on one of the Axis).  That made no sense but there was nothing to be done to change the “Existing” behavior.

Here are some examples of the “Existing” function behavior:

a)  “select…from…where ({Feature1,Feature2})” – this works fines with a count of two (out of 10 total features) – since the “feature” dimension is the “WHERE” clause (i.e. one of the Axis).
b) “select {Feature1,Feature2} on 0 from…” – this works fines with a count of two (out of 10 total features) since the “feature” dimension is selected on one of the Axis.
c) “select…from (select {Feature1,Feature2} on 0 from….)” – this does NOT work and comes with a count of 10 (out of 10 total features) since the “feature” dimension is NOT placed on one of the Axis.

Again, the solution with counting the existing members works fine for most cases.  If the client software generates MDX with a “sub-select/sub-cube/” then the above solution will NOT work due to the misbehaving “Existing” function. 

I took it as a challenge to make the solution suitable for all client tools (including filters in the sub-select/sub-cube).   I did some digging and found that ASSPs were needed to detect what members were selected in the “sub-select/sub-cube”; basically fix the “Existing” behavior issues.  I added some new functions to the BI Portal SSAS DLL and was able to implement the solution with success.  Yes, it’s possible but it’s not an easy task.  Here is a snapshot of my end result:

The popup dialog box (shown below) allows the user to configure the subscription. Most of the configuration options are intuitive and self-explaining. For example: a) Description (i.e. Daily Sales Report Delivered at 4am via Email); b) Delivery Method: Email or File Share; c) Recipients email addresses; d) Rendering Format (i.e. PDF, Excel, Word, Image, HTML, etc.); e) Delivery Schedule (i.e. Time, Frequency, Private or Shared); f) Report Parameters values; and more. The options are well documented in the SSRS on-line help.

The BI Portal extends the default subscription capabilities by offering the option of report delivery based on a condition or business rule. The condition is defined either by a) using a statement (which can be configured by selecting the data source and providing the statement); or b) using a report (the UI allows for report selection) – in both cases, the statement and the report needs to return a single cell, which evaluates to either “0/1” or “True/False”). If the value is “1/True”, the report will be sent. This is a powerful feature, which allows the user to receive reports ONLY when a certain condition is met – for example: if the Sales are down 10 percent from last week, then the report will be sent. This will eliminate a lot of unneeded reports, saving the server resources in preparing  them and user time in reviewing them.

The final step is for the user to save the new subscription and optionally, test it by clicking on the “Run” button. Hint: all the user subscriptions are listed in the left panel; each subscriptions has a different icon indicating its type. If the icon contains a database image, the subscription is data-driven. The user can also click on the “Refresh” button and check the status of the selected subscription in the right panel at the top “Status” label (i.e. after Running the subscription manually)



The DDS is a variation, which allows the distribution of a given report to multiple users while customizing its content for each user. The user needs to provide a SQL statement, which retrieves a list of users as well as their preferences for the report customization/rendering. Any of the options listed under the Standard Subscription (and described above) can be customized. For example: the SQL statement “select 'User1' as [To], 'PDF' as RenderFormat union select 'admin' as [To], 'Excel' as RenderFormat” can be used to create a DDS, which will target “User1” and “admin” as the recipients, and the report will be rendered in PDF and Excel formats for each user correspondingly. Think of the DDS as a preliminary step, during which the SSRS engine executes a statement and retrieves a record set from a relational database; for each record, the engine create on-the-fly standard subscription using the values in the different fields (i.e. [To], [RenderFormat], [IncludeLink], etc.). Note: For options which are not included the statement, the engine will use the default option provided in the standard subscription definition. The user needs to provide a mapping between the resulting set columns and the subscriptions options.

How can we create a DDS in SSRS? The steps are the same as for a Standard Subscription, but in the context menu the user needs to select “Create Data-Driven Subscription”. The popup dialog and settings are the same as before, except for “Data-Driven Parameters” extra button at the bottom (highlighted in Red in the snapshot below). This button allows the DDS specific options to be configured; clicking it will display the dialog below. The user first needs to select what Data Source will be used for executing the SQL statement/query and retrieving the users’ specific settings. The Query/SQL statement is provided next; it defines what fields/columns will be returned as well as the actual data (which usually comes from a relational DB table). The “Get Fields” button will execute the statement against the “Data Source” and will retrieve a list of fields/columns; this list will be available in the combo boxes in the next section. (Note: The dropped down combo box in the snapshot displays the list of returned columns after clicking on the “Get Fields” button.) The user then needs to provide the mapping between the returned fields/columns/ and the DDS settings/options/. In the example below, only the “To” and “Render Format” settings are mapped/set/ - this basically says: for each record in the returned set, use the “To” column value to get the recipient list, and the “RenderFormat” column value to specify the user preferred report format (i.e. PDF, Excel, etc). The final step is for the user to save the subscription and optionally test it by hitting the “Run” button and “Refresh” to check for errors.

To summarize the DDS execution flow: the SSRS engine uses the selected Data Source to execute the specified “Query”. The returned records are iterated, and for each record, the specified mapping is used to “fill in”/configure/ the currently created subscription – i.e. the “To” column will give the recipients list, the “RenderFormat” column will provide the current user preference, and so on. IMPORTANT: the Data Source MUST NOT require any user input (such as credentials) or any configurations coming from the assumption of an active user; the data source (DS) is used WITHOUT a current user or any user interactions.  The engine should be able to open the DS connection directly in “lights-out” fashion. In other words, specify in the DS definition the user credentials – do NOT use “current user identity” settings since there is no current user during the DDS execution!

Everything so far is standard DDS functionality. The BI Portal adds the “Report Delivery Condition”, which is a very powerful feature in controlling when a report will be sent out to a given user; this option is covered above and it’s behavior is the same with DDS. A good example will be the distribution of the “Daily Sales” report to all the sales managers in a company; if the business rule for sending the report is the same one from above, only the managers with declining sales from last week will get the report!

The information so far is a coverage of the Report Subscription and the DDS topics. The blog is about Custom Security and DDS. This is where the BI Portal solution shines.

The DDS is a very powerful feature for the Windows-Domain Enterprise Environment but it’s not very Internet friendly. In other words, it does not work well with Custom/ASP.NET Membership/ security, The BI Portal extends the DDS and makes this feature available for Internet Users. I have covered in a previous blog how our solution offers a custom security with a Single-Sign-On (SSO) for SSRS, SQL, and SSAS. This functionality is extended to cover the DDS as well. When a Data Source is define with the ESS Data Source Extensions for MS SQL and SSAS (and OLEDB Sources), there is an option, which says that “Credentials are not required” – this enables the SSO identity and its flow into the DDS.
When the DDS is executed, each user is impersonated when the data source connection is opened, and the data is filtered using the user’s identity! This customizes the report content to match the security clearance of each user. (Note: the alternative solution are a) to create multiple data sources (with different credentials) and matching reports (not a DDS solution); or b) to use “Expression Based” connection string, or c)… in any case, the resulting solution is very complicated and difficult to support and extend)
Here are some examples of the BI Portal DDS solution at work. The snapshot bellows shows several pre-defined subscriptions; the top three are DDS’ (with the DB image in the icon). All of them have been created with the steps described above. The BI Portal Data Source Extensions are used in the reports so SSO can be used during the DDS. Two users are used in the tests: “User1” and “Admin”. Both MS SQL and SSAS data sources are used by the subscribed reports to demonstrate the data filtering capabilities for the different users.

Let’s start with the “GIS Report” report, which uses the SSAS as a data source ; custom security is in place. When I click the “Run” button, I receive two reports: one for “User1”; and another one for “Admin”. Here is a snapshot of these reports. A you can see, “User1” gets the report as a PDF file and can only see “US and Canada”, while “Admin” can see all the regions and prefers the Excel format. One Data Source, One Report – multiple users served. 

The second report is the “Sql Security Test”, which uses the MS SQL as a data source and custom security with SSO. Running the subscription results in two emails with embedded report. Here is a snapshot of the reports. Again, each user only sees the records, for which s/he’s been given security clearance. 

The DDS feature requires the Enterprise Edition of SSRS. The BI Portal solutions brings this feature to the MS SQL Standard and Express/Free/ Editions. One of our clients requested the DDS concept but with custom reports; the BI Portal offers ad-hoc reports based on SSAS. We offered a custom service, which prepares and sends ad-hoc and other custom reports (i.e. by “custom”, I mean non-SSRS reports). Eventually, we discovered that the DDS boils down to executing a SQL statement against a Datasource and retrieving a record set.  Then for each records (after making the mapping adjustments), we can fire an event in SSRS and accomplish the same functionality offered by the standard DDS. Since we don’t have any SSRS version dependencies, our DDS implementation supports all version of SSRS including the free edition. In other words, we bring DDS to everyone.

In summary, the BI Portal solution offers custom/asp.net/ security for the Data-Driven Subscription in SSRS. With a single identity (aka SSO), the user can access data in both MS SQL and SSAS. There is no need for the administrator to create multiple data sources and reports – one for each security requirement. The data is securely served on the Internet with filtering done for each user or assigned role. The DDS takes advantage of the custom security and the SSO. With one report and one data source, unlimited number of users with different security needs can be served.

There are several main challenges in securely publishing data on the Internet.  Here are some of them:

1) Integrating Multiple Components

A common Business Intelligence (BI) Solution will require a) a Web application; b) Report rendering engine; c) Data sources, and d) various supporting services including security. All these components usually does not support a common security model.

2) Various Security Needs   

Both row/horizontal/ and column/vertical/ level security are needed. Some examples: a) a manager can only see the data/rows for his/her subordinates; b) an employee can see his/her own HR data but only some columns/fields of his coworkers HR data.   A combination of the two can create a very complex permutation matrix, which can be very difficult and expensive to implement.

3) Multiple Logins

The user may need to provide different logins to the various components, which makes the solution not very user friendly plus it creates a lot of security concerns.

4) Support and Maintenance

The solution can quickly become very expensive and difficult to support.  Different technical expertise needs to be recruited to support it. For example: security expert, web app expert, database expert, report writer, infrastructure expert, content expert, etc.

A common solution will be to acquire third party components/product and to integrate them with their business needs.  While selecting an eye-candy front page is not difficult,  finding or building a solid security platform could be quite a challenge - more specifically, coupling the user web identity with the data source security.   The usual approach to create multiple data sources with different security settings, and then assign them to the different user roles.  This works for simple data security needs but it fails for more complex deployments.   To further the topic, there is also the need for report/data subscriptions.  Most systems assume that the user is currently logged it; what if the user is offline and the report needs to be prepared and send within the user identity!  These are some of topics most BI vendors do not want to talk about.  

The “BI Portal” offers a security solution that addresses all of the above challenges and business needs.  It does that thru the use of a Single-Sign-On (SSO) for the Web app, SSRS, SSAS, and SQL.   The portal supports both Windows security for the Enterprise/intranet/extranet applications as well as the Internet and its custom security requirements.

How is it done?  The portal integrates with SSRS.  It uses SSRS as a document repository and a report rendering engine as well as its security features. (Note: we do also offer a non-SSRS stand-alone SQL deployments.)  The SSRS custom security extension is provides which makes SSRS Internet friendly as well as email enabled.  When the Internet user logs into the portal, his/her credentials are also passed to the SSRS service. When SSRS service needs to render a report, it will forward the user identity to the data sources, SSAS or/and SQL.  This is done thru a custom SSRS data extension, which can be configured to support either per-Role or per-User security granularity.   Both SSAS and SQL data extension supports all the security models offered by these data sources (see my blog on Security in SSAS). 

Here is an-online example of this integration with the SSO using the secured web site: https://biportal.entsoftsol.com/

1)  A business analyst logs into the web site. The landing page is shown below.  It’s called the Report Previewer since it allows the user to get visual preview of the report without the need of opening it. Note:  It is common for users to open multiple reports until they find what they are looking for, creating a lot of unneeded work on the server in report rendering.  This feature solves this problem. The snapshots are NOT shared among users since this will be a security breach.  The snapshot are per-User.

The user wants to create new report, so s/he right-clicks on the “My Reports” folder and select “New Ad-Hoc Report using MS Analysis Services”. This opens a dialog asking the user to select what SSAS endpoint and cube to use.  This type of report allows the user to dynamically slice-n-dice the data by dragging and dropping SSAS object with the mouse.

2) The user drags-n-drops the geo dimension on rows. S/he also creates a custom calculated measure showing her/his user name identity. The UI is shown in the snapshot below. The MDX reveals that in this particular deployment, the “CustomData()” security implementation is used.  As you can see the user identity is shown in the output grid.  The user saves the report by clicking on the toolbar button.  

3) The user wants to convert the ad-hoc report to a standard SSRS report so s/he needs to prepare an SSRS Datasource that will be used by the new report.  The snapshot below shows the definition of the datasource using the Enterprise Software Solutions (ESS) custom SSRS data extension for SSAS.  Notice the checkbox “Credentials are not required” at the bottom of the dialog box; this instructs the SSRS engine to use SSO for authenticating the user!

4) The user is ready to convert the ad-hoc report to a standard SSRS report. S/he right-clicks on the ad-hoc report and select the “Convert” option. (Note: There are three choices: a) static parameters will use what is currently selected in the UI; b) Dynamic values – it will convert any instant filters into parameters; or c) Query-Based – it will allow the user to prove a query for fetching the parameter values).  With a single click, the ad-hoc report is converted into an SSRS report.  

4) The SSRS report using the SSAS data source shows that the identity of the current user is as expected.  This demonstrates the use of a SSO with the web app, the SSRS service, SSAS ad-hoc reporting (aka cube browsing), and SSRS report rendering!

5) The user also create an SSRS report using the SQL Datasource configured for SSO. The snapshot below shows that the same user identity seen by the SQL engine.  Note: For manually creating a report, the user first right-clicks on a folder, and selects a new report, then it expands a data source and drags-n-drops objects in the UI. After the report is save, the user can right-click on the report and continue editing with more precision using the ReportBuilder.

There are a couple of unique feature worth mentioning:

5.1) Expandable Datasource is concept introduced by ESS.  The datasource is presented as a hierarchy of object: from top to bottom – for SQL: Database, Schemas, Tables/Views/Stored Procedures, and Row Data at the bottom;  for SSAS: Database, Cubes/Perspectives, Measure Groups/Dimensions, Hierarchies/Attributes/Measures, Levels, and Raw Data at the bottom. The snapshot below shows some of the hierarchy objects.  The user identity is used when retrieving the metadata/objects so the user can ONLY see what s/he is allowed to see!

5.2) When a new user logs into the portal and requests access to the SQL data via SSO, her/his account is auto-provisioned.  This means that the user is automatically added to the database application roles, and the stored procedure “usp_ProvisionNewUser” is called, passing the new user identity as a parameter.  This allows the administrator to place code inside this stored proc, which can auto-configure new users!  The newly created Application Roles can be viewed inside the SQL Server Management Studio (SSMS) and further configured.  The schema “SqlRoles” is automatically created and all new users are given read/select/ access to it; it create only one table “tblTest” to confirm that the access is working correctly.  The administrator can change the schema and access settings thru SSMS. Note: the SQL Data extension can be configured for per-User or per-Role based authentication; accordingly, application roles will be created for each user or for each role the user belongs to.    

6) The user is given access to a view called “vw_SecurityTestRecords”. This view dynamically filters the records based on the user identity (i.e. “select * from tblTest where UserName  = user_name()”).  The user creates a report based on this table and confirms that only the assigned records are visible.   The user then decides to share his reports with other users. S/he simply copies them the “Demo Customer\Shared Reports” folder. The security is automatically set.

7) “User 1” logs into the system. S/he opens the shared reports.  The SSO identity correctly flows to the data sources, the reports confirm the “User 1” identity seen by SSRS, SSAS, and SQL.  While in SSAS, the previous user can all Geo Country members, “User 1” can only see “Canada and United States”.  The SQL records are also correctly filtered as shown below.  As more users log into the system, they will be automatically provisioned and will have access to the reports with data filtered only for their security clearance.

The above example shows very powerful security features that work out-of-the-box. Even if the BI Portal is not selected as the front-end UI, the security framework is still available for purchase.  The solution is ONLY using industry standards (such as WS, SOAP, XML, RDL) and popular BI products (such as SSRS, SSAS, SQL).  The BI Portal works with all MS SQL Editions from the free SQL and SSRS Express Editions to the Enterprise.  Note: Alternative scaled-down SSAS engine can be offered for deployments where MS SSAS is a major cost consideration.

A deployment document comes with the BI Portal that gives step by step instructions on how to set it up.  The administrator can select from various configuration options. For example, if the SSRS service is running with a low privileged account, the SSAS and SQL data sources can be configured to impersonate Windows accounts with elevated rights while connecting to the corresponding data source.  There is also a data extension for OLEDB data sources.  

The portal also offers advance report subscriptions features and extensions.  A report can be subscribed to by any user and rendered in off-peak hours and emailed to the user.   The report emailing can be configured to be triggered by a business rule – for example: send me only the report if the sales are down 10% compared to last week.  The user identity is preserved so the report is custom prepared for the given user using his/her security rights.

It is important to point out that with a SINGLE Report and Datasource we are able to serve unlimited number of users with unique security requirements! A step further is to host multiple clients on the same web site using the same data sources; each client gets a company-tailored UI while all the reports, data, and servers are shared under the hood. We are currently using this platform to provide BI services to multiple clients while hosting them on the same machine and using the same reports and data sources. The alternative will be multiple reports and data sources, which can be quite expensive to develop and support.

Please, let me know if you are interested in a demo.  I can give you login credentials to the web site listed above so you can try on your own, or we can do an online web session and I can offer you assistance and additional information.