Hello,
Recently
I have come across one interesting issue related to Microstrategy Enterprise
Manager (Release prior to 10.x), I just thought
it will be useful posting so that if there
any such issue is in your environment you
can take benefit of itJ, I also heard of same
issue from my couple of friends , so
here goes the post!!
Issue
Description: There are no data is showing up in the EM
reporting for the date later than 31st Dec 2015 , while EM fact table
is populating the data from the statistics table (as shown in example below)
EM
Report shows no data later than 31st Dec 2015.
While fact table do have data for year 2016.
What has caused the issue:
In MicroStrategy 9.x, the Enterprise Manager Warehouse
database contains data only until year 2015 (December 31, 2015) for the
following Date Dimension tables:
DT_DAY
DT_QUARTER
DT_QUARTER_YTD
DT_MONTH
DT_MONTH_YTD
DT_YEAR
Also, the partitions
on all the Statistics tables and Enterprise Manager Warehouse tables only exist
till the end of year 2015.
As shown in the below example
When the first part of query is executed, we can see the
data getting recorded up-to-date (as shown in snapshot #2 above),
with
gopa1 as
(select
(((EXTRACT(YEAR FROM a11.DAY_ID2) * 10000) + (MONTH(a11.DAY_ID2) * 100)) +
EXTRACT(DAY FROM a11.DAY_ID2)) "CustomColumn",
a11.IS_PROJ_ID IS_PROJ_ID,
sum(((a11.IS_REP_JOB_ID * 0) + 1)) WJXABC1
from IS_REP_FACT
a11
group
by
(((EXTRACT(YEAR FROM a11.DAY_ID2) * 10000) + (MONTH(a11.DAY_ID2) * 100)) + EXTRACT(DAY
FROM a11.DAY_ID2)),
a11.IS_PROJ_ID
)select distinct pa11.IS_PROJ_ID IS_PROJ_ID,
a14.IS_PROJ_NAME IS_PROJ_NAME,
pa11."CustomColumn" "CustomColumn",
a12.DAY_DATE DAY_DATE,
a13.MONTH_OF_YR_ID MONTH_OF_YR_ID,
a15.MONTH_OF_YR_LDESC MONTH_OF_YR_LDESC,
pa11.WJXABC1 WJXABC1
from
gopa1 pa11
join DT_DAY
a12
on (pa11."CustomColumn"
= a12.DAY_ID)
join DT_MONTH
a13
on (a12.MONTH_ID = a13.MONTH_ID)
join IS_PROJ
a14
on (pa11.IS_PROJ_ID = a14.IS_PROJ_ID)
join DT_MONTH_OF_YR
a15
on (a13.MONTH_OF_YR_ID = a15.MONTH_OF_YR_ID);
However when you run the whole query , no data is returned
despite data exist in EM Fact table and further when you review you will see
there is no entry in the date lookup tables for 2016 or later date so there is
no matching data available hence not returning any rows.
Below is the sample you can see that data in the date dimension table has entry
only till 31st Dec 2015.
How should I fix the issue:
Starting with MicroStrategy Enterprise Manager
10, the Enterprise Manager Warehouse date data has been extended until the year
2020.
If it is required to extend the dates and
partitions on a pre-10 environment, the following scripts can be
used on a MicroStrategy Enterprise Manager Warehouse version 9.x.
Important Note: Customers should make a backup of their Enterprise Manager
Warehouse before running the scripts.
The following script have been attached to the TN270405. Users should select the script based on their Enterprise Manager Warehouse database type and partitioning requirements.
Happy moment: The issue was fixed, once the
Lookup table populated with data for 2016 till 2020.
Note: The script for extending
Teradata partitions can be found in the following MicroStrategy Knowledge
Base Technical Document:
Note: Users who downloaded the Oracle partition extension script
before 1/5/2016 will experience a loss of data for DAY_ID Dec 1st 2015 and
later. To fix this issue, users should roll back to a backup of the Statistics
tables/Enterprise Manager and run the new scripts which have been posted to TN270405.
Happy
Learning!! Hope it is useful!!
Regards,
Arun
Gupta
https://in.linkedin.com/in/arunkrdwbi
