Welcome!

January 5th, 2009 No comments
  • english
  • french

Welcome to Pierrekiroule Consulting!

I share here with you my experience and findings about Business Intelligence (BI), Document Management, or other thoughts, findings and experiences.

To contact me, please send me an email at: consultant (at) pierrekiroule (dot) com

You can check my Linkedin resume and connect to me here (link opens a new window).

Categories: Uncategorized Tags:

Fast switching users in Content Server demo site

November 24th, 2014 No comments

In Content Server, some settings are applied at a user level by the administrator. For instance, the access controls on the content, or the ability to create more or less type of objects in the system.
In order to quickly test or demonstrate this, it is convenient to switch from one user (with administrative rights) to other users showing the applied settings.
Here is one convenient way to do this using a custom view on the front page (Enterprise Workspace):

Screen Shot 2014-11-24 at 10.59.15 AM

fast switching users in Content Server

First, you need to enable “Allow log-in via HTTP GET request” under “Server Configuration > Configure Security Parameters > Log-in Connection Policies” in the admin pages. This may not work in a production environment for security purpose, but is acceptable in a demo or development environment:

HTTP GET request

HTTP GET request

Also, your environment must be using the Content Server Internal Authentication (or use a backdoor to access internal accounts on your server) for this switching to work.

Next, you may want to retrieve some users icons to use on the top row of the custom view (optional). I chose icons with a similar look and feel, and with a 64×64 size. You will save these icons in a location that is mapped in your Content Server Site:

By default, the “http://servername/img/” is mapped to the “..OPENTEXT/support” folder, so you could use this to save your icons in a subfolder within the “support” folder. Check the proper mapping (if different than “img”), and, save the icons in a folder named: “..OPENTEXT/support/appearances/users/”

You will also need to create and know the users needed for switching, together with their passwords (which will be visible to everyone, thus, making this method unsafe for a production environment)

Then you need to create a text file which will be used to build a custom view with the following code, using the “LL.login” function to pass the user name and password, and bypass the login screen of Content Server:

<style>

.center
{
text-align: center;
}
</style>
<table width="100%">
<tr>
<td class="center"><a href="http://<servername>/OTCS/cs.exe?func=LL.login&username=Admin&password=<admin_password>&NextURL=%2FOTCS%2Fcs.exe%3Ffunc%3Dllworkspace"><img src="http://<servername>/img/appearances/users/user1.png" alt="Admin" height="64" width="64"></a></td>
<td class="center"><a href="http://<servername>/OTCS/cs.exe?func=LL.login&username=<user_name>01&password=<user_password>&NextURL=%2FOTCS%2Fcs.exe%3Ffunc%3Dllworkspace"><img src="http://<servername>/img/appearances/users/user2.png" alt="John Doe" height="64" width="64"></a></td>
</tr>
<tr>
<td class="center"><a href="http://<servername>/OTCS/cs.exe?func=LL.login&username=Admin&password=<admin_password>&NextURL=%2FOTCS%2Fcs.exe%3Ffunc%3Dllworkspace">Admin</a></td>
<td class="center"><a href="http://<servername>/OTCS/cs.exe?func=LL.login&username=<user_name>&password=<user_password>&NextURL=%2FOTCS%2Fcs.exe%3Ffunc%3Dllworkspace">John Doe</a></td>
</tr>
</table>

The first row (<tr>) is for the icons, and the second row displays the text.

Replace the values between “<>” such as server name, passwords and user names with actual values, and also check for the image (icons) mapping, as well as the instance name (OTCS in this case, seen both in the beginning of the URL and in the NextURL tag).

Add as many <td> (cells) in each row of the table as you need users to switch.

Create a Customview in the Enterprise Workspace using this text file and test it. Ensure the custom view has proper permissions to allow all users to see and use it. When your user switching works, you can then hide this custom view from the Enterprise workspace.

Categories: ECM, Tech tips Tags: ,

Recursive queries with hierarchical result using SQL Server

