General Data Lake / File location Connector

Hi @daniel,

in part this is already possible using the CData connectors which have AggregateFiles options. Excel is one of these. You can load a whole folder of Excel files into parquet through the ODX. I believe the CSV and XML connectors also make this possible though the XML one uses wildcards in the URI to achieve this.

Note that this kind of functionality is close to data integration in some aspects so there is a risk of holding a hammer and seeing nails everywhere. With the OneLake support there may be ways to use shortcuts etc. to achieve similar results for end users without actually applying I/O depending on whether the data needs to be loaded through DWH and SSL instances.

Hi @rory.smith ,

I know of this funcionality, but does it automatically load ‘new’ files which have a different structure then the other files as well?

What I'm looking for is a connector which looks at a folder or container, and then loops over all the files and puts these in the ODX data lake. So these files are not nessisarily the same. Later the data engineers can combine these files into one file if needed but it does not have to aggregate the files.

So file 1 might have 3 headers (ID, Name, Category) and file 2 might have 4 headers (Client, Firstname, Last Name, Gender). I just want them automatically loaded in the Data lake as parquets and able to select in the ODX window so you can drag them to the DSA / MDW

Hi @daniel,

that sounds like data integration / file share synchronization and not data warehousing. I.e. something you use azCopy / rsync / SnapLogic / Azure File Sync / OneLake shortcuts, etc. to solve. 

The core point of the ODX is to have the metadata and data organised, I wouldn't use it for the purpose of copying more-or-less random files from A to B.

The aggregatefiles option may need a synch to pick up new files or not, depending on how you set up the details (.rsd generation or not etc.) but it will expect all files per ODX Source to have the same structure.

Hi @rory.smith ,

Fair point. The next issue is that I need to get the data into TX. So I still need to set up a connector for each of the files. Whichs takes forever, costs me a connector every time and I only need the connector one time to get the file into the TX / ODX managed lake so it gets recognised by TX and I can load it in the MDW.

and the files will not have the same structure, but we do need to hang on to the data, create an ETL pipe and get the data to a semantic model to be analysed.

Hi @daniel,

I guess the wider business case would help steer the type of solution. If you can recognize the structure somehow and either move files to folders per structure (or have the source already do this), you can bundle similar files in single sources.

If every file is a one-off, I would tend to question the rhyme or reason of the entire approach. It would probably be better to try and enforce some kind of templating or business application to solve the underlying need and channel the data in a more predictable fashion.

If the case is such that you simply can't, then you need to accept some form of engineering for every new file structure as par for the course. The choice is then where the formalisation of structure takes place: upon ingestion or in the bi tool. As converting to parquet requires metadata analysis, I am not sure that it is feasible to construct a “copy from elsewhere to the ODX storage while converting to parquet” functionality that does not involve specifying things. Especially given the inherent freedom in csv and Excel files to break structures in interesting ways.

Hi @rory.smith ,

In this particular case the matter is the latter. The data comes in from different sources depending on the need of the customers of my client. They send in the data in the format that they have their data in. So standardistion is not possible and with the amount of different customers with different data not workable.

Ofcourse working wiith csv, flat files, excel and whatever file type the comes in would make this connecot very difficult, but I would like to think in solutions. If such a connector would exsist and you could set a file type (so all csv's are loaded) this would also greatly reduce the amount of connectors needed. And perhaps a loop within a loop. So first read the meta data in the location, detect that there are 3 file formats (csv, flat file and excel) and then create lists of the 3 different file type. First loop through the csv, then through the flat file and then the excel.

Back in the day when I worked a lot with QlikView this was no issue at all. I would do the above but then the output would be a QVD. So i guess we could make a custom connector which does this

I recon a lot of companies would be helped by this, even though I fully support your comments on the standardization / templating which would have been the best option.

Hi @daniel ,

given how the ODX storage is organised your loop would end up being:

• loop over location and extract file types (assuming all csv have the same internal formatting, i.e. don't allow character-separated and comma-separated etc., all Excel adhere to some structure, there is no mix of region settings)
  • loop over the files for each filetype and extract structure
    • for each filetype-structure combination create target folder and convert to parquet

In my experience this always ends up as a mess. In previous work where other parties delivered data to be used in benchmarking or reporting I have always restricted the freedom of data submitters by providing a format or template or API (or all of the above) to avoid needing 1000s of different handlings. Typically these parties are simply wanting a defined approach and are happy to conform to a target. The party handling the data probably doesn't want to spend money dealing with random stuff being submitted. I have had data submitters send me a pile of paper printouts of traffic count results because there was no definition of how to submit data as an example of what leaving it up to the customer can result in.

Of course this kind of handling is “easy” to do in Qlik; Qlik script is Turing compete so it does everything (though not with the features of a real programming language). And this is really my point: I don't think TimeXtender is the solution to this specific problem. Once something like a virtual ODX appears you can certainly pick up the parquet output from an appropriate tool or pipeline and push to a Semantic Model but I would solve this using a tool geared to this problem space.

In the past, TimeXtender has always advocated staying inside “their” problem space. It could be that there is drive to include this now, but that may mean waiting for improvements directly inside TimeXtender's native problemspace.

Hi @rory.smith ,
Standardizing and making sure you get the data in a format you can (easily) digest would be perfect. Unfortunately this is not always the case. Standardizing which file, file type, delimiting characters, region settings etc etc. should be the way to go. My issue now still being that I have a lot of Excel / CSV files with all different columns structures and I need a good solid solution. Doing it file by file in ADF (if ADF may be used) is way easier then creating a data connection for each of these files. Also I will never run out of the connector limit, which I might in TX right now.

Maybe it is not a tool for TimeXtender, maybe it will work for Exmon. My point is that TimeXtender has made my life so much better by offering an easy and solid way of building a DWH. Making things easy for the end user is working, take a look at Fabric for instance. Or what Qlik and Power BI are doing by just drag and drop your file or load it from the enviroment / (one) lake and it converts it to a file format that the tools can use and re-use. This should also be TX. Just drop you file, a Parquet / Delta is made and TX or some other tool can now ingest the data.

If this tool already exsists please let me know, but I think a tool like this should be naitivly supported by TX. Because Time Matters