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...

14 Responses

  1. SKP says:

    Hey Bryan,

    I really appreciate you explanation with the screenshots, and i would like have more information about the pro’s and cons of using multi-sourcing. Did you already have any postings in your blog..if yes, can you please send me the location…my email address is skp.mstr@gmail.com

    Thanks
    SKP

  2. Bryan says:

    I think Multisource can creatively solve specific problems, but I’m worried that overall it will only contribute to poor implementations and serve as a shortcut for true ETL/Data Warehousing.

    For more on the subject, you can check out TN30336 – Recommendations for physical modeling of attribute data in projects using the MultiSource option in MicroStrategy SQL Generation Engine 9.x

  3. James says:

    Thanks Bryan, I followed your instructions and they worked

  4. raghu says:

    Hi Bryan,
    I am facing the below issue with the report SQL.
    The SQL of the report shows database name as prefix for some of the tables and for rest its showing only table names . Any idea why its happening and how to get the SQL with databasename.tablename format? . Backend is Teradata . Please let me know . Thanks in advance.

    Example:
    Select col1,col2,col3
    from
    dimension_table (No database prefix)
    join database1.fact_table

    • Bryan says:

      Go to Warehouse Catalog (under the Schema menu), and check to make sure that the table has the appropriate prefix defined. If not, you can right click on the table object and set it.

  5. ashish says:

    Hi Bryan,

    I am facing an issue with table prefix. Some time back my MicroStrategy project was configured with database credentials thru which certain schema (say S1) was accessible, so few tables were imported and reports were built on top of them. Now the database access credential got changed again to access different schema (say S2) with exactly same table as the older schema (S1) had and it can no longer access old schema (S1).

    Problem is that the report SQLs are still using the old table prefix (S1) which now is no more accessible and throwing error. When I tried to change table prefix in warehouse catalogue it did not let me change it.

    I had a belief that database schema name does not gets stored in the report metedata and source of data for a report can be changed by changing schema name (table prefix) as far as table structure remains same. But I am confused now.

    What should I do?

    Thanks in advance.
    Ashish

    • Bryan says:

      You should be able to go into the warehouse catalog and modify the table prefix there. I think you can only do it on 1 table at a time, but if you have a lot of tables to update, you can probably do this using Command Manager. If that’s not working, you could also try using the Architect tool to make the change.

  6. mayooran87 says:

    Hi Bryan,

    I have a question related to the DB schema (table owner) in Oracle. If you want MSTR to create it’s temp tables in a separate DB schema, how would you do it? So accessing the data from Schema A, but creating temp tables etc. in Schema B?

    Thanks in advance

    • Bryan says:

      I’ve never tried it, but what if you make the default database for the login your tempdb and make sure all of your warehouse tables are prefixed with your whdb? Otherwise, I’d imagine that would be an oracle setting, wouldn’t it? Unless you have it creating physical tables and then dropping them, in which case it may not be something you can change, because they may keep them close for performance.

  7. Snehal says:

    I have SQL server dbs. This database hold multiple schemas. I have 3 different projects that has been created.
    Each of them either share same schema tables and more than one schema tables. When i updated schema query to pull of them, I see same tables coming out twice and it gives me an error. How can I fix this?

  8. Ramya says:

    Hi Bryan,
    This post was very useful. Is it possible to change the default query using command manager?

  9. shruthi says:

    Hi Bryan,

    I have similar kind of issue. have to join two different columns of two different tables from two different Database instances.
    Example:

    reservation.date >= booking.dimcreatedt

    How to achieve the joins. I tried doing it in filters using ApplyComparison and ApplySimple, but its limited to single table.

  10. Naveena says:

    Hey Bryan, Can you help me with more details on linked server? Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *