Yumi Nishijima avatar image
Yumi Nishijima asked Matthew Gillespie commented

Import data to PowerBi using sql database

Hi, im having some trouble because im using calculated table to export may databse from SQL server and import my data with PowerBI. I dont understand why i cannot see the results using PBI.

When i open the results from fx and DB Brownser i can see the values but when i open with PBI, the values are null. I've downloaded the model teste and some pictures to show better.fx-test.pngCalculatedTable_Test.fsmpbi-teste.pngdbbrownser-teste.png

FlexSim 22.2.2
sql bug#calculatedtable
fx-test.png (41.4 KiB)
pbi-teste.png (63.4 KiB)
· 3
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Jason Lightfoot avatar image Jason Lightfoot ♦ commented ·

Are you able to change the format of those columns to char, string or varchar?

Update: The fields Linha and MetricName look to be REAL in the stored calculated table in SQlite which seems like it could be an issue.

1 Like 1 ·
Yumi Nishijima avatar image Yumi Nishijima Jason Lightfoot ♦ commented ·

Hi Jason thanks to help me :)
My values from fields Linha and MetricName are varchar.
I tried to change the format of my values on GlobalTable to string but is not working too.

0 Likes 0 ·
ct.png (27.6 KiB)
Jason Lightfoot avatar image Jason Lightfoot ♦ commented ·

Hi @Yumi Nishijima, was Jordan Johnson's answer helpful? If so, please click the "Accept" button at the bottom of their answer. Or if you still have questions, add a comment and we'll continue the conversation.

If we haven't heard back from you within 3 business days we'll auto-accept an answer, but you can always unaccept and comment back to reopen your question.

0 Likes 0 ·

1 Answer

Jordan Johnson avatar image
1 Like"
Jordan Johnson answered Matthew Gillespie commented

This is a bug. As @Jason Lightfoot noted, the Experimenter is flagging the columns as REAL when they should be flagged as TEXT. SQLite allows data to not match the declared type, and so the text data is added to the table correctly. However, PowerBI probably depends on the column type to determine how to show data.

Don't know very much about PowerBI, but I wonder if there is a way to tell PowerBI to treat those columns as text, to sort of override the behavior. But I'm not sure.

Another thing you can try is to fix the column declaration in the file. You can do this with the following code:

/**Custom Code*/
Object current = Model.find("Tools/Experimenter");

Database.Connection db = function_s(current, "getDBConnection");
int shouldDisconnect = 0;
if (!db.isConnected) {
    int connected = db.connect();
    if (!connected) {
        return 0;
    shouldDisconnect = 1;

db.query("PRAGMA foreign_keys=OFF");

// create a new table with the correct scheme
string createStmt = "CREATE TABLE new_table (\
      task_id INT REFERENCES tasks(id) ON DELETE CASCADE\
    , \"Date\" REAL\
    , \"Linha\" TEXT\
    , \"MetricName\" TEXT\
    , \"MetricValue\" REAL\

// Copy all the data from the old to the new
db.query("INSERT INTO new_table SELECT * FROM \"/CalculatedTable1\"");
// Drop the old
db.query("DROP TABLE \"/CalculatedTable1\"");
// Rename the new
db.query("ALTER TABLE new_table RENAME TO \"/CalculatedTable1\"");

db.query("PRAGMA foreign_keys=ON");

if (shouldDisconnect) {

The code essentially creates a new table with the correct column definitions, copies the data from the old table into it, drops the old table, and renames the new table.

You'll have to run this in a script window. I tried putting it in the "On End of Job" trigger, but I think there's a timing issue; there's an exception with trying to drop the CalculatedTable1 table. I suspect it hasn't finished writing that table to the disk yet.

· 3
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Yumi Nishijima avatar image Yumi Nishijima commented ·

Hi Jordan, thankss!!!

Its working now. Although when i run pop an error.

0 Likes 0 ·
1679589618526.png (11.5 KiB)
Jordan Johnson avatar image Jordan Johnson ♦♦ Yumi Nishijima commented ·
That error appears when trying to drop the original table, if it is "in use" somewhere else. Be sure to wait after the Experiment finishes before running the script, as that table might still be being updated. Also be sure that no other programs are trying to use that table.
0 Likes 0 ·
Matthew Gillespie avatar image Matthew Gillespie ♦♦ commented ·

@Yumi Nishijima

This issue is fixed in 23.0.6 released today.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.