Updating Report Based on a Data Module

[Cognos_Jan2017]

Pretty sure we know the answer.

A Report built on a Data Module fed from 2 Excel files ...
1 - SQL ServerData in the Cloud App
2 - From Excel data NOT in the SQL Server Cloud App

The SQL Server data DOES have to be Uploaded to update the Excel file in the Data Module?

If the answer above is YES, is there a way to automate running the Cognos Report making the
Excel file and the automatically uploading?

Another issue will be ...
Uploading places the Excel file in "My Content".  We then have to Move that Excel file to the
Path holding the Data Module for the Report.

TIA, Bob

[MFGF]

Pretty sure we know the answer.

A Report built on a Data Module fed from 2 Excel files ...
1 - SQL ServerData in the Cloud App
2 - From Excel data NOT in the SQL Server Cloud App

The SQL Server data DOES have to be Uploaded to update the Excel file in the Data Module?

If the answer above is YES, is there a way to automate running the Cognos Report making the
Excel file and the automatically uploading?

Another issue will be ...
Uploading places the Excel file in "My Content".  We then have to Move that Excel file to the
Path holding the Data Module for the Report.

TIA, Bob

Hi,

I'm not sure I quite understand your description here. Is your Data Module based on a database (SQL Server) or on a file? If it's based directly on SQL Server, there is no uploading to do - as soon as the SQL Server data changes you'll see it reflected.

If not, can you explain how the data gets from SQL Server to the file you need to upload? Why have you chosen this as an approach?

I have a major concern with production reports build over uploaded data. You risk introducing data governance issues. The main role for uploaded files is personal data discovery - ie "is there any value in this data, and if so what is it"? This is why files get uploaded to My Content, and why there is no scheduling for uploads. If the data is required for production reporting, it should really be included in your data warehouse and go through the normal data cleansing/checking/validation processes so that you can be sure it is correct and trustworthy.

Cheers!

MF.

[Cognos_Jan2017]

Thank you MFGF.

The Data Module has 2 Excel files making a UNION query.  The 2 Excel files in the Data Module are ...

1 - Run to Excel from SQL Server from data from a 3rd-party Cloud App we have had about 2 years.  We understand SQL Server data (used separately) updates w/o needing a Data Module.

2 - Prior to getting the Cloud App above, data was kept in only an Excel file for around 10 years.

The purpose of the Report, sourced from the Data Module, is to find the Most Recent (found from utilizing the Maximum function) date of a Data Item.

To do that, we need to Union older data w/ the SQL Server data.  It works fine.  The only "negative" is having to update the SQL data and upload that Excel file for the Data Module, and then Move it to the appropriate Path in Team Content.  Really no big deal.  Would be good if someway to code all that as "automation".

I have recommended to Mgmt to keep the Data Module Report above, but to add another Report looking for the Maximum date of the Data Item from only SQL data.  That way, "current" data is easily seen, and no Data Module is needed.

The "gotta" ... this Report examines many Stores "measured" positively when the particulate Data Item doesn't record a new Entry Date for the Data Item.  Those Stores need that "aged recognition" (by how long since that Data Item had an entry) by having the older non-SQL data available.

Hope this doesn't confuse things more?

Do you see our above technique as perhaps "the best way" to build this Mgmt Report ... or can you recommend a better way?

TIA, Bob

[MFGF]

Thank you MFGF.

The Data Module has 2 Excel files making a UNION query.  The 2 Excel files in the Data Module are ...

1 - Run to Excel from SQL Server from data from a 3rd-party Cloud App we have had about 2 years.  We understand SQL Server data (used separately) updates w/o needing a Data Module.

2 - Prior to getting the Cloud App above, data was kept in only an Excel file for around 10 years.

The purpose of the Report, sourced from the Data Module, is to find the Most Recent (found from utilizing the Maximum function) date of a Data Item.

To do that, we need to Union older data w/ the SQL Server data.  It works fine.  The only "negative" is having to update the SQL data and upload that Excel file for the Data Module, and then Move it to the appropriate Path in Team Content.  Really no big deal.  Would be good if someway to code all that as "automation".

I have recommended to Mgmt to keep the Data Module Report above, but to add another Report looking for the Maximum date of the Data Item from only SQL data.  That way, "current" data is easily seen, and no Data Module is needed.

The "gotta" ... this Report examines many Stores "measured" positively when the particulate Data Item doesn't record a new Entry Date for the Data Item.  Those Stores need that "aged recognition" (by how long since that Data Item had an entry) by having the older non-SQL data available.

Hope this doesn't confuse things more?

Do you see our above technique as perhaps "the best way" to build this Mgmt Report ... or can you recommend a better way?

TIA, Bob

Hi,

Why have you taken the approach of dumping your SQL Server data into an Excel file then uploading it to Cognos Analytics? You can connect from Cognos Analytics directly to SQL Server and use the tables in a Data Module. As Data Modules don't directly support Unions, you must be unioning the data in a report, I'm guessing? You can do this with data from SQL Server tables in a data module just as easily as with data from an uploaded file in a data module. Wouldn't the obvious approach be to remove the need to upload data constantly by using a direct connection to SQL Server instead? It seems like you're generating a huge amount of ongoing maintenance with the approach you're taking?

Overall, the most robust solution would be to include both the old data and the data from your Cloud app in a data warehouse, with your ETL processes adding the data currently in SQL Server automatically on whatever basis you require? You'd then be able to create a package over this data, and there would be no need for unions or data modules or uploading at all. Using uploaded files might be an "interim solution", but the problem with interim solutions is that, although they are not robust and scalable and governed, the business sees the problem as being "solved" so there is never any will to deliver a proper solution. If you have the ability, it's better to architect a robust solution from the outset :)

Cheers!

MF.

[Cognos_Jan2017]

Thank you MFGF.

You can do this with data from SQL Server tables in a data module just as easily as with data from an uploaded file in a data module. Wouldn't the obvious approach be to remove the need to upload data constantly by using a direct connection to SQL Server instead? It seems like you're generating a huge amount of ongoing maintenance with the approach you're taking?

True, I agree IF all the data was in SQL Server, we wouldn't need a Data Module at all (as I understand those).

Yes, the UNION utilized is a Query in an Analytics Report.

After writing early today, I realized I forgot to mention we had considered ETL, but Mgmt decided
to try the Data Module "concept" as I learned that.  We got Analytics the end of May 2017, and I
started learning it then.  All the material I read, and watched (YouTube), mentioned using Data
Modules w/ "uploaded files".  I don't remember anything mentioned about directly using Data Modules
w/ the Package (SQL Server) provided by the Cloud App.  As I understand it, the Cloud App Vendor
won't let "Subscribers" manipulate Packages within Framework Manager.  No problem ... we utilize
what they give us.

The crux of the problem appears to be the "older" 10-years data in Excel.  Right now, that data
will NOT be loaded into SQL Server.  Yes, it causes "maintenance problems", but UNLESS that
data IS loaded into SQL Server, how else can I "UNION" the older data w/ current SQL Server
Cloud App data?

Also, can you recommend a good source of utilizing SQL Tables in a Data Module?  IF all the
data been utilized is from SQL Tables, then there should NEVER (?) be a need for including
that data in a Data Module?

TIA, Bob

[MFGF]

Thank you MFGF.

True, I agree IF all the data was in SQL Server, we wouldn't need a Data Module at all (as I understand those).

Yes, the UNION utilized is a Query in an Analytics Report.

After writing early today, I realized I forgot to mention we had considered ETL, but Mgmt decided
to try the Data Module "concept" as I learned that.  We got Analytics the end of May 2017, and I
started learning it then.  All the material I read, and watched (YouTube), mentioned using Data
Modules w/ "uploaded files".  I don't remember anything mentioned about directly using Data Modules
w/ the Package (SQL Server) provided by the Cloud App.  As I understand it, the Cloud App Vendor
won't let "Subscribers" manipulate Packages within Framework Manager.  No problem ... we utilize
what they give us.

