Accepted Solutions (1)
Hi Daulet
Here are some ideas on tracking down what is missing.
First, determine the consolidation table that holds the data. In my example below, it will be ct_co0033
Using "sp_help ct_co0033" you can determine the columns in the table
Using this, I build the following SQL (I do it this way, as using "where not exists" can be very slow)
SELECT
a.period,
b.name 'entity',
c.name 'orig entity',
d.name 'account',
e.name 'flow',
f.name 'audit',
g.name 'partner',
h.name 'share',
i.name 'currnecy',
j.name 'technical origin',
k.name 'global origin',
l.name 'journal',
a.enumber 'JE nr.',
a.amount,
a.convamount,
a.consamount
FROM ct_co0033 a
left outer join ct_entity b ON a.entity = b.id
left outer join ct_entity c on a.entorig = c.id
left outer join ct_account d on a.accnt = d.id
left outer join ct_flow e on a.flow = e.id
left outer join ct_nature f on a.nature = f.id
left outer join ct_entity g on a.partner = g.id
left outer join ct_entity h on a.ctshare = h.id
left outer join ct_curncy i on a.curncy = i.id
left outer join ct_techorig j on a.techorig = j.id
left outer join ct_site k on a.globorig = k.id
left outer join ct_journal l on a.journal = l.id
NOTE: your table will have additional columns (the custom dimensions you have added). You can simply add these to the SQL
If I run this, it will look like this - each identifier has been replaced by the name of its corresponding table
I verify that the nr of rows is correct
Once we have the data, what are we looking for?
1) NULL values in mandatory dimensions
This is always a sign of a removed dimensions
For example, if I delete account TP110
This will lead to a NULL value in the 'account' column of my table
Note: by using "select * from ct_co0033" you will find the ID (in this case 3)
2) NULL values in other columns
Null values in other columns are not necessarily an issue. A null in the 'enumber' simply means that data did not come from a journal.
However, a NULL in 'journal' where the 'enumber' is not NULL is an issue (as you have a journal entry where the ledger has been removed)
For "dimensional analysis" dimensions, you need to cross-reference the ID
Take for example this record - "partner" and "share" are dimensional analysis, so not every amount is broken down by these dimensions. A NULL can therefore be expected
If you look at the ID's of this record, they are both 0, so in this case the NULL is normal
If the ID had been any other value than 0, you would have an issue
** Resolving the problem **
If you have found a missing ID, you need to get it back into the table
If you are in a 'child' site, you should be able to use the send/receive mechanism
If the member was created in the site, create the member in the Dimension Builder
...and update its ID directly with SQL
Hope this helps you a little.
Marc
To make this a little easier, I have adjusted the SQLs
1) To determine the consolidation table
Use this SQL:
select p.id, a.name as 'category',
ltrim(str(1900+(p.updper&536608768)/262144)) + '.' +
right('00'+ltrim(str((p.updper&253952)/8192)),2) as 'period',
b.name as 'scope',
c.name as 'variant',
d.name as 'currency'
from ct_coref p, ct_phase a, ct_scope_code b, ct_variant c, ct_curncy d
where a.id=p.phase and
p.scope = b.id and
p.variant=c.id and
p.curncy = d.id
This will visualize the ct_coref table. Based on this, check the consolidation definition (CA, DP, SC, VA, CC) and see what ID is linked. In my example, ID=33, so the table used is ct_co0033
2) Check the consolidation table
Use this SQL:
SELECT
a.period,
ltrim(str(1900+(a.period&536608768)/262144)) + '.' +
right('00'+ltrim(str((a.period&253952)/8192)),2) as 'period',
a.entity,
b.name 'entity',
a.entorig,
c.name 'orig entity',
a.accnt,
d.name 'account',
a.flow,
e.name 'flow',
a.nature,
f.name 'audit',
a.partner,
g.name 'partner',
a.ctshare,
h.name 'share',
a.curncy,
i.name 'currnecy',
a.techorig,
j.name 'technical origin',
a.globorig,
k.name 'global origin',
a.journal,
l.name 'journal',
a.pmu,
m.name,
a.mu,
n.name,
a.enumber 'JE nr.',
a.amount,
a.convamount,
a.consamount
FROM ct_co0033 a
left outer join ct_entity b ON a.entity = b.id
left outer join ct_entity c on a.entorig = c.id
left outer join ct_account d on a.accnt = d.id
left outer join ct_flow e on a.flow = e.id
left outer join ct_nature f on a.nature = f.id
left outer join ct_entity g on a.partner = g.id
left outer join ct_entity h on a.ctshare = h.id
left outer join ct_curncy i on a.curncy = i.id
left outer join ct_techorig j on a.techorig = j.id
left outer join ct_site k on a.globorig = k.id
left outer join ct_journal l on a.journal = l.id
left outer join ct_mu m on a.pmu = m.id
left outer join ct_mu n on a.mu = n.id
This will now show the ID + "description" of the corresponding table.
An error in each pair would be:
- a non 0 value in 1st column
- a NULL in the 2nd column
For example:
Note: the columns 'enumber', 'amount', 'consamount' and "convamount' cannot lead to the 'invalid pointer' problem. I've left them in the SQL, for clarity
Marc
Daulet
Ignore that last thread - you are obviously exporting consolidated data, not package
Can you double-check on this ct_0000_DS
As an example, I used ct_0000_an on my table.
select COUNT(*) from ct_co0033 where ct_0000_an <> 0
select ct_co0033.ct_0000_an, ct_0000_analysis.name
from ct_co0033, ct_0000_analysis
where ct_co0033.ct_0000_an = ct_0000_analysis.id
The count() of the first SQL should match the number of returned record on the 2nd SQL
If this all ok, then the data in the table does not seem to be at cause
Did you check FIM, DS, tomcat logs?
Also, check the FC logs (WS and ctserver)
Marc
Hi Marc,
I tried to run both your queries, but there is count of rows is null(empty):
After I exclude DS(DISCLOSURE) from mappin table - everything is OK.
Daulet,
Sorry, I was not very clear in the previous update. That was simply an example, you need to adjust the SQL to your specific situation.
First, determine the reference table used by the dimension (for you this will be DT)
In my example, the dimension code is "AN" and table is ANALYSIS
This leads to column name ct_0000_an and table ct_0000_analysis (which I used in my example)
Then, write the SQL ( it will be something like this for you)
select COUNT(*) from ct_co0140 where CT_0100_DS <> 0
select ct_co0140.CT_0100_DS , ct_0000_DS.name
from ct_co0140, ct_0000_DS
where ct_co0140.CT_0100_DS = ct_0000_DS.id
NOTE: in your previous example you were using ct_co0140 but the last time you use ct_co0173
Do you know with certainty which table you are using in the FIM mapping?
Marc
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.