Business Intelligence Ping Pong

This morning I had this request from a user.

- I need a list of all materials in our plant with purchase prices. Can you fix that?

- Sure

select matnr, purch_price, currcd for materials

I sent the Excel by mail.

-But I need this for all products with quantity?

- You want a BOM exploded list?

- Yes

- Oki

select

a.product,a.component, a.qty, c.purch_price, c.currcd.

from bom_tree a inner join materials  c

on a.component = c.matnr

order by a.product,a.component

I sent the Excel by mail.

-Hello again, I also need lotsize, can you add that

select

a.product,a.component, a.qty, c.lotsize, c.purch_price, c.currcd.

from bom_tree a inner join materials  c

on a.component = c.matnr

order by a.product,a.component

I sent the Excel by mail.

- This is a nice report, but where is the price in swedish crowns?

- There is no price in swedish crowns.

- I also need the purchase price in swedish crowns using company year currency rate, can you add this?

- Shure

select

a.product,a.component, a.qty, c.lotsize, c.purch_price, c.currcd

,round(coalesce(c.purch_price * d.factor,0),2) as 'PRICE_IN_SEK'

from bom_tree a

inner join materials  c on a.component = c.matnr

left join cur_rate d on d.f_currcd = c.currcd and d.t_currcd = 'SEK' and d.year = '2013' and d.ratetype  = 'ACAB'

order by a.product,a.component

I sent the Excel by mail.

-Hello again, I like the report but I also need unit price. Can you add that?

-Yes

select

a.product,a.component, a.qty, c.lotsize, c.purch_price, c.currcd

,round(coalesce(c.purch_price * d.factor,0),2) as 'PRICE_IN_SEK'

,round(coalesce(c.purch_price * d.factor / c.lotsize,0),2) as 'UNIT_IN_SEK'

from bom_tree a

inner join materials  c on a.component = c.matnr

left join cur_rate d on d.f_currcd = c.currcd and d.t_currcd = 'SEK' and d.year = '2013' and d.ratetype  = 'ACAB'

order by a.product,a.component

I sent the Excel by mail.

- Hello, I really like the report, but I also need the description of the component. Can you add?

-Yes.

select

a.product,a.component, c.descr, a.qty, c.lotsize, c.purch_price, c.currcd

,round(coalesce(c.purch_price * d.factor,0),2) as 'PRICE_IN_SEK'

,round(coalesce(c.purch_price * d.factor / c.lotsize,0),2) as 'UNIT_IN_SEK'

from bom_tree a

inner join materials  c on a.component = c.matnr

left join cur_rate d on d.f_currcd = c.currcd and d.t_currcd = 'SEK' and d.year = '2013' and d.ratetype  = 'ACAB'

order by a.product,a.component

I sent the Excel by mail.

-Hello, now I only need the material overhead for the component?

-???

- If you look into transaction CK11 you find  the overhead in there . Can you add it?

This is what I call BI ping pong , and it is not that bad. I’m playing this match now. We paused for the night, and the SQL in here is not the actual SQL  but a slight transcription for clarity . It is iterative development by interaction and it’s fast. When the user contacted me we were not sure what he wanted, but now at least I have a pretty good idea. And we have not spent many minutes on the development. When I have figured out what the material overhead is, this user will come back and say - ‘I want to compare this report with the figures from last year’. And then we already have a very good, tested and working prototype for the OLAP app the user wanted without anyone of us knowing it from the beginning, (the user actually does not know this yet, but I will suggest a Qlikview app tomorrow).

The conventional (and boring) approach; start a project with lots of eventual users creating demand specifications etc. etc. will take longer time, cost more money and deliver a less satisfactory result.