Some time ago I published an entry in relation to the creation of Datamart Report in Microstrategy, which you can find at this link:
Now I will devote a few lines to speak further about configuration and characteristics of this functionality.
MicroStrategy provide us the possibility to store statistics about our projects in physical tables. This information is varied and includes access dates, fault recording, runtimes … If we want to see the definition and composition of statistical tables which Microstrategy gathers, we can consult in:
Microstrategy 9.4 AdminSupplemental
since 463 page, there, we can see the information contained in each table.
In this post, I will combine information from the statistics repository with the information which we have in MicroStrategy metadata, for this reason it is advisable to take a look at the previous post:
Microstrategy Metadata Queries (I)
Microstrategy provides us a good utility which we usually not use, insert a custom report DataMart as another table in our model. This is very simple, we need to go the the next menu:
I share a simple query to find duplicates in all columns that compounds our tables in the project
SELECT '/* '|| AB.TABLE_NAME || ' */ SELECT ' || AB.COLUMN_NAME ||', COUNT(1) AS DUPLICADOS FROM ' || AB.TABLE_NAME || ' GROUP BY '|| AB.COLUMN_NAME ||' HAVING COUNT(1)>1;' FROM(SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE FROM ALL_TAB_COLUMNS WHERE TABLE_NAME LIKE '%' -- CONDITIONORDER BY 1, 2) ABGROUP BY AB.TABLE_NAME, AB.COLUMN_NAMEORDER BY AB.TABLE_NAME, AB.COLUMN_NAME;