Utilizing Database Prefixes

A handy little feature available in the MicroStrategy Warehouse Catalog is the ability to add a prefix to a table.  This can be done either manually or automatically and gives you a little bit of flexibility in your database schema as well as opening a few tricks for you.  While it’s not necessary in every situation, it does come in handy in a pinch and has saved me a lot of trouble in several cases.  Today I’ll walk through this simple feature and show how you can take advantage of it yourself.

A database prefix on a table is optional in most databases I’ve worked with. Usually, it’s only required if the table owner is different from your login (Oracle) or if your login is not an administrator (SQL) or sometimes it doesn’t matter at all (Netezza).

Manually Setting the Table Prefix
The easiest way to set a table prefix on all or none of your tables is to open Warehouse Catalog for your project, right click on a table or tables (you can multiselect) and chose Table Prefix.  There may or may not already be options in the list, but you can freely add them if you’d like.  You’ll now see the Prefix column populated on the right hand side of the Warehouse Catalog, and any reports hitting those tables will now prefix them with your string.

Automatically Setting the Table Prefix
While you must perform the Manual Settings for existing tables, you can configure MicroStrategy to set them automatically on future tables.  This can be done by clicking the Options button inside the Warehouse Catalog and going to the View tab and choosing Table Prefixes.  This screen affords you the option of Automatically setting the prefix as creating additional prefixes.  Now any tables imported through this DSN will have the prefix automatically appended.

Supporting Multiple Databases
Before Multisource, this was how you did Multisource.  It’s still a poor-man’s “free” version of Multisource depending on your Database platform.  In the case of SQL server, you can access separate databases on the same server by appending “Database.TableOwner.” to the front of the table.  In this way, you can support reports hitting tables across multiple databases, albeit on the same server.

In the same vein, you can use SQL Server’s Linked Server option to hit different databases on separate servers with the syntax “Server.Database.TableOwner.”.  This requires some additional setup by your Database Administrator and comes with it’s own pro’s and con’s with performance.  However, in a pinch, this can be useful.

If you choose to use a schema that spans databases in this fashion for more than a table or two, you can also modify the Warehouse Catalog to automatically read tables from multiple databases by default.

In the Warehouse Catalog, click on Options and then choose Read Settings.  There is then a Settings button that you can click to modify the SQL MicroStrategy generates to retrieve the list of available tables.  Depending on your database platform, this button my not be available to modify.  If it is, you can copy the SQL it’s using to get the list of tables and try it in your DB Query Tool to see how it works.  In SQL server, simply append the database prefix to the table references, and then copy and paste the same query with a UNION using the next database.  Repeat these steps for each database you want to support.  Do the same for the 2nd query in the Read Settings window, the Column query, and when you return to Warehouse Catalog and refresh, you’ll now see all tables from your multiple databases.  This trick also works with linked servers using the same technique.

Example, SQL Server 2005

Original Query:

SELECT DISTINCT S.name NAME_SPACE, T.name TAB_NAME
FROM sys.all_objects T
JOIN sys.schemas S on T.schema_id = S.schema_id
WHERE T.type IN ('U', 'V') AND S.name IN ('dbo', N'#Login_Name#')

Updated Query:

SELECT DISTINCT S.name NAME_SPACE, T.name TAB_NAME
FROM MyDB1.sys.all_objects T
JOIN MyDB1.sys.schemas S on T.schema_id = S.schema_id
WHERE T.type IN ('U', 'V') AND S.name IN ('dbo', N'#Login_Name#')
UNION
SELECT DISTINCT S.name NAME_SPACE, T.name TAB_NAME
FROM MyDB2.sys.all_objects T
JOIN MyDB2.sys.schemas S on T.schema_id = S.schema_id
WHERE T.type IN ('U', 'V') AND S.name IN ('dbo', N'#Login_Name#')

While this isn’t the best way to do multisource, it’s a quick option in a pinch.  It can also save you the trouble of having to get a DBA to change table schemas if they’re different for some reason, or for setting up a POC project before proper ETL/modeling has occurred.

You may also like...