Mapping/ Join SQL and CSV Data

Ellina Mnikh

Hi all,

I'm quite new in Board and would be happy to get an advice to my topic. I use ASCII Data Reader to get the inventory data from a csv. To get some additional information like material description I add a database table Material via SQL protocol. Both of them have a common column calling Material code. 
I would like to show the inventory data and material description in one dataview. So I'm wondering, if here would be a way to join the data?

Some of my settings:
Data Reader:
ASCII: Inventory
Day   Owner   MaterialCode   Material  Quantity

SQL: Material Data
MaterialCode   MaterialGroup

Relationships: 
MaterialGroup - MaterialCode - Material

Cube:
Day   MaterialCode   Owner

Reportidea:

Thank you in advance,

Ellina

------------------------------
Ellina Mnikh
Controller
------------------------------

Brendan Broughton

Hi Ellina,
If you've loaded your entities and cube correctly you don't really need to 'join' them.  The SQL will load the entity and description, the ASCII will populate the cube.

When you display your cube in the Dataview you put an entity (or multiple) by row, and it will then give a table showing the inventory values by row. You can then control (either via the entity setting, or axis setting in Dataview layout) if it shows the entity Code or Description for the entity. You can also 'flatten groups' so that rather than collapsible groups it will show a column for each of the entities 'by row'.

I'm not sure if this is where you're stuck, but if it's not then perhaps look at your entities/relationships.  In BOARD Entities have 2 components, a 'code' and 'description'.  From my interpretation of your message/relationships it looks as though you have loaded 'Material Code' and 'Material' as two separate entities... if 'Material' is the description of 'Material Code' then I would look to have a single entity called 'Material' perhaps, and load 'Material Code' as the code component and 'Material' as the description. This would be using the SQL reader to load the codes and group (with Append), and then the ASCII reader to load the description against the code).

------------------------------
Brendan
bbroughton@bi5.com.au
Australia
------------------------------
-------------------------------------------

Ellina Mnikh

Hi Brendan, 

thank you for your reply! I saved the 'Material Code' and 'Material' as one entity and fixed the relationships. Now it works

Unfortunatally I have another problem now. We receive the inventory data as a csv file. It contains daily data for the last two month. My Idea was to upload the file daily. The new combination of 'Material' and 'Day' would be added and for the existing ones the inventory data would be updated. 

Nowever  every time I upload the file 'Stocks' for the existing combination of 'day' and 'material' is duplicated.

Could you tell me what I'm doing wrong?

Thanks!

Ellina




------------------------------
Ellina Mnikh
Controller
------------------------------
-------------------------------------------

Brendan Broughton

Hi @Ellina Mnikh great to know that got you moving forward.

I think the reason it's 'duplicating' is because you haven't specified an action for the cube.  The default (in the windows client that you are using) is to add the values together.  If you ran the datareader twice the second time it will add it to the value already stored in the cube.  If you ran it a third time it will add it again and you will end up 3x what you want.

If you're going to be reading the same time periods multiple times (as you will be if you load the last two months daily) and want it to overwrite/replace then select 'replace' like in the Datareader below..

The other option is to control the selection on 'day' when the datareader is called, and only load the latest day.  But personally if it doesn't take long I'd just read all the days, but with 'replace' on the cube.
Just a couple of things to note - it will clear the cube for EVERY 'Day' that it finds in that csv file.  So you will need to make sure ALL your materials/data is in the one csv.  If you have data in multiple files then let me know and I'll let you know how we get around it.

The other thing, I was told years ago to read the entities separately to the cubes, so I still do, it allows you to control the entities separately to the data.

If it was me, I would separate the adding of entities and the loading of cubes into 2 separate datareaders, and I never 'append' entities in the same datareader that loads a cube.  The main reason for this is that loading the cube will only have/need one dimension of what could be a large tree. So if that datareader adds a new entity member it won't also populate the full tree (ie. all the related entities), that would be done in the 'Tree' or 'Master List' datareader which populates/add entity members.

Cheers

------------------------------
Brendan
bbroughton@bi5.com.au
Australia
------------------------------
-------------------------------------------

Ellina Mnikh

Hi Brendan,

that's the problem I can't understand. I choosed already the "Replace" Action for the Cube in ASCII data reader. But I still get duplicates.

Do you have any idea what I'm doing wrong?

Thanks!




------------------------------
Ellina Mnikh
Controller
------------------------------
-------------------------------------------

Brendan Broughton

Hi Ellina,
Just to confirm, you do mean duplicating the numbers/values in the cube correct?
What do you get if you clear the cube, then run the datareader once?
Can you put up a snip of the csv file, a snip of what you are getting as your result in the DView, and a snip of the cube structure?

------------------------------
Brendan
bbroughton@bi5.com.au
Australia
------------------------------
-------------------------------------------

Ellina Mnikh

Hi Brendan,

exactly, the values are duplicated after each run of the data reader.
I cleared the cube and run it again. 
Here are some snips of my settings and the results I get:

Entities:

Relationships:

Cube:

Data Reader SQL (Entities):

Data Reader ASCII (Cube):

Results after first run:

Results after second run:

Results after third run:

Thank you,

Lina

------------------------------
Ellina Mnikh
Controller
------------------------------
-------------------------------------------

Brendan Broughton

Interesting.  It all looks ok from what I can see.  First question, are the numbers you see after the first run correct/what is in the source file? What do you see if you drill down to DAY after each... is it doubling/tripling against the same day, or are they reading them against different days?

I have had issues in BOARD where a datareader seems to 'break', and no matter what you do it does unexpected things/doesn't work.  To get around it I've just built a brand new Datareader - so perhaps give that a go and see if it helps.  (Sometimes I need to read into a brand new cube too..no idea why)

------------------------------
Brendan
bbroughton@bi5.com.au
Australia
------------------------------
-------------------------------------------

Ellina Mnikh

Hi Brendan,

is it possible to use something similar to WHERE-clause in ASCII? E.g in SQL I would use in my case <span class="pln">Date </span><span class="pun">></span><span class="pln"> DATE_ADD</span><span class="pun">(</span><span class="pln">NOW</span><span class="pun">(),</span><span class="pln"> INTERVAL </span><span class="lit">-1</span><span class="pln"> DAY</span><span class="pun">)<br /></span>
If I understand correctly, on this way I could awoid the duplicates in my cube.

To your questions: The numbers after the first run are correct compared to the source file. If I drill down to day after each run, it doubling/tripling against the same day. I tried to built a new Data reader with the same settings and got the same result.

Lina

------------------------------
Ellina Mnikh
Controller
------------------------------
-------------------------------------------

Ellina Mnikh

Hi Brendan,

I got it. The problem was in the datatype of the date column. I converted it to YYYYMMDD and now it works.
Thank you for your help!

Lina

------------------------------
Ellina Mnikh
Controller
------------------------------
-------------------------------------------

Brendan Broughton

Great! I did notice the different format in your DR.  I've only ever got it to work with the YYYYMMDD format so I assumed there was an ETL to convert otherwise I would have expected it to fail... or possibly read incorrectly which I guess is what was happening.  I hope something I said helped

------------------------------
Brendan
bbroughton@bi5.com.au
Australia
------------------------------
-------------------------------------------