Bill and the Time Machine

How to keep several years’ worth of data without jeopardizing calculation times? In this episode, Bill explains an idea to keep online a reasonable amount of data, while still having access to past information.

“Hey, Bill. I was trying to see my applications costs for the last three years, and couldn’t get past January the 2021. The Date Range selector wouldn’t show me any years before that. Don’t we have that data anymore?” Amy Rose was in charge of the Availability application and worked frequently with Bill’s TBM Office.

“That's correct, Amy. We only have up to two years available online: the current and the past one.”

“And why is that?”

“The more periods we have available, the longer the system will calculate, particularly during our software promotion cycles, our sprints, if you will.”

“So the data is gone?”

“Not quite. True, you cannot see it through the regular reports, but we have an alternative. It seems you haven’t come across our Historical Data report. I guess we haven’t done a good job of publicising it.” Bill chuckled. “Let’s look at it.”

Bill opened a report on his computer.

Fig. 1 - Historical Data report

“See, Amy? We have data since 2017. You’ll find drill-downs for business services, applications, and projects. As you can see, those are the tabs in the report.”

“This is great, Bill! We have Applications and Business Services by Tower and by Cost Pool, links between Business Units and Projects and Business Services and, most important of all, a drill-down from Business Services to Applications. Please open the Applications by Tower tab. That’s what I’m interested in.”

“Suit yourself, Amy.” Bill stood up and let Amy sit at his desk. “You can slice by any combination of years.”

Ellie Nakamura—the Finance TBMA—joined Amy and Bill.

“Say, Bill. How did you build that report? What tables are backing it up?”

Bill sat down at his computer again and checked out the report. He then clicked on the requested table.

“In every tab we have a table with a specific set of data, and a slicer for the years.”

“Why can’t we have a single slicer?” Asked Ellie.

“Because every table is different and has its own column with the fiscal year. Thus, the filter must have that one field and cannot be shared with other tables.”

“Wouldn’t it be more user-friendly if we had a common slicer?” Said Ellie.

“Yes, having to select the same time periods on every tab looks somewhat cumbersome.” Said Amy.

“I understand that, ladies. Thanks for the suggestion. I’ll see if we could combine all tables into a single one. That would allow having a single slicer. It used to be on my ‘someday/maybe’ list, but I’ll move it to the ‘to-do’ one. Point taken.” Bill smiled and took a quick note. He then clicked on the table and continued.

“As you can see, to do the drill-down from applications to Cost Pools, we have a table called Applications by Cost Source MAP.”

Bill went to the Project Explorer, searched for that table and opened it. 

“The original data comes from 2017, but then we append a series of tables, one per fiscal year. We use the resulting dataset to feed the report.”

Fig. 3 - List of tables in the Append step

“You're saying you have such a MAP file for each of the tables in the report, right?” Asked Ellie.

Bill nodded.

“So, you could append all MAP files into a single table that would have all the data…” Continued Ellie.

“Yes. We’d need to make sure that every tab reports only the rows that relate to that specific view. Now that you mention it, Ellie, I think I’d know how to do that.”

“Guys, this is very interesting, but where do you get the data in the first place? I guess you cannot get to past years like us…” Said Amy.

“When we’re sure the previous year’s data is solid, we run a special report that extracts the data in a format the report you’re looking at will understand.” Said Bill. “We then create the new tables for the fiscal year and load the data to each one of them.”

Fig. 4 - Report that collects data for the fiscal year overview

“Is that all manual, Bill?” Asked Ellie.

“I guess we should put together some automation to do this.” Said Bill. “The thing is that it’s something we do once a year, so I haven’t felt the need to prepare the groundwork that would enable such automation.”

“But you could do it.” Insisted Ellie.

Bill smiled and typed something. “Yes, I just added an entry in my ‘to-do’ list. We’ll create a Datalink connector, or a set thereof. We’ll run those connectors on demand, and the program will load the data to the appropriate tables.”

“What happens after that?” Asked Amy.

“We then change the project settings and remove the year before that.” Said Bill. “This way we only have available the current and the previous years. Information is still available, as we have been discussing today.”

“Why can’t we have all the data online?” Asked Amy.

“Any time the system has to calculate, e.g. if we change something in the configuration or load new data, it will scan all the time periods. This takes time.”

“Couldn’t we just limit the report calculation periods to those two years?” Asked Ellie.

“Yes, we could do that, but it would only help a little. The system calculates tables, models and metrics prior to reports, so having all years since 2017 would increase calculation times considerably.”

“Thanks for the insights, Bill. Very useful.” Said Amy.

“Yes, I didn’t know about that either.” Said Ellie.

“Sure, ladies. My pleasure.” Said Bill.

***

Copyright (c) Guillermo Cuadrado 2022