February 10th, 2014 No comments

Recursive queries are not commonly used, yet they are very useful to display hierarchical data stored in flat tables.

Unlike the “Connect by” statement in Oracle, SQL Server doesn’t provide an easy answer to build them.

Here is a way to solve this with SQL Server, found after a bit of reading.

Lets use a flat table named “Example” containing some hierarchical data with the following column:

IDNameParentID
1Root NameNULL
2child11
3child21
4Grandchild12
5Crandchild22
6Grandchild13

In this table, any row can have a parent (except the root one), and therefore a hierarchy view could be created:

Root: Level 1Level 2Level 3
Root Name
Child1
Grandchild1
Grandchild2
Child2
Grandchild1

In order to create a query to display this hierarchical view, we first create a recursive source (‘recursive_source’) to provide the expected result. We will need the following 4 columns in our result:

  1. ID: a unique identifier used for joining itself recursively to the parent ID,
  2. The name: this is what we want to display in our report,
  3. The depth level: to be able to indent the result nicely
  4. A sort field: to ensure we display the data in proper hierarchical order
1
2
3
WITH recursive_source( ID, Name, Depth_Level, Sort) AS

(

The first selection is only to get the starting point, the root of our hierarchy (level 1), with the “Parent Name”:

1
2
3
4
5
6
7
8
9
10
11
SELECT    root.ID,

CONVERT(VARCHAR(255), root.name), /* The name, we need no discrepancy in the column type so we convert this into varchar */

1, /* depth level: this is the first iteration, therefore we set it at level 1 */

CONVERT(VARCHAR(255), root.name) /* we will start with the parent name for sorting our output */

FROM Example AS root /* we name this first iteration table "root" */

WHERE root.ID = <parent ID> /* either know the parent ID, or prompt users to select where they would like to start the hierarchical report from */

This is where we create the recursive query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
UNION ALL

SELECT    iteration.ID,

CONVERT(VARCHAR(255), REPLICATE('|---', Depth_Level) + iteration.Name), /* Add "|---" as many time as Depth_Level (= Replicate) in front of the name, this creates a natural indentation of the result */

Depth_Level + 1,  /* increment Depth_Level for next iteration */

CONVERT(VARCHAR(255), RTRIM(Sort) + '|---' + iteration.Name) /* Append the new name at the end of the sort field + "|---" to keep the hierarchical order of the data as previous level are in front of the name */

FROM Example AS iteration, recursive_source /* The source is recursively queried */

WHERE recursive_source.ID = iteration.ParentID

)

Then create the select statement which will display the sorted recursive result with hierarchical indentation:

1
2
3
4
5
SELECT Name 'Hierarchical Structure'

FROM recursive_source

ORDER BY Sort

Here is the full query in one block an no comment:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
WITH recursive_source( ID, Name, Depth_Level, Sort) AS

(SELECT  root.ID,

CONVERT(VARCHAR(255), root.name),

1,

CONVERT(VARCHAR(255), root.name)

FROM Example AS root

WHERE root.ID = <parent ID>

UNION ALL

SELECT    iteration.ID,

CONVERT(VARCHAR(255),

REPLICATE('|---', Depth_Level) + iteration.Name),

Depth_Level + 1,

CONVERT(VARCHAR(255), RTRIM(Sort) + '|---' + iteration.Name)

FROM Example AS iteration, recursive_source

WHERE recursive_source.ID = iteration.ParentID

)

SELECT Name 'Hierarchical Structure'

FROM recursive_source

ORDER BY Sort

Excel macro n00b

December 10th, 2013 No comments

I just got tired and lazy while formating a report in Excel. Instead of manually clean the same rows more than 1100 times, I thought I’d better use my time creating a macro to automate the process…

All I wanted to do was to cut and paste a cell, and delete 3 rows, then repeat:

Move the cell to the right of the name (cut and paste it 2 rows above and one column right):

excel Macro 1

Delete empty rows (rows 3, 4 and 5 in the example above), and repeat this 1100 times:

excel Macro 2

So this is my extremely basic macro for doing this:

1
2
3
4
5
6
7
8
9
10
11
Sub Move_action()
' Move_action Macro
' Keyboard Shortcut: Ctrl+Shift+A
Do While InStr(ActiveCell.Value, "Action") > 0
ActiveCell.Cut Destination:=ActiveCell.Offset(-2, 1)
ActiveSheet.Range(ActiveCell.Offset(-1, 0).EntireRow, ActiveCell.Offset(1, 0).EntireRow).Delete
ActiveCell.Offset(1, 0).Select

Loop

End Sub

I then just selected the first “Action” cell, and ran the macro, watching Excel (slowly) do the work for me while I was eating my lunch :)

Categories: Tech tips Tags:

Creating cascading attributes with Open Text Content Server

January 23rd, 2012 No comments

“Categories and Attributes” in Open Text Content server is a nice way to add customized metadata to your content stored in the system.

If you want to have “cascading” attributes, for instance “Country >> City”, where you will only display the list of cities within the selected country, you need to use some look-up tables and a bit of SQL.

Here is an example of how you can implement this, with one pitfall and how you can avoid it.

We will use the following sample data:

Countries:

Country_CodeCountry_ENCountry_FR
CNChinaChine
DEGermanyAllemagne
BEBelgiumBelgique

Cities:

Country_codeCity_CodeCity_ENCity_FR
CNBJBeijingPékin
CNGZGuangzhouCanton
CNSHShanghaiShanghaï
DEFRFrankfurtFrancfort
DEHAHamburgHambourg
DEBEBerlinBerlin
BEBXBrusselBruxelles
BENANamurNamur
BEGNGhentGent

 

Step 1: setting up the look-up tables with the data from above (SQL Server code / Transact-SQL):

  • Create the countries table:
1
2
3
4
5
6
7
8
9
CREATE TABLE CAT_COUNTRY(

[Country_code] [NCHAR](10) NULL,

[Country_EN] [NCHAR](255) NULL,

[Country_FR] [NCHAR](255) NULL

) ON [PRIMARY]
  • Populate the countries table:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
INSERT INTO CAT_COUNTRY(

[Country_code]

,[Country_EN]

,[Country_FR])

VALUES

('CN','China','Chine'),

('DE','Germany','Allemagne'),

('BE','Belgium','Belgique')

GO
  • Create the cities table:
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE CAT_CITY(

[Country_code] [NCHAR](10) NULL,

[City_code] [NCHAR](10) NULL,

[City_EN] [NCHAR](255) NULL,

[City_FR] [NCHAR](255) NULL

) ON [PRIMARY]
  • Populate the cities table:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
INSERT INTO CAT_CITY(

[Country_code]

,[City_code]

,[City_EN]

,[City_FR])

VALUES

('CN','BJ','Beijing','Pékin'),

('CN','GZ','Guangzhou','Canton'),

('CN','SH','Shanghai','Shanghaï'),

('DE','FR','Frankfurt','Francfort'),

('DE','HA','Hamburg','Hambourg'),

('DE','BE','Berlin','Berlin'),

('BE','BX','Brussel','Bruxelles'),

('BE','NA','Namur','Namur'),

('BE','GN','Ghent','Gent')

GO

Step 2: fixing a “Table Key Lookup” attribute type shortcoming:

The SQL code provided in the “Table Key Lookup” can’t be more than 255 characters, and must be very simple.

As we would like to combine and display in our look-up values the English and French description, and in order to keep the select statements simple in the attribute definition, we will then create 2 views from our previous tables as follow:

Create Country_View:

1
2
3
4
5
6
7
SELECT DISTINCT

RTRIM(country_EN) + ' - ' + RTRIM(country_FR) AS Country_desc

FROM CAT_COUNTRY

ORDER BY Country_desc

Create City_View:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT DISTINCT

RTRIM(dbo.CAT_Country.Country_EN) + ' - ' + RTRIM(dbo.CAT_Country.Country_FR) AS Country_Desc,

RTRIM(dbo.CAT_City.City_EN) + ' - ' + RTRIM(dbo.CAT_City.City_FR) AS City_Desc,

FROM CAT_CITY

INNER JOIN CAT_COUNTRY

ON dbo.CAT_CITY.Country_Code = dbo.CAT_Country.Country_Code

ORDER BY Country_Desc, City_Desc

Step 3: Creating the Country and City category:

In the Content Server Categories volume, create a new Category named “Country and City” from the “Add Item” menu:

Click on the newly created category name to edit it.

In the “Add Attribute” drop down menu, select “Text: Table Key Lookup” to create the “Country” attribute:

Name it “Country” and use the following settings:

The “Custom SQL statement” box should be checked in order to edit the “Values” box where we will add the SQL statement to query the list of available countries in our look-up table:

Use the following simple select statement to retrieve the list of available countries from the look-up table:

1
 SELECT Country_desc FROM Country_view

Create the “City” attribute in the same way (except that this one may not be required, and display it after “Country”), and with the following SQL select statement to retrieve the list of cities based on the selected country:

1
SELECT City_Desc FROM City_view WHERE Country_Desc = <Country>

Then, test your newly created category by selecting first a country, and then display the available cities you can select:

Categories: ECM, Tech tips Tags:

List the content of a folder in Mac OS X

April 14th, 2011 No comments

The little utility I used to “print” the content of a folder or volume into a text file (for instance to save the list of all movies in a folder) didn’t work in Snow Leopard (Mac OS X 10.6) anymore, so I looked for an other way of doing it.

While not as complete as the utility (PrintFinder, no longer available or supported) this one was self made and works :-)

Instead of writing how it works, a quick video of creating the service is easier:

Creating a “Print to file” folder service with Automator

Categories: Tech tips Tags:

Internet bandwidth cap…

September 13th, 2010 3 comments

I just made the following quick math:

I have a 95 GB bandwidth monthly cap with my current ISP (Rogers) Internet contract (which was added over the time, along with removing other services and increasing the price…).

95 GB is a lot you might say. This cap includes both uploads and downloads.

Here are 2 things I think makes it not so big:

  • If I was to use my current “10 Mbps” connection at 100% for downloading only, I would reach this limit in just over 24 hours. That’s less than 1 hour of 100% usage per day in a month. For just downloading.
  • As I ditched the cable TV, and don’t trust Blu-Ray DRM so I won’t buy a Blu-ray reader soon, I like to get content for my HD TV from either DVD or from the Internet. High Definition movies are about 1 GB per hour. Downloading only 4 movies, 2 hours each would already use 10% of my bandwidth. Add a weekly TV series (4 episodes) for another 8Gb… and you get the picture on how we can soon reach the cap in less than a month, all perfectly legally.

This is surely something Rogers would like to prevent me from doing as they would rather have me pay for their cable or “pay-per-view” offering instead of using internet, which of course I’m not going to do

All that to say that my ISP (Rogers) is selling me a so called monthly service that I need to carefully manage if I want to be able to see the content I chose, and use it during the entire course of a month.

This is not even considering the fact that the ISP wouldn’t be able to provide this service if all their clients in a given area would start using their Internet bandwidth at 100% capacity…

Here is for instance our family usage history for the past 6 months… and I should add here that we almost never turn torrents on :-)

This is something we can manage now, but I just wait for two things before I switch to another provider:

  • Competition readiness (for instance, Teksavvy already provides DSL in my area, but is expecting to also provide cable in a near future)
  • Receiving my first bill from Rogers with charges for additional bandwidth…

After writing this rant, I checked the current plans on Rogers, and my plan has changed to 15 Mbps download speed and 80 GB cap… so make the 24 hours 21 now… It seems that my cap to 95 GB was grandfathered, but I don’t see the speed bump either…

Categories: Humor, Tech tips Tags: