Diadem Show reduced data in VIEW?

[EWiebe]

Hello,

I have installed the Diadem Evaluation version 15.0

Then I found my data on a SQL Server and loaded it into Data Manager (Datenportal): (see picture)

Now I want to visualize my data in the VIEW tab and have a problem:

This table has data for several PP (PP stands for testing place and has integer values 1..16).

Question: can I show data in the VIEW Tab only where e.g. PP = 3 ?

I have tried to write a script, but it is too slow (there are 6892777 rows in this table).

Adding the row directly to the VIEW is fast, so it should be also possible to add rows where PP = 3 ?

Thank you in advance

BR

EWiebe

[Stephan_D]

Hello Eugen,

it would be helpfull if you attach all available information to your post.

How do the script look like, which you have written to process the data?

How do the data in a channel look like?

How long does the execution of the current script takes?

Which steps have you tried to optimized the script?

Why should the post-processing be done in DIAdem? It should be possible the write an SQL-Query, which returns only the data for pp=3 from your database instead of all data.

Stephan

[EWiebe]

OK, again:

I have a MSSQL Table (see attached screenshot)

It has this current size: more than 8000000 entries and a size of app. 1,4 GB

Then I have heard that DIADEM is able to work with big data.

So I have connected DIADEM over ADO Connection string to my database (after configuring under Windows ODBC) :

(Provider=MSDASQL.1;Password=xxx;Persist Security Info=True;User ID=xxx;Data Source=VSRV_SQL)

It took a LOT of time (15min ?) and at the end told me that there was an error and not all rows could be tranferred,

but the data seems to be complete...

In this table there was collected all data from the whole test stand which contains 16 places.

Identifying over PP-Column:

Test results from Test Place 1 have PP=1

Test results from Test Place 2 have PP=2

... and so on...

OK, I have the whole data from all test places in this table now.

When I go on the VIEW TAB, i can add a series to the view (e.g. WID2_MAX over DT (time) ) (see attached screenshots)

BUT THIS SERIES INCLUDES ALL TEST PLACES (1..16) BUT I WOULD LIKE TO SEE ONLY FROM SPECIAL TEST PLACE (e.g. only from TestPlace 3, means where PP is 3).

So, I have searched for a solution and found that it is possible to use scripts for that:

============== SCRIPT START =========================

'creating new channels: one for the time and one for the measured data

Call Data.Root.ChannelGroups(GroupDefaultGet).Channels.Add("DT_PPX",DataTypeDate)
Call Data.Root.ChannelGroups(GroupDefaultGet).Channels.Add("WID2_MAX_PPX",DataTypeFloat64)

'set whole length first
ChnLength("SCHALTER_DAUER_STD/DT_PPX") = ChnLength("SCHALTER_DAUER_STD/DT")
ChnLength("SCHALTER_DAUER_STD/WID2_MAX_PPX") = ChnLength("SCHALTER_DAUER_STD/DT")

Dim i, j, length, R1, curr_dt, curr_pp
On Error Resume Next
j=1
length=ChnLength("SCHALTER_DAUER_STD/PP")
For i=1 to length  'ChnLength("SCHALTER_DAUER_STD/PP")
  curr_pp = ChD(i, "PP")

  ' copy from PP 5
  if curr_pp = 5 then
    'copy date and time
    ChD(j, "SCHALTER_DAUER_STD/DT_PPX") = ChD(i, "SCHALTER_DAUER_STD/DT")
 
    'copy number
    ChD(j, "SCHALTER_DAUER_STD/WID2_MAX_PPX") = ChD(i, "SCHALTER_DAUER_STD/WID2_MAX")    
    j=j+1
  end if
  i=i+1
Next

'set length
ChnLength("SCHALTER_DAUER_STD/DT_PPX") = j
ChnLength("SCHALTER_DAUER_STD/WID2_MAX_PPX") = j

Call MsgboxDisp("Finished")

============== SCRIPT START =========================

OK, it works, but it needs more than 1 minute for one channel, so I thought perhaps anybody knows if it is possible to view the data by reduce data without scripts.

If I will use script for all channels it would need over 20 minutes and with this method I duplicate data (copying to new channels) which does not make sense, because the data should simply be filtered and not copied. And creating a report would need more than 20 Minutes? I think my boss will not pay for DIADEM if he hears this...

BTW: In SQL it would be very easy: SELECT * FROM SCHALTER_DAUER_STD WHERE PP=3   🙂

I hope it helped.

Thank you in advance.

[Stephan_D]

You should use ChDX instead of ChD to access each value of a channel, because this is much faster:

Variable: ChDX - DIAdem 2011 Help - National Instruments
http://zone.ni.com/reference/en-XX/help/370859J-01/varoff/chdx/

From the linke document:

"Accessing the CHD variable takes much longer than accessing the CHDX variable."

