New week, new article: welcome back to #BestInMicro! This is a space where I will be writing about different topics related to MicroStrategy, its use, best practices, and functionalities to get the most out of the tool.
Today I bring you an essential article for any administrator. Today I will be talking about MicroStrategy Metadata and some queries that are very useful when administering the tool. Let’s get started! And as every week, first things first…
The Metadata is where all the MicroStrategy definition is stored. It is a database where the whole “skeleton” of the tool is located. And by this, I mean all objects: Attributes, tables, names, users, states, object information, etc.
Going to the basics, below is the basic outline of the MicroStrategy architecture:
The MicroStrategy Metadata stores the information of all the objects we have in our environment. By connecting to Metadata, the Intelligence server is already able to show us the entire structure of our environment. This is why, if you remember my article about ScanMD and its risks, you will now understand a little more why it is so important to handle Metadata with extreme caution.
Many of you may be familiar with tools such as Enterprise Manager or Platform analytics. These are projects that are used to monitor the MicroStrategy environment. If we think together for a minute, they allow us to put together reports that provide information of two types: What objects exist and their statistics… and where do you think you get the information about the objects? You got it right, they are queries against Metadata! So, if we already have these projects to extract information, you may wonder…
Although these are useful tools and make this task much easier, the need to consult the Metadata directly is a relatively frequent scenario. There are times when queries require very exact precision, or even to consult some tables that do not exist in the model of the projects mentioned above.
As we said before, Metadata is a database, and, therefore, it is made up of tables. Although you can consult in detail the Metadata tables, here below I explain the most common ones.
Although here we are talking exclusively about the Metadata tables, in many situations you will want to join with the statistics tables, precisely to know from which objects the statistics are extracted. In these cases, you will notice that the ID of the objects between the different databases are different, and have different formatting. For these cases, for example in SQLServer(depending on the database used will vary), the functions fn_CharToUniqueidentifier or fn_UniqueidentifierToCharMSTR are used. These functions, as the name indicates, “caste” the object IDs by changing their structure to allow us to make the join. Below is the code of the functions. As you can see, it is basically a set of subStrings ordered or separated by dashes.
CREATE FUNCTION dbo.fn_UniqueidentifierToCharMSTR(@charid CHAR(36))
RETURNS CHAR(32)
AS
BEGIN
DECLARE @tpid VARCHAR(32)
SET @tpid = SUBSTRING(@charid, 1, 8)
SET @tpid = @tpid + SUBSTRING(@charid, 15, 4)
SET @tpid = @tpid + SUBSTRING(@charid, 10, 4)
SET @tpid = @tpid + SUBSTRING(@charid, 27, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 25, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 22, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 20, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 35, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 33, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 31, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 29, 2)
RETURN @tpid
END
CREATE FUNCTION dbo.fn_CharToUniqueidentifier(@charid CHAR(32))
RETURNS UNIQUEIDENTIFIER
AS
BEGIN
DECLARE @tpid VARCHAR(36)
DECLARE @uid UNIQUEIDENTIFIER
SET @tpid = SUBSTRING(@charid, 1, 8) + ‘-‘
SET @tpid = @tpid + SUBSTRING(@charid, 13, 4) + ‘-‘
SET @tpid = @tpid + SUBSTRING(@charid, 9, 4) + ‘-‘
SET @tpid = @tpid + SUBSTRING(@charid, 23, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 21, 2) + ‘-‘
SET @tpid = @tpid + SUBSTRING(@charid, 19, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 17, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 31, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 29, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 27, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 25, 2)
SET @uid = CAST(@tpid AS UNIQUEIDENTIFIER)
RETURN @uid
END
¡Y Ahora sí, con estas funciones, podrán hacer queries entre la Bases de estadísticas y la Metadata! Por ejemplo, a continuación, convertimos el Project id de la Metadata en el GUID de la base de estadísticas. Do you dare to do the reverse?
select MD.PROJECT_ID,
EM.IS_PROJ_GUID,
EM.IS_PROJ_NAME
from METADATA.dbo.Dssmdobjinfo MD, Statistics.stats.IS_proj EM
where METADATA.dbo.fn_UniqueidentifierToCharMSTR (MD.PROJECT_ID) = EM.IS_PROJ_GUID;
As MicroStrategy releases progress, the Metadata is updated, and new tables appear or are modified, so if one of the following queries does not work, perhaps it is a matter of changing the name of a field or a table (a good exercise to get familiar with query creation!)
Here is a list of the most useful queries or the ones I use most frequently:
selecto.project_id,
o.OBJECT_TYPE type_code,
caseo.object_type
when 1 then ‘filter’
when 2 then ‘template’
when 3 then ‘report’
when 4 then ‘metric’
when 6 then ‘autostyle’
when 8 then ‘folder’
when 10 then ‘prompt’
when 11 then ‘function’
when 12 then ‘attribute’
when 13 then ‘fact’
when 14 then ‘hierarchy’
when 15 then ‘table’
when 21 then ‘attribute ID’
when 22 then ‘schema’
when 24 then ‘warehouse catalog’
when 25 then ‘warehouse catalog definition’
when 26 then ‘table column’
when 28 then ‘property sets’
when 34 then ‘users/groups’
when 39 then ‘search’
when 42 then ‘package’
when 47 then ‘consolidation’
when 52 then ‘link’
when 53 then ‘table’
when 56 then ‘drill map’
when 58 then ‘security filter’
else ‘Other’
end AS type,
o.object_name,
o.object_id
fromdssmdobjinfo o
order by type
Therefore, if it says Custom E1U0C0D0W0R0 it means that you will be able to Execute, but not the rest. Finally, the ?0BX indicates whether it is inherited permission by default (B0) or manually assigned (B1).
To understand a little more, don’t forget to check out the MicroStrategy security article!
select DISTINCT RIGHTS, CASE RIGHTS
WHEN 1 THEN ” — b00000001
WHEN 5 THEN ‘Custom E0U0C0D0W0R1?1B1’ — b00000011
WHEN 13 THEN ” — b00001101
WHEN 128 THEN ‘Custom E1U0C0D0W0R0?0B0’ — b10000000
WHEN 132 THEN ” — b10000100
WHEN 133 THEN ” — b10000101
WHEN 198 THEN ‘Custom E1U1C0D0W0R1?1B0’ — b11000110
WHEN 199 THEN ‘View’ — b11000111
WHEN 207 THEN ‘Custom E1U1C0D0W1R1?1B1’ — b11001111
WHEN 223 THEN ‘Modify’ — b11011111
WHEN 239 THEN ” — b11101111
WHEN 251 THEN ” — b11111011
WHEN 255 THEN ‘Full Control’ — b11111111
WHEN 268435504 THEN ‘Custom E1U1C0D0W1R1?1B1’ — B010000000000000000000000110000 ?? 0==grant
WHEN 268435505 THEN ‘Custom E1U1C0D0W1R1?1B0’ — B010000000000000000000000110001 ?? 0==default
WHEN 268435512 THEN ‘Custom E1U1C0D0W0R1?1B1’ — B010000000000000000000000111000
WHEN 268435513 THEN ‘Custom E1U1C0D0W0R1?1B0’ — B010000000000000000000000111001
WHEN 268435579 THEN ‘Custom E1U0C0D0W0R1?0B0’ — B010000000000000000000001111011
WHEN 268435583 THEN ‘Custom E1U0C0D0W0R0?0B0’ — B010000000000000000000001111111
WHEN 268435711 THEN ‘Denied All’ — B010000000000000000000011111111
WHEN 536870913 THEN ‘Child Custom E0U0C0D0W0R0?0B1’ — B100000000000000000000000000001
WHEN 536870917 THEN ‘Child Custom E0U0C0D0W0R1?0B1’ — B100000000000000000000000000101
WHEN 536871110 THEN ‘Child Custom E1U1C0D0W0R1?1B0’ — B100000000000000000000011000110
WHEN 536871111 THEN ‘Child View’ — B100000000000000000000011000111
WHEN 536871119 THEN ‘Child Custom E1U1C0D0W1R1?1B1’ — B100000000000000000000011001111
WHEN 536871135 THEN ‘Child Modify’ — B100000000000000000000011011111
WHEN 536871167 THEN ‘Child Full Control’ — B100000000000000000000011111111
WHEN 805306369 THEN ‘Child Custom E1U1C1D1W1R1?1B0’ — B110000000000000000000000000001
WHEN 805306400 THEN ‘Child Custom E1U1C0D1W1R1?1B1’ — B110000000000000000000000100000
WHEN 805306416 THEN ‘Child View’ — B110000000000000000000000110000
WHEN 805306424 THEN ‘Child Custom E1U1C0D0W0R1?1B1’ — B110000000000000000000000111000
WHEN 805306425 THEN ‘Child Custom E1U1C0D0W0R1?1B0’ — B110000000000000000000000111001
WHEN 805306623 THEN ‘Child Denied All’ — B110000000000000000000011111111
WHEN 1610612736 THEN ” — B1100000000000000000000000000000
ELSE ‘Unknown’
END DESCRIPTION
from dssmdobjsecu
WITH Parent AS (SELECT OBJECT_ID, PROJECT_ID AS project_id, PARENT_ID AS Parent_ID, OBJECT_TYPE AS object_type, CAST(OBJECT_NAME AS varchar(MAX)) AS OBJECT_NAME
FROM dbo.DSSMDOBJINFO
WHERE (PARENT_ID = ‘00000000-0000-0000-0000-000000000000’) and object_name =’MicroStrategy Tutorial’
UNION all
SELECT TH.OBJECT_ID, TH.PROJECT_ID, TH.PARENT_ID, TH.OBJECT_TYPE, CONVERT(varchar(MAX), Parent_2.OBJECT_NAME + ‘/’ + TH.OBJECT_NAME) AS Path
FROM dbo.DSSMDOBJINFO AS TH INNER JOIN
Parent AS Parent_2 ON Parent_2.OBJECT_ID = TH.PARENT_ID AND Parent_2.project_id = TH.PROJECT_ID)
SELECT distinct d.OBJECT_NAME AS Project_name, b.OBJECT_NAME, b.OBJECT_TYPE,
CASE WHEN b.object_type = – 1 THEN ‘Unknown’ WHEN b.object_type = 0 THEN ‘Reserved’ WHEN b.object_type = 1 THEN ‘Filter’ WHEN b.object_type = 2 THEN ‘Template’ WHEN b.object_type = 3 THEN ‘ReportDefinition’
WHEN b.object_type = 4 THEN ‘Metric’ WHEN b.object_type = 5 THEN ‘Unused’ WHEN b.object_type = 6 THEN ‘Autostyles’ WHEN b.object_type = 7 THEN ‘AggMetric’ WHEN b.object_type = 8 THEN ‘Folder’ WHEN
b.object_type = 9 THEN ‘Unused’ WHEN b.object_type = 10 THEN ‘Prompt’ WHEN b.object_type = 11 THEN ‘Function’ WHEN b.object_type = 12 THEN ‘Attribute’ WHEN b.object_type = 13 THEN ‘Fact’ WHEN b.object_type
= 14 THEN ‘Dimension’ WHEN b.object_type = 15 THEN ‘Table’ WHEN b.object_type = 16 THEN ‘Unused’ WHEN b.object_type = 17 THEN ‘FactGroup’ WHEN b.object_type = 18 THEN ‘Shortcut’ WHEN b.object_type
= 19 THEN ‘Resolution’ WHEN b.object_type = 20 THEN ‘Unused’ WHEN b.object_type = 21 THEN ‘AttributeForm’ WHEN b.object_type = 22 THEN ‘Schema’ WHEN b.object_type = 23 THEN ‘FindObject’ WHEN b.object_type
= 24 THEN ‘Catalog’ WHEN b.object_type = 25 THEN ‘CatalogDefn’ WHEN b.object_type = 26 THEN ‘Column’ WHEN b.object_type = 27 THEN ‘PropertyGroup’ WHEN b.object_type = 28 THEN ‘PropertySet’ WHEN
b.object_type = 29 THEN ‘DBRole’ WHEN b.object_type = 30 THEN ‘DBLogin’ WHEN b.object_type = 31 THEN ‘DBConnection’ WHEN b.object_type = 32 THEN ‘Project’ WHEN b.object_type = 33 THEN ‘ServerDef’
WHEN b.object_type = 34 THEN ‘User’ WHEN b.object_type = 35 THEN ‘Unused’ WHEN b.object_type = 36 THEN ‘Configuration’ WHEN b.object_type = 37 THEN ‘Request’ WHEN b.object_type = 38 THEN ‘Unused’
WHEN b.object_type = 39 THEN ‘Search’ WHEN b.object_type = 40 THEN ‘SearchFolder’ WHEN b.object_type = 41 THEN ‘Unused’ WHEN b.object_type = 42 THEN ‘FunctionPackageDefinition’ WHEN b.object_type
= 43 THEN ‘Role’ WHEN b.object_type = 44 THEN ‘SecurityRole’ WHEN b.object_type = 45 THEN ‘InBox’ WHEN b.object_type = 46 THEN ‘InBoxMsg’ WHEN b.object_type = 47 THEN ‘Consolidation’ WHEN b.object_type
= 48 THEN ‘ConsolidationElement’ WHEN b.object_type = 49 THEN ‘ScheduleEvent’ WHEN b.object_type = 50 THEN ‘ScheduleObject’ WHEN b.object_type = 51 THEN ‘ScheduleTrigger’ WHEN b.object_type = 52
THEN ‘Link’ WHEN b.object_type = 53 THEN ‘DBTable’ WHEN b.object_type = 54 THEN ‘TableSource’ WHEN b.object_type = 55 THEN ‘DocumentDefinition’ WHEN b.object_type = 56 THEN ‘DrillMap’ WHEN b.object_type
= 57 THEN ‘DBMS’ WHEN b.object_type = 58 THEN ‘MDSecurityFilter’ WHEN b.object_type = 59 THEN ‘PromptAnswer’ WHEN b.object_type = 60 THEN ‘PromptAnswers’ WHEN b.object_type = 61 THEN ‘GraphStyle’
WHEN b.object_type = 62 THEN ‘ChangeJournalSearch’ WHEN b.object_type = 63 THEN ‘Blob’ WHEN b.object_type = 64 THEN ‘DashboardTemplate’ WHEN b.object_type = 65 THEN ‘Flag’ WHEN b.object_type
= 66 THEN ‘ChangeJournal’ WHEN b.object_type = 67 THEN ‘ExternalShortcut’ WHEN b.object_type = 68 THEN ‘ExternalShortcutTarget’ WHEN b.object_type = 69 THEN ‘Reconciliation’ WHEN b.object_type = 70
THEN ‘Layer’ WHEN b.object_type = 71 THEN ‘Palette’ WHEN b.object_type = 72 THEN ‘Thresholds’ WHEN b.object_type = 73 THEN ‘PersonalView’ WHEN b.object_type = 74 THEN ‘FeatureFlag’ ELSE ‘Unknown’
END AS object_type_desc, Parent_1.OBJECT_NAME AS Name_with_path, REPLACE(Parent_1.OBJECT_NAME, b.OBJECT_NAME, ‘’)
AS Path_name, b.HIDDEN, dbo.fn_UniqueIdentifierToCharMSTR(a.OBJECT_ID) as OBJECT_ID
FROM dbo.DSSMDOBJSECU AS a INNER JOIN
dbo.DSSMDOBJINFO AS b ON a.OBJECT_ID = b.OBJECT_ID AND a.PROJECT_ID = b.PROJECT_ID INNER JOIN
dbo.DSSMDOBJINFO AS c ON a.TRUST_ID = c.OBJECT_ID INNER JOIN
dbo.DSSMDOBJINFO AS d ON a.PROJECT_ID = d.OBJECT_ID INNER JOIN
Parent AS Parent_1 ON a.OBJECT_ID = Parent_1.OBJECT_ID AND a.PROJECT_ID = Parent_1.project_id
And just like the cases I have been describing, the possibilities are endless! And of course, it can be a good idea to create views with the queries or to create new objects in our model that allow us to play and navigate with the different queries or those fields that we customize.
Although it is possible to use command manager to obtain information, it is much more customizable to do it with a query.
Now it’s your turn, did you know about these consultations? Are there any you would like to recommend? Have you ever made queries or browsed the Metadata?
So much for this week’s article. As always, I hope you find this helpful, maybe learn something new, and I invite you to leave comments or topics you’d like me to write about!
See you next #BestInMicro!
* Tu información será utilizada exclusivamente para contactarte en relación al Business Data Master. No hacemos spam ni compartimos datos con terceros.
2 Responses
How can I have a list of disable users using query? As far as I can see, I can only access via command manager.
Hi Myaman! Yes, command manager and platform analytics are the best way.
anyway, if you want to make a query, you can try this:
SELECT info.OBJECT_NAME, def.*
FROM DSSMDOBJDEF2 def
INNER JOIN
DSSMDOBJINFO info
ON (def.OBJECT_ID = info.OBJECT_ID)
WHERE object_type = 45 AND subtype = 11520 AND DEFINITION LIKE ‘%900D%’
unfortunately, is likely to not get any result as the DEFINITION is an encripted column specific for each installation. MicroStrategy support can tell you the correct string for your scenario.
i Hope that helped!