Data Mart - consolidating historical snapshot files

[sevenhills]

Hi

The company has a legacy three system reports.

• Hospital pharmacy clients from 2009 - 2013 on Internal Applciation.
• Hospital pharmacy clients from 2013 - 2016 on Microsoft Dynamics.
• Hospital pharmacy clients from 2016 (dec) onwards on Oracle 

Each systems' historical report snapshots periodically are generated as time progress and kept as files. We are not pulling from these systems, but we need to pull data from the report snapshot files.

PBI - Datamart: requirement: To consoldiate all historical report snapshot files into one.

Is there any guidelines or samples for data pull?
Is there any dataflow or data mart samples to process this semi-structured data?

Thanks in advance.

[sevenhills]

Any other user comments?

[sevenhills]

For POC, I demoed two tabular reports as below:

- Data source: took few of historical report snapshot print format files for simple ones

- Data flow: from Data Source and transform to a data model for reports consumption

- Dataset, paginated reports, interactive reports

Now they would like to have DataMart... 

[lbendlin]

I still don't fully understand what problem datamarts are trying to solve. Is it targeted at smaller businesses ? Is it a subversion of a company's IT department/datawarehouse? indirect advertisement for Azure SQL? A solution looking for a problem?

[sevenhills]

Check this to understand Datamart

https://docs.microsoft.com/en-us/power-bi/transform-model/datamarts/datamarts-overview 

[lbendlin]

"Datamarts are a fully managed database that enables you to store and explore your data in a relational and fully managed Azure SQL DB."

Sure. But at what cost?  There's no free lunch.  Somewhere there must be a cost, like Azure consumptions etc.  Not to mention the mental cost of the silly fights between Business, IT and the CFO.

[sevenhills]

We went through the excercise, after few weeks of back and forth, they want datamart. Not my decision. 

Even we tried to present other points

- why cant we consume as is with dataflow and datasets?

- who wants to track 2009 data? how do we build metadata for these? 

- why cant they create a offline Dynamics server and get the data as structured for upto 2016 ? (after retrieving making this offline)

- We already have three versions of DW, based on three legacies

- The cost of going through these report output snapshot files is high too. Around 600 reports and different frequencies like weekly, monthly, quarterly ... blah, blah. 

Gave up.

Coming back to the needs, at the end, we are given files which are like printed report outputs. 

PBI - Datamart: requirement: To consoldiate all historical report snapshot files into one.

Is there any guidelines or samples for data pull?
Is there any dataflow or data mart samples to process this semi-structured data?

[lbendlin]

Does it have to be a datamart?  Could it be dataflows, or plain CSV files?