You can also use ADO in DIAdem to get only the relevant channel:

ADO - DIAdem 2011 Help - National Instruments
http://zone.ni.com/reference/en-XX/help/370859J-01/ado/ado/ado_general/

[EWiebe]

I tried with CHDX, but:

* it does not work for DateTime

* and using with the numeric (double) value :

ChDX(j, "SCHALTER_DAUER_STD/WID2_MAX_PPX") = ChDX(i, "SCHALTER_DAUER_STD/WID2_MAX")

it tells me, that it is only possible with integer values:

Fehlermeldung des DIAdem Befehlskerns:
     Unzulässiger Parameter beim Befehl "CHDX(1,>???<)".
     Zulässig sind hier nur Integerzahlen.

I will try to use SQL SELECT from scripts.

Thank you

[EWiebe]

The data from DB can be read with:

Set RecSet = CreateObject("ADODB.Recordset")
RecSet.Open "My_Table", oDBConnection
RecSet.Open "Select * from My_Table "&"where Time>0.8", oDBConnection
If not RecSet.EOF Then
  RecSet.MoveFirst
  Do While not RecSet.EOF
    Msgbox RecSet.Fields(2)
  ' or RecSet.Fields("Speed")
    RecSet.MoveNext
  Loop
End If

How to transfer data from RecordSet to the Data Portal (on the right side of DIADEM) ?

[EWiebe]

This helped me:

Link

[EWiebe]

It helped me but it is still too slow 😞

The best way would be:

1. import all data from SQL Server once

2. VIEW / REPORT data by specifying, which data should be viewed (in my case only data where PP=3 e.g.)

@NI

Please add to --> Diadem Idea Exchange

Thank you

[EWiebe · Accepted solution]

Trying with CHDX works !!

And performace is OK (app. 1 minute for 7000000 entries).

I think this is my solution

Option Explicit  'Erzwingt die explizite Deklaration aller Variablen in einem Script.

Dim i, j, dt, x, length, R1, curr_dt, curr_pp, ch_len, percent, last_percent
Dim intChannelNo_PP, intChannelNo_DT_ORIG, intChannelNo_VAL_ORIG, intChannelNo_DT_NEW, intChannelNo_VAL_NEW

ch_len=ChnLength("SCHALTER_DAUER_STD/DT")

Call ChnAlloc("DT_PPX", ch_len, 1, DataTypeFloat64) 'Allocate channel intLoop
Call ChnAlloc("WID2_MAX_PPX", ch_len, 1, DataTypeFloat64) 'Allocate channel intLoop

intChannelNo_PP = CNo ("/PP") 'Calculate channel number
intChannelNo_DT_ORIG = CNo ("/DT") 'Calculate channel number
intChannelNo_VAL_ORIG = CNo ("/WID2_MAX") 'Calculate channel number
intChannelNo_DT_NEW = CNo ("/DT_PPX") 'Calculate channel number
intChannelNo_VAL_NEW = CNo ("/WID2_MAX_PPX") 'Calculate channel number

On Error Resume Next

j=1
percent=0
last_percent=0
length=ChnLength("SCHALTER_DAUER_STD/PP")
For i=1 to length  'ChnLength("SCHALTER_DAUER_STD/PP")
  curr_pp = ChDX(i, intChannelNo_PP)
  if curr_pp = 5 then
    'get date
    dt=ChDX(i, intChannelNo_DT_ORIG)
    ChDX(j,intChannelNo_DT_NEW) = dt
 
    'get number    
    x=ChDX(i, intChannelNo_VAL_ORIG)
    CHDX(j, intChannelNo_VAL_NEW) = x'Fill channel with data    
    j=j+1
  end if
 
  percent = (i*100\length)
  if percent <> last_percent then
    last_percent = percent
    Call WndShow("SHELL")
    Call LoopInc(percent)
    Call SqlStatusDlg("Copying... " & CStr(i*100\length) & "%")
  end if    
 
  i=i+1
Next

ChnLength(intChannelNo_DT_NEW) = j-1
ChnLength(intChannelNo_VAL_NEW) = j-1

'Call ChnPropSet((intChannelNo_DT_NEW), "length", j) 'Set channel length
'Call ChnPropSet((intChannelNo_VAL_NEW), "length", j) 'Set channel length

Call ChnCharacter(intChannelNo_DT_NEW) 'Update characteristic values
Call ChnCharacter(intChannelNo_VAL_NEW) 'Update characteristic values

Call SqlStatusDlg("Fertig")
Call MsgBoxCancel


Sub SqlStatusDlg(Msg)
  Call MsgBoxSetPos(40, 45, 15, 5)
  Call MsgBoxDisp(Msg, "MB_NOBUTTON" , "MsgTypeNote", 0, 0, 1)
End Sub ' SqlStatusDlg()