The crux of the problem appears to be the "older" 10-years data in Excel.  Right now, that data
will NOT be loaded into SQL Server.  Yes, it causes "maintenance problems", but UNLESS that
data IS loaded into SQL Server, how else can I "UNION" the older data w/ current SQL Server
Cloud App data?

Also, can you recommend a good source of utilizing SQL Tables in a Data Module?  IF all the
data been utilized is from SQL Tables, then there should NEVER (?) be a need for including
that data in a Data Module?

TIA, Bob

Hi,

A data module lets you assemble data from multiple sources. You can have your old, static data in a single uploaded file, and your newer data in tables in SQL Server - all part of the same data module. If you have a package already describing your SQL Server tables, you can utilise this in your data module. If you want to connect to the data directly in SQL Server, you can do this instead.

There's a video on Youtube that shows blending data from different sources in a data module - an uploaded file, a package (over a SQL Server database), a direct connection to a DashDB database (in the cloud), and a direct connection to a SQL Server database. It's worth spending a few minutes to watch?

https://www.youtube.com/watch?v=AXcG43tN6Zs

Spookily, the presenter has the same initials as me :)

MF.

[Cognos_Jan2017]

Thank you MFGF.  Happy belated Halloween.

Early am here.  Today, I will study your link after completing another non-Data Module due this morning Report.

Yesterday I searched a trusted Data Module video and confirmed he didn't mention SQL Tables in a Data Module.

Hoping this week adds knowledge about Data Modules.  It would be VERY COOL as this particular Report is one of our Manager's favorites.

Will add another Post to this thread after I absorb this, and successfully implement it.

THANK you again, Bob

[Cognos_Jan2017]

MFGF ... I watched the video you sent.  YES, "Packages" can be part of a Data Module.

I have this on my List of things to learn.  I have to be pleased that I have a heavy workload now,
and must get those completed first.  BUT I WILL learn the "mix" of Packages and an Uploaded
Excel file ... and let you know how that goes (as I learn that).

WILL report back in this thread hopefully within 1 or 2 weeks.

THANK you again, Bob

[MFGF]

MFGF ... I watched the video you sent.  YES, "Packages" can be part of a Data Module.

I have this on my List of things to learn.  I have to be pleased that I have a heavy workload now,
and must get those completed first.  BUT I WILL learn the "mix" of Packages and an Uploaded
Excel file ... and let you know how that goes (as I learn that).

WILL report back in this thread hopefully within 1 or 2 weeks.

THANK you again, Bob

Hi,

Not just packages. The video also shows how a data module can use a direct connection to database tables (if you want to take that approach). :)

MF.

[Cognos_Jan2017]

True.  I'm still writing other Reports, but hope to test a Data Module composed of ...
1 - Package Source
2 - Excel file NOT in our App.

Hope to report back on that by this Friday at the latest.

Our company hopes to "blend" non-App data ("old" data
before subscribing to the App) w/ the App.

There was another "App" that produced MUCHO data which can
be beneficial and we had automated that data to Excel files.

[Cognos_Jan2017]

MFGF - Yes, a Data Module composed of ...
1 - Package Source
... and ...
2 - Excel file w/ data NOT in our App

... can indeed be utilized in a Report.

Thanks again for being A-OK, Bob

[MFGF]

MFGF - Yes, a Data Module composed of ...
1 - Package Source
... and ...
2 - Excel file w/ data NOT in our App

... can indeed be utilized in a Report.

Thanks again for being A-OK, Bob

That sounds like a good outcome! Thanks for the update!

Cheers!

MF.

[Cognos_Jan2017]

You are welcome MFGF.

We plan on authoring a bunch of these.

THANKS again, Bob