SQL Repository/Version 8

From XLCubed V8, XLCubed Web Enterprise Edition can store its repository of published files in a database rather than on the server's file system. This method allows for finer grain control of various aspects of repository access, as well as a richer interface:

• new Excel-based dialogs for controlling permissions and access to reports and folders
• full version history of published items, as well as locking the live version
• logging of report accesses, including rendering times for usage and performance monitoring
• integration with any present domain and the ability to set up custom groups
• Extended options around integrating with existing "Forms Authentication" websites

In addition to these features, the SQL Repository feature also enhances the following:

• scheduling: this is also accessible from Excel, and features an easier to use interface as well as integrated run history
• error logging: available from a dedicated server administration application, this can be used on any machine and user that has server access

Installation

1. You must be running SQL Server 2008 or above, any edition is fine and it can be run on a different server to XLCubedWeb without any changes to the setup process
2. Create a blank SQL Server database, and grant full access to it for the user doing the installation:
   
   • Set the "Recovery Mode" to "Simple"
3. On the XLCubed Web Edition server, run the Configure XLCubed Web Edition application
4. Select the appropriate Web Edition instance (this will usually be done for you)
5. Switch to the Sql Repository tab:
   
6. Select the Use SQL for XLCubed Repository box
7. Enter the server and database names for the previously created blank database, and click Connect .
8. The application will ask you if you want to populate the database. Click Yes :
   
9. Check the Domain name and click the Load Domain Information button to load the users and groups from the domain
   • To use the user and group information from the host machine rather than a domain, use localhost in the Domain name box.
   • To allow XLCubed to automatically refresh the Domain information, switch to the Scheduling tab and tick the Schedule domain update box. Scheduler must be set up correctly for this feature to work.
   • To limit the domain retrieval you can enter a semi-colon delimited list of Ldap queries instead of the domain, for example:

 LDAP://OU=Development,DC=somecom,DC=com; LDAP://OU=Consultants,DC=somecom,DC=com

1. If desired, click the Import Repository button and enter the path to import an existing file system repository. Existing permissions will be transferred wherever possible but this should be verified once the system is available again as an initial test.
2. Using SQL Server Management Studio, you should now add a Login for the XLCubed application pool user (if the default XLCubed application pool is used with the Application Pool Identity, then you can use IIS AppPool\XLCubed as the user name)
3. On the User Mappings screen map that user to the XLCUBED_USER role
4. If any users should be able to administer the repository (see below), map those users to the XLCUBED_ADMIN role
5. If you need to restrict access to XLCubedWeb:
   • In the Configure XLCubed Web Edition application, click the Administration button
   • Select the Admin > System Settings menu item
   • Select the group that should be allowed access to the Web Edition then click the Save toolbar button:
     

Scheduling

If Scheduling is desired, a few additional steps are required to use it with the SQL Repository.

1. On SQL Server Management Studio's User Mappings screen, map the scheduling service user to the XLCUBED_SCHEDULER role
2. In the Configure XLCubed Web Edition application, click the Administration button, then select the Admin > System Settings menu item. Choose the Scheduling user from the list then click the Save toolbar button
3. Select the Admin > Scheduling > SMTP Servers menu item and add the details of the SMTP server
   • The ID and uniqueid columns will be filled in automatically
   • To use an SSL connection, enter Y in the Use SSL column
   • To allow the scheduler to use the server to send emails, enter Y in the Is Global column
4. Select the Admin > Users or Admin > Groups menu item, and add the Can schedule reports and the Can edit schedule distribution groups where appropriate.
5. Schedules can now be set up either:
   • Through the Web Edition as normal
   • For a more streamlined approach, through the Excel Edition, select the XLCubed ribbon > Publish to Web dropdown > Scheduling... ribbon button. Once you have connected to the server, you can then add new scheduled reports and manage existing ones

Administration

There are two applications that can be used to administer the SQL repository:

• Configure XLCubed Web Edition (included in the XLCubed Web Edition setup, and installed on the server's start menu)
• Administer XLCubed Web Edition Repository (optionally installed separately on any machine)

Either of these will allow you to:

• Administer users, including giving them access to the XLCubed Web application, allow and deny them access to the repository, their personal folders, scheduling and scheduling distribution groups
• Administer custom groups, including adding and removing users. You may also designate a group for XLCubed web access, as mentioned above
• Control folder access and permissions
• Control startup reports for users and groups
• Manage SMTP servers for scheduling
• View logs

Operational Reporting

The installation provides an operational reporting dashboard as shown below:

The Excel file can be accessed through the XLCubed Administration Reports Application, or directly in the \Reports folder within the XLCubed Web Website. Additional operational reports can be built using the oData datasource provided with XLCubed Web (accessed through Grids & Tables - Other Sources).

You can enable at a group or user level who has access to these reports:

See Also

• Scheduling