Possibility to read data into FlexSim from Excel data model?

[axel_kohonen]

[ FlexSim 18.2.2 ]

Hi,

Is it possible to import data to FlexSim from the Excel data model or do you have any plans on making it possible?

This would be nice as the Excel data model supports data with more than one million files, which Excel sheets do not, the data is better compressed, and one can user PowerQuery and PowerPivot to easily edit the data.

Thank you!

Kind regards,

Axel

[JordanLJohnson · Accepted solution]

My off-the-cuff reaction is that we probably won't support importing Excel Data Models. It sounds like we would import lots of data, but the model would really only need or use a small fraction of it. But that's just my first impression. Can you describe your use case for this feature a little more?

[axel_kohonen]

Hi @jordan.johnson

My case is that PowerQuery is an easy way to manipulate large amounts of data in Excel even for ones that are not familiar with queries and SQL as such. Thus I could take the raw data from whatever source there is (text files, databases, and such) and import it into the Excel data model. With PowrQuery myself or someone else, maybe who does not know VBA, can easily modify the raw data into the form that I want to use as input for the simulation model by dropping some columns, filtering unnecessary rows, possibly combining it with data from another data source etc. Then I would want to get this merged and appended table into FlexSim.

If the data is less than one million rows I can load the modified data, i.e. the result of the Power Query query, from the data model onto a normal Excel sheet and import that sheet into FlexSim or save it as text or csv and then import it to FlexSim, but it requires one more "unnecessary" step in the process and then the data is both in the data model and in an Excel sheet which increases the file size. If FlexSim could read the result of selected queries (to not get too much data) directly from the Excel data model I would not need this step. It might be a matter of taste if this is required or if it is better to have an intermediate file with the modified data.

If the data is more than one million rows then copying the data to a normal Excel worksheet does not work. One can get the data from the data model using DAX studio which can save it into a text/csv file and then it can be imported into FlexSim or a database, but this is an extra step still which requires a separate program. I might be able to figure out how to export to a database from Excel, but after googling for a day or two it does not seem that Microsoft planned for someone to export anything from the data model.

For more than one million rows of data it would obviously be best to get the data to a database from which I would query it with FlexSim. I could edit the raw data in the database also which would make sense, but editing data in the database is much harder than the easy to use interface of PowerQuery. Especially if someone not familiar with SQL/DAX has to use it.

For both use cases I think it would be the smoothest for me if I could connect to the Excel data model as if it were a database i.e.using the database connector. Then I could either get whole tables or query for subsets of data without having to get it all into FlexSim. Not sure if this approach would be possible to implement though.

Any thoughts?

Kind regards,
Axel

[JordanLJohnson]

I see your point; it's easy to get the table you want with by using a data model, but then it is hard, or at least annoying, to get that table in to FlexSim, especially if that table is long. You would like some kind of query-like interface, that allows you to only import the data you want, but do it directly from Excel, without an "export" step.

If that is the case, I will put an issue on the dev list. That way, we can consider doing something in the future, when we revisit how we connect to Excel.