Click here to see the listing of all new features added to 2025.02.

Fixes in Pyramid 2025.02

Model

• Fixed an issue where import failed when a model contained a Parquet destination node
• Fixed an issue where a model schedule continued executing even after the model was deleted
• Reduced sort memory usage to prevent OutOfMemory errors during high parallel sort/join workloads
• Fixed an issue that was causing Roles to be removed from a Model after importing the same model to a different server
• Fixed an issue where Parent-Child Hierarchies displayed the wrong related table when hovering over the PCH attribute in the Discover view
• Resolved a blank-screen issue when switching from new Discover to Model
• Fixed an OutOfMemoryError in the Summarize Node that triggered a “Satellite monitor IOException 7004” when processing a model
• Fixed an issue where joining large tables from a MariaDB source in the Model ETL Flow would timeout and fail to read data
• Fixed an issue where using Pyramid Content as a source in a model had failed
• Fixed an issue where queries running through a Pulse server returned only first 100 records
• Fixed an issue where column data types appeared mismatched in a Union node, even when they were identical
• Fixed an issue where running Python on a table sourced from Pyramid Content caused an error
• Fixed an issue where a Model Preview based on a Pyramid Content source crashed the runtime engine
• Fixed an issue where ETL calculated columns using string comparisons produced incorrect results
• Improved the error message shown when a stored procedure used in an ETL Custom Query is missing required columns 

Discover

      Queries:

• Resolved an issue in which enabling caching caused the grid to display an outdated cached view when slicers were updated
• Fixed an issue where removed slicers still impacted Analyze Further when the member existed on both rows and filters
• Fixed an issue where aggregations on parameter members did not work
• Fixed an issue where an “Explanation” note appeared whenever a regression line was added although 'Show Notes' was off
• Fixed an issue where formulations using a Custom Column and saved under a Custom Column hierarchy in the Select Hierarchy dropdown were inaccessible
• Fixed an issue where a tooltip action displayed “Not enough information” when a “Pyramid Content” URL was present on columns
• Disabled context calculations in the filter dropzone, which were never meant to function as filters
• Fixed an issue where the “Explain” feature displayed incorrect results in a Discovery
• Fixed an issue where a Discover failed due to a Sort on member key error

• Fixed error when querying a measure with a disconnected table while having filtering logic

      Visuals:

• Implemented performance optimizations for AG Grid, resulting in improved rendering speed and responsiveness when handling large datasets
• Removed redundant space from a row header
• Fixed an issue where the data label background in Bar Charts stretched across the entire bar depending on the window or screen size
• Fixed an issue where a slicer appeared as "Loading" when first launching the Discover report
• Fixed a visual issue where a specific chip in a Context Calculation appeared to be unselected when editing the calculation
• Resolved an issue where extra whitespace appeared in rows, occurring only when the second member on the rows was a URL type member
• Fixed an issue where the start and end points of the IBCS waterfall chart didn't display tooltips
• Fixed missing total labels on stacked charts when “No Scale” mode was enabled
• Fixed an issue where data labels were not displayed on maps
• Fixed an issue where drilling down on a Shape Map did not function correctly
• Fixed data labels not showing in certain columns when using a Marimekko chart
• Fixed incorrect colors in conditional formatting 

Present

• Fixed an issue where multi-selection slicer choices were ignored when users quickly switched to selecting from another slicer

• better fallback handling for invalid order-by-columns

• Resolved an issue causing the tooltip action to appear in an unexpected position within an embedded presentation
• Fixed an issue where a Parameter icon is shown in a direct URL when it should be hidden
• Fixed an issue that caused an endless spinner when attempting to edit a disabled slicer in Presentation Runtime mode
• Fixed an issue where the search box inside slicers in Presentation Runtime did not allow pasting via the context menu
• Fixed an issue when "Run Slicers" didn't trigger the dynamic text in a presents
• Fixed an issue where enabling a slicer caused Present to timeout
• Fixed an issue where an action used to switch between mini-tabs did not function as expected
• Fixed an issue where the width of button slicers could not be changed
• Fixed an issue where slicers did not update mini-tabs when changing to a different mini-tab
• Fixed an issue where a Tabulate item located in private content was causing a timeout in a Present
• Fixed an issue where Model Mapping failed when a dimension or attribute containing NULL values was also used as a slicer, due to failed NULL matching
• Fixed an issue where presentation text scaled down incorrectly on smaller screen resolutions
• Fixed an issue where cascading slicers failed to load when “All” was selected and the data source was SAP BW
• Fixed a JavaScript error when applying a date slicer bookmark that threw “Cannot read properties of undefined (reading 'caption')”

Tabulate

• Fixed and issue where a Tabulate stopped working due to a null exception
• Fixed an issue where the custom Grand Total name did not update in Tabulate
• Fixed an issue where Tabulate returned errors when adding a slicer to a new Tabulate 

Publish & Subscription

• Fixed an issue where in-line email license restrictions were not enforced correctly
• Fixed an issue where report subscriptions failed with a timeout error, requiring a Task Engine restart
• Fixed an issue where new drop-down slicer element selections from a hierarchy were not saved in a publication
• Fixed an issue where selected items in a tree slicer were deselected when changing the slicer type to drop-down in a publication
• Fixed an issue where report titles were not visible in publications, and the “Report Title/Subtitle” switch was disabled under Visual
• Fixed an issue preventing publication downloads via link when the user was not authenticated 

Formulate

• Fixed a caching issue involving Custom Columns based on parameters combined with a nested calculation
• Fixed an issue where the DayOfWeekNumber function produced incorrect results on German-locale RTE servers
• Fixed an issue where the “Last X” node returned incorrect results for naturally ordered lists
• Fixed a query error in Discover reports caused by the use of Switch and List formulas 

Illustrate

• Resolved an issue in which missing data-source permissions for a single illustration caused other dashboard illustrations to fail

   

Content Management

• Fixed an issue where Presentation items were removed from users’ Favorites after content migration, even though the items migrated successfully and retained their original IDs
• Fixed an issue where details were missing when hovering over the item name for one-off illustrations that were made shareable
• Fixed an issue where saving a new version in a duplicated presentation caused the original presentation to become corrupted
• Fixed Bookmarks and Subscription issues after Cut, Paste, and Replace of a Presentation
• Fixed an issue preventing discoveries from being deleted due to falsely remaining linked to a Presentation
• Fixed an issue where a measure from Measure Settings in a Custom Column incorrectly appeared in Search and the Discover content tree
• Fixed an issue where migration to production failed due to duplicate keys in bookmark data 

Printing & Export

• Fixed an issue where embedded presentations in RTL UI were printed to PDF in LTR format
• Fixed an issue where certain levels of opacity for text or shapes were not printing when exporting to PPTX
• Fixed an issue where disabling “Merge common cells” during Excel print/export still merged the subtotal row
• Fixed an issue where an already-encoded URL link in a Discovery could not be opened when printed to Excel
• Fixed an issue where the "Add Link-Back" feature was not available under 'External Busting premium' license
• Fixed an issue where exporting a Presentation or Publication with interactions added unwanted hyphens to the file name
• Fixed an issue that caused export to Excel to fail
• Fixed an issue where exporting a Presentation containing a Tabulate grid to Excel exposed hidden Tabulate columns
• Fixed an issue where exporting a Tabulate from a Presentation to Excel resulted in unformatted measures, unlike direct Tabulate exports
• Fixed an issue where Excel exports from a Tabulate were formatted differently depending on whether the Discovery was added as “Direct” or “Formula.
• Fixed an issue where mixed Hebrew and English text was printed to PowerPoint with incorrect font sizes 

Mobile

• Fixed an issue that caused an infinite loading screen when a user attempted to log into the mobile app
• Fixed an error when selecting slicer values in Present on iPad when using Apple Magic Keyboard
• Fixed an issue where mobile users were not validated correctly when using an Azure proxy with SAML authentication and device ID checks enabled
• Resolved navigation panel clipping when using large system font sizes on iOS 

Admin

• Resolved an issue where clicking the key icon in the licensing widget in the Admin Hub resulted in an error
• Fixed an error that occurred when clicking the key icon in the Licensing widget of the Admin Hub
• Fixed an issue where the IMDB Status widget did not function properly
• Fixed an issue where Task Manager filtering did not return results after navigating to another view and then returning
• Tenants folder were added to the "Add content or widget" section of Hub templates 

Security

• Numerous third party components with critical, high and medium vulnerabilities have been upgraded.
• Support for legacy security capabilities has been removed. 
• Java was upgraded to 21.09

Other 

• Implemented various security improvements across the platform
• Fixed an issue that caused an upgrade to fail with a c.pa.tools.init.SetupToolsExecutor error
• Resolved a problem that caused the runtime to crash continuously, creating additional runtime engine processes each time
• Fixed an issue where some openID tokens were rejected by Pyramid causing login failures
• Fixed an issue where upgrade failed due to duplicated values in the user_prom_selection table
• Fixed an issue where the updateRole API in the Pyramid SDK always applied the isHidden flag
• Fixed an issue where the runtime engine didn't start after an upgrade
• Fixed an issue where running the CreateUsers and UpdateUsers APIs in parallel caused incorrect license allocations
• Fixed an issue where the Box JWT authentication connection was broken
• Removed the use of the USE function from the upgrade scripts
• Removed USE statements from upgrade scripts
• Fixed an issue where the internal Python environment used the Conda default channel despite Conda Forge being selected
• Fixed an issue where R appeared as installed (green) after an upgrade, even though it was not actually installed
• Fixed an issue where upgrades failed because the IRDB configuration file was not found
• Fixed an issue where upgrading failed when Pyramid was installed on one drive and the repository directory on another 
• Various help improvements 

• Printed exported excel files to keep format
• Row background color is not exporting same in excel
• Bold formatted rows from Discover to be retained when exported to excel
• Custom grand total text print to excel
• Retain the header format from Tabulate to Present when Print to Excel
• Custom Query Filters Exported to Excel
• Have Excel export format match chosen theme instead of the default theme
• Excel Export: More Information about filter
• Discovery export - include a summary of filters with export
• Maintaining Formatting when exporting Data
• Export tabulate visual area via Present
• Download Filters Titles to Export (partial for Excel only)
• Export to Excel- Title and Breadcrumb to be separate selection box and only include slicer values instead of column names + slicer

Click here to see the issues fixed in 2025.01. 

Model

Discover

Formulate

Present

Publish

Tabulate

Content Management

Administration

Embedding

Mobile

Infrastructure and APIs

Documentation

Click here to see the listing of all new features added to 2025.01.

Fixes in Pyramid 2025.01

Model

• Fixed an issue where ETL Progress was not showing up for previous runs for schedules triggered from a Pyramid Event
• Fixed an issue where Masking was not working for Calculated Columns
• Fixed an issue that was causing a 'Satellite monitor timed out 7001' error when running a model
• Fixed a Pulse timeout issue that was causing a "Stream has been closed due to timeout of inactivity when using a Pulse Data Source in ETL" error.
• Fixed an issue where a deselected column in a SELECT node caused preview failures on AS400 data sources
• Fixed an issue where the database list was empty if a custom JDBC driver did not return catalogs
• Fixed an issue where writing to an S3 target had failed 
• Fixed an issue where a private role set on a model did not take precedence over a public role
• Fixed a specific scenario that caused a black screen and corrupted the model
• Fixed an issue where selecting 'Parquet' as the type for an Azure Blob source resulted in a blank screen
• Added BigInteger to the Python output column dropdown
• Fixed an issue where a Postgres query to the Pyramid repository failed due to incorrect syntax when a model process failed to run
• Fixed an issue where opening a Model resulted in a blank screen
• Fixed an issue that caused missing data when multiple data flows wrote to the same IMDB target
• Fixed an issue where uploading large Google Sheets failed with a 429 Too Many Requests error from the Sheets API
• Fixed an issue where previewing data from a Google Sheet caused a "Error getting excel row" 

Discover

      Queries:

• Fixed an issue where joining tables using a Calculated Column that referenced multiple tables had failed
• Fixed an issue where the first load of a Direct URL report had failed with a 404 error
• Fixed an issue where the Inverted hierarchy sort didn't work from the context menu
• Fixed an issue that was causing the DayOfWeek function to return unexpected results
• Fixed an issue that caused a query failure while processing zipped information from SAP BW that contains specific special characters
• Fixed an issue where the Discovery row limit set in User Defaults was not applied correctly
• Fixed an issue where a Discovery failed to open with a "Cannot read properties of undefined (reading 'forEach')" error
• Added explicit handling for Oracle’s inability to cast between varchar and nvarchar automatically
• Fixed an "Invalid Column" error when opening a Discover containing an unlinked attribute and measure
• Text direction settings were added to rows and columns to assist with signs in right-to-left languages
• Fixed an issue where the SAP BW Tree View Icon was missing on the New Discovery page
• Fixed an issue where a date calendar slicer selection was not injected into a tooltip action
• Fixed an issue where a Parent-Child hierarchy with "Include All Level" and "Roots" orphan handling didn't expand to include the root members

      Visuals:

• Fixed an issue where the X-axis labels on a chart became misaligned after adding more data or increasing the label font size
• Fixed an issue where the Total Label position was incorrect in a Stacked Column/Bar chart with negative values
• Fixed an issue where X-axis labels were overlapping each other in a chart with a Range Slide
• Fixed an issue where Data Labels on a line chart were aligned slightly to the left
• Fixed an issue where Legend formatting options didn't work for a regression line
• Fixed an issue where changes to measure properties in Discover were not applied as expected
• Fixed an issue where chart labels for negative values were missing when the chart was displayed in a small container
• Fixed an issue where the Date Slicer pop-up was cut off in embedded content
• Fixed an issue where a measure that was added to the Color drop zone caused hierarchical levels to expand incorrectly in grids
• Resolved the inconsistent placement of total data labels for negative values
• Fixed an issue causing overlapping titles when using Multi Chart
• Fixed an issue where the IBSC Waterfall chart showed the secondary axis measure as the far-right bar label instead of the primary axis measure
• Added an option in the Legends formatting menu to show the Shape Type in the legend

Present

• Fixed an issue where Zoom Lasso was not working in a full-screen report
• Fixed an issue in the Action Panel where Action Settings' Values were not displayed until the Edit button was clicked
• Fixed an issue where using a jump action from a direct link would open a new tab with an incorrect URL containing an extra "Direct", resulting in a 404 error
• Fixed an error that occurred when deselecting a list in Slicer Settings with a measure applied in the "Non Empty Filter" and "Hide Empty Hierarchy Members" enabled
• Fixed a Slicer Element Limit issue that was causing a cascading filter to not show all relevant elements
• Fixed an issue where a disabled slicer still filtered the target when using Model Mapping
• Fixed an issue where the slicer query was executed despite the slicer being disabled
• Fixed an issue where a deleted parameter value remained selected if it had been set as the default
• Fixed an issue where an interaction in a Custom did not work as expected
• Fixed an issue where an interaction failed to correctly filter a grid after it was expanded and collapsed
• Fixed an issue where a Master Page that was created for a Theme did not appear when selecting this Theme in a new Presentation
• Fixed an issue where duplicate #ModelProcessedDate text blocks in the content master prevented slides from running
• Fixed an issue where a Zoom Lasso selection couldn't be removed
• Fixed an issue that caused the order of mapped hierarchies to change under Model Mapping
• Fixed an issue that was causing inconsistent Insight results
• Fixed an issue where a mini-tab didn't update after changing the slicer selection
• Fixed misleading confirmation from the chatbot after an attempted filter change

Tabulate

• Fixed an issue where adding two Discover reports from the same data source and using shared slicers caused the slicers to be duplicated. This resulted in errors when attempting to edit or delete them via the Interaction Manager 

Publish & Subscription

• Fixed an issue where page triggers failed to update when slicer selections changed
• Fixed an issue where calendar slicers using "current date" with "last X days" did not dynamically update in scheduled reports, causing outdated data to appear in subscriptions and publications
• Fixed an issue where subscriptions failed when Discovery parameters or slicers were changed
• Fixed an issue in publication that prevented hierarchy tree slicers from being used as dropdowns

Formulate

• Fixed an issue where the Custom Order didn't work for items with StrToMember
• 'Custom Column' preview is now sorted according to the specified 'Sort By' field
• Fixed an issue where a PQL Case statement returned unexpected results
• Fixed an issue where a List caused a report to fail after an upgrade to 2025
• Fixed an issue where Lists didn't work consistently following an Oracle Repository Patch

Illustrate

• Fixed an issue in the Asset Tooltip Designer where the line height did not adjust when the default font size was increased 

Content Management

• Fixed an issue where saving content that was opened from a Direct URL had failed
• Fixed an issue where non-admin users were unable to see the "Business Logic" icon in certain reports
• Fixed an issue where the Merge functionality failed to update content correctly in certain scenarios
• Fixed an issue where deleted content was still accessible using a Direct URL Link
• Fixed an issue where importing a Presentation to replace an existing one failed if the original included bookmarks and/or schedules and a global parameter

Printing & Export

• Fixed an issue where a Panel title was incorrectly aligned in a print
• Fixed an issue where Excel export failed if the combined report and page repeater member names exceeded the 31-character sheet name limit
• Fixed an issue where certain levels of opacity for text or shapes were not printing when exporting to PPTX
• Fixed an issue where exporting a Tabular grid with a URL attribute to Excel caused all columns to appear as hyperlinks
• Fixed an issue where a Dynamic text in a table cell was cut off in a print
• Fixed an issue where a Matrix Table in a printed Presentation incorrectly showed the slicer caption as a breadcrumb

Mobile

• Fixed an issue where the Smart Insights close button didn't respond in the iOS App
• On iPad, "Jump to Content Item > New Tab" now opens correctly in a new tab instead of a pop-up
• Fixed a redundant (x) close button in the UI
• Fixed an issue where the navigation bar would disappear on a mobile device after clicking a hyperlink 

Data Sources

• Added support for Microsoft Entra ID authentication for "MS SQL Server Azure and Azure Synapse" data sources

Admin

• Fixed an issue where removing an AI service instance from the server instances did not remove it from the AI servers list, which sometimes caused ML environment reinstalls to fail by referencing an invalid AI server.
• Fixed an issue where auto-provisioning continued running after deactivation
• Fixed an issue where removing an AI server from the server instances list without refreshing the browser caused the old server instance to be used when installing AI environments
• Fixed an issue where an error message appeared when attempting to save changes in the Logs Clean task within Task Manager
• Fixed an issue where filtering users by Secondary Phone and External ID, did not function correctly 

Other 

• Fixed an issue where Roles were not inherited from the parent folder when importing via the API
• Fixed an issue causing the Router service to stop with a "Cannot invoke because 'machineStatus' is null" error
• Fixed an issue that was causing a "Can't run k8s pod monitor" error
• Various help improvements
• Fixed an issue where a Discovery tutorial was unresponsive
• Fixed an issue that was causing IMDB connection failures
• Fixed an issue that caused an "Exception while executing batch" error to appear in the System Logs
• Fixed an issue where embedded report CSS was interfering with the host application's styling
• Fixed an issue where the Recommendation task caused an out-of-memory error
• Fixed an issue where the login failed with error 500
• Fixed an issue where users could be assigned to roles from other tenants via the API, even when "Enable cross-tenant user roles" was disabled
• Fixed an issue where reusing one of the last 12 passwords with High strength settings caused the password change screen to hang without feedback
• Fixed a member security issue on a parent-child hierarchy
• Fixed an issue where, after upgrading  to 2025, certain users were missing roles following the execution of the automatic provisioning job using Active Directory as the source
• Fixed an issue where some folders' permissions changed after upgrading from 2023 to 2025 on Linux
• Resolved an error triggered by clicking "Load current present dashboard" in a Conversation
• Fixed an issue where the User "Sync" didn't update the username
• Resolved a log cleaner failure caused by too many parameters
• Fixed an issue where the idle connection cleaner could lose connection from the pool

See the Pyramid 2025 "Newton" Product Announcement for full details.

Model

Execute a chain ad hoc in modelling

Parquet files as source for models

Tabulateexcel write back to data prepmodel

Enhanced llm integration and functionality workflow node with selectable llms providing multiple new use cases

Sorting on multiple columns in model

Add ability to choose new field names for summarize node in modeling

Discover

Allow analyst users to select bookmarks while in discover lite

Allow analyst users to generate a url in discover lite present lite

Add a pause button to the discover lite

Control autorun in analyze further mode

New features for the maps

Add show total labels feature to stacked 100 column and marimekko chart

Legend graph formatting miss match

Copy and pasting rawvisual data out of pyramid into google sheets

Lasso functionality in maps

Resizing multiple columns in a discover

Show dimensionss subfolders when swapping

Nested sort

Sorting order to be retained when swap dimension feature is in use

Nested sort

Formulate

Allow existing formulate objects to change attribute context

Present

Time delayed filter activation for present

Pause button to allow slicing without immediately updating

Allow analyst users to generate a url in discover lite present lite

Adding mini tabs to scrollable panel or vice versa in present module

Adjustable tile template size for mobile

Lasso functionality in maps

Nested sort

Shadow for Shape

Export / print button to be mapped with a single chart at the presentation layer

Publish

Include multi select dropdown slicer in publish

Illustrate

Enhancing illustration capabilities

Embedding

Improve exportinga single grid to excel from an embedded present

Admin

Add support for auto provisioning with saml

Add group role button missing in saml authentication mode

Pass adfs groups to pyramid in saml authentication mode

Content Management

Add folder favorites for making saving content easier

Report management copy and paste your own reports and folders as viewer

Default content view settings for viewer licenses

Other Features

Api endpoint for imdb database sizes

Key bug fixes added into Pyramid 2025 will also be released as well as part of an upgrade to Pyramid 2023 Archimedes and Pyramid 2024 Fibonacci in the new few weeks.

Fixes in Pyramid 2025

Model

• Fixed an issue where a Model using a custom connector lost the ability to select a data source in the target environment after migration
• Fixed a date format issue in the Pivot node
• Performance enhancement of connectivity to a Snowflake data source with SSO
• Fixed an error that occurred when using a column from a table in an outer join within an Oracle Direct Query model, specifically when the table contained CLOB-type columns
• Fixed an issue where a Variable was not set correctly
• Fixed an SQL type issue for Trino
• Fixed an inaccurate BigInt value in the Preview

• Fixed an issue where an unexpected array in an LLM response caused a model process to fail

Discover

      Queries

• Fixed an issue where Sort didn't work as expected for a chart that uses a dynamic dimension based on a Custom Column
• Fixed an issue where a filtered list based on a custom column didn't return the right items
• Fixed an issue where 'Total Function per Measure' Produced unexpected results
• Fixed an issue where the Explain feature wasn't working when using custom calculations and parameters
• Sub querying optimizations
• Fixed an issue where a Discover failed to open with a "Could not find column error"

• Fixed an issue where a Select Distinct failed on IMDB with a "The table does not exist in the database" error

• Fixed an issue where a nested Custom Column functioned as a filter only when "All" was selected

• Fixed an issue where an Action didn't pass the Context when using a Custom Column 

• Fixed a strToMember issue with CurrentMember functions

      Visualizations

• Fixed an issue where a data label was missing in a chart for a measure on the secondary axis when the primary axis measure contained a null value
• Fixed an issue where a Custom Column sort by global parameter didn't work
• Fixed a Range Slider issue in Firefox
• Fixed an issue where a Discover caused a blank screen upon opening 

Present

• Fixed an issue where Undo and Reset failed to revert cross-visual Target Interactions on Presents
• Fixed an issue where Reset and Undo didn't work on a grid after moving to a different slide and then back
• Fixed an issue where a Jump Action was not working when triggered from a chart that uses a Parameter
• Fixed a caching issue that prevented data from updating
• Fixed an issue where drilling down a multi-level hierarchy in a Present caused a "connection closed" error

• Fixed an issue where visualized content from Tabulate failed to load in present 

• Fixed an issue where a Present didn’t update after the first slicer change when multiple custom columns with different parameter values were used

• Fixed an issue where Smart Insights failed with a "Cannot read properties of undefined" error

Tabulate

• Fixed an issue where the XIRR formula didn't work in Tabulate

Publish

• Fixed an issue where a Schedule was no longer using the BW default parameters
• Fixed a page trigger issue when using model mapping
• Fixed an issue in Kubernetes environments where subscriptions with the 'Attach Content' option enabled failed to send with a 'No such file or directory' error 

Formulate

• Fixed an issue creating Custom Columns when metadata cache is off
• Fixed an issue where NextMember, Lags, and Leads functions returned NULL when a column was sorted as descending in the model 

Illustrate

• Fixed a Text alignment issue in a one-off illustration

Content Management

• Fixed an issue that was causing duplicated data sources to appear in the Data Source Changer after an import
• Fixed an issue where Pro users couldn't delete a folder they created

• Improved handling of content opening in cases where its data source has been deleted

• Fixed an issue where items created in Formulate were not appearing in the formula tree when creating a new item 

Printing / Export

• Fixed a scaling issue of a Dynamic text in a present table when printed to PPT
• Fixed an issue that caused printing to Excel to fail when using a BW data source
• Fixed an issue where a Custom Subtitle in a Publication was not reflected in the Excel Export
• Fixed a time format error on a measure that caused a print to Excel to fail
• Fixed an issue where a "Publication print click action" downloaded multiple files 

Admin

• Fixed an issue where a Provisioning job changed profiles regardless of the user's type

• Fixed an issue where the Region couldn't be changed when configuring a Google Gemini LLM provider

• Fixed an issue where the Maximum File Size setting was ignored

• Fixed an issue where "Download logs" for the In-Memory DB failed in a Linux environment where the default location was changed

• Fixed an issue that prevented certain filters in the Transaction Logs from working properly

Performance

• Fixed an IMDB connection issue that caused the IMDB server to become unresponsive

Mobile

• Fixed an issue where Labels were cut off when viewing a bar chart on an iPad
• Fixed an issue where clicking a link in a Discovery grid didn't work in a mobile browser

• Fixed an issue where the Toolbar in Analyze Further was cut off on the iPad 

Other

• Fixed an issue where Logs were not getting cleaned from the log folder
• Fixed an issue with Pyramid embed using React when installing in the root of the project
• Fixed an issue where "Not enough information" appeared in an embedded Present in the initial loading
• Fixed an issue where the app tabs order couldn't be changed on macOS
• Fixed an issue where Prometheus, deployed via Helm, failed due to a missing Content-Type header
• Fixed an issue where a "Can't run k8s pod monitor" error appeared in the System Logs for Kubernetes installations
• Fixed an issue where a  "Can't create process at schedule" error appeared in the System Logs until a restart of the pods

• Online help improvements

• Fixed an issue that was causing a "Cannot find an active runtime engine" error

Embedding with Pyramid allows developers to embed visuals, reports and dashboards from the Discover and Present modules into any application, while retaining all core functionality of the visualizations. In this guide, you will get a walkthrough on all areas that are important to understanding the embedding capability – use cases and key drivers to embedding, authentication, embedding API’s and typical embedding workflows. 

Learning Objectives

This guide, comprised of 11 videos, is geared towards Professional license holders, who have rights to embedding content outside of the Pyramid platform. Although JavaScript knowledge is not a requirement, it could be useful in understanding how embedding is implemented via the Pyramid Analytics website.

Please note there are two licensing options for embedding:

• Embed - Named: Embed interactive visuals, reports and dashboards from the Discover and Present functionality into any of the customer's website web application. Required for SSO. Does not include named authenticated users.

• Embed - Public: Embed interactive visuals, reports and dashboards from the Discover and Present functionality into any of the customer's website web application. Users accessing the embedded experience are not named/license users.

Take the guide here: 

Embedding Analytics with Pyramid

Let us know what you think!

Calculations are a key feature of the Pyramid Platform, offering powerful functionality across its modules. Depending on the specific use case, calculations can be created within individual modules or through Formulate. While this flexibility can seem complex, this guide will walk you through the basics, explaining how and where to use calculations in four comprehensive chapters.

Learning Objectives

The aim of this guide is to provide useful insights into:

• how the various Pyramid modules (or applications) support calculations

• how the calculation engine is applied in the various modules.

Note: Be aware this course focuses on basic principles rather than teaching Formulate. Users should refer to our Help Pages and our Courses on Formulate for deeper insights.

Take the course here: 

Data Concepts and Integrating Calculations in Pyramid

Let us know what you think!

The Use Case: Improving an existing dashboard

This course, designed for professional license holders, walks you through five key design principles to help you create dashboards that are both visually appealing and effective.

You'll be involved in every step of the process, with opportunities to think critically about how the dashboard provided in this course (image above) can be improved.

Learning Objectives

By the end of this course you will be better able to:

• spot design mistakes in your own dashboards
• use design principles to prevent and correct them
• create visually appealing dashboards ready to be shared with your team.

Note: Be aware this course focuses on basic design principles rather than teaching Formatting panel and Design ribbon functionality. Users are expected to be familiar with the Formatting panel in the Present module.

Take the course here: 

Basic Dashboard Design Principles for Pro Users

The dashboard can be found here: Starting Dashboard 

Let us know what you think!

In a recent Webinar, our own Joe Beaubien talked with Cris Byers from our Partner Mapbox about the potential of using GenBI in combination with their mapping service. See the video below.

Feel free to share this article (preferred) or the Youtube Video with your colleagues.

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

Let us know your feedback.

The Importance of Dyslexic-Friendly Fonts

After my Blogs on the general Management of Data Analytics Projects I would like to add another angle to your Business Analytics Project.

How to cater for users with Disabilities, its not a topic we usually think about when designing reporting, but its an important one for your users. My daughter has dyslexia. We're lucky; it was diagnosed early, and working with her schools, we've ensured that it does not affect her learning ability. She now thrives, and this is lovely to see. We deal with this in varying ways: she uses her iPad and MacBook to make notes (her handwriting can be poor), and when reading on her Kindle, we use a Dyslexic font, which was a game changer in reading. And this brought me to an important point for your reporting environment. 

Around 10% of the global population is affected by dyslexia.

Statistically, there are 3 men for every 1 woman diagnosed with dyslexia, but women tend to under report or better compensate for their difficulties. This in itself means that there are potentially many more millions who are affected and simply "get by". 

In the realm of data analytics, accessibility should be at the forefront of design—not only does it ensure inclusivity, but it also empowers all users to engage fully with the data. 

If at least 10% of your users could be affected, a significant number of your workforce could be at a disadvantage, potentially unable to interpret the data they're presented with ease. 

Traditional fonts often exacerbate these challenges, making it difficult for individuals with dyslexia to decode text efficiently. Dyslexic-friendly fonts are designed to mitigate these issues by enhancing letter distinction and optimising spacing.

By incorporating a dyslexic font into Pyramid Analytics, we can create an environment that supports all users, fostering confidence and a positive relationship with data analysis.

Here's how you might support that 10% of your organisation when it comes to enabling better consumption of analytics. 

Step One: Add OpenDyslexic to your Web-Browser, use Edge, and the Web Version. 

This is not perfect and is not the finished solution. If you are dyslexic and want most of the data within Pyramid to show in a Dyslexic-friendly font, then:

1. Use Edge. From what I've found, the extensions in Edge work better for accessibility tools. 
2. Download the Hyperbird extension. The link here is here. There are free and paid-for versions. I used the free version during my tests and it worked great. 
3. Enable it. 

What does it do? It ensures that the Measures and Dimensions panels appear in the OpenDyslexic font. This might be useful for those who use self-service. 

What does it not do? It doesn't work on the Drop Zones, Formatting Panels, or Ribbon. The visuals will adopt the font as dictated by the font from the Formatting Panel,or the Design Theme. 

Things to note: I've found that the Chrome extensions appear to be great options for general websites but are ineffective for Pyramid. 

Step Two: Setting Up a Dyslexic-Friendly Font in Pyramid Analytics.

The above is half the story. It allows professional and Analyst Users to use a dyslexia-friendly font when building new content. It doesn't change the font of the visual, though, so it's only half the solution.

To fix this, you can add a custom font to your Pyramid Analytics environment! 🎉

We're using OpenDyslexic, a free-to-use font that can be found here. The standard fonts are downloadable, but you'll want the Woff2 and Woff files, so use the links below. Thanks to OpenDyslexic for creating the font and making it available for free use. Feel free to make a donation to the creator, Abbie Gonzalez, here!

To do this, follow these steps:

1. Download these fonts from here, which you'll use below. 
2. Go to the "Admin Console". 
3. Click "Design".
4. Click "Custom Fonts".
5. Click "Add Custom Font". 
6. Enter the following details as your Custom Font, per the image below.
   1. Caption: Open-Dyslexic
   2. Font Family Name: Open-Dyslexic
   3. Fallback Font Style: Sans Serif
7. Upload the woff2 and woff files, as included above, for:
   1. Normal
   2. Bold
   3. Italic
   4. Bold-Italic
8. Note that the Bold and Bold Italic don't look any different from their Normal and Italic counterparts in the Preview. 
9. If you have associated tenants, then ensure to complete these sections also.
10. Apply. 

This is what the Custom Font settings will look like. 

That's it! You now have a custom font that your Professional and Analyst Users can use to build dyslexia-friendly solutions.  

Step Three: Create and use Theme Designs that include the new font.

If you wish to roll out dyslexia-friendly themes, you can create them as you would normally. Alternatively, at Hopton Analytics, we've created enhanced Themes based upon standard pre-existing ones that include dyslexia-friendly fonts.

You can download them here. 

That's it! What did we miss? What could we have said better?

What do you think we missed? We're planning on working on a series of colour-blind-friendly themes, too. But in the meantime, is there anything else we should or could have suggested above?

Do you have any ideas of anything else you'd like to see?

We'd love to hear from you. 

We hope you find the above useful from all of us at Hopton Analytics.  

Navigating Business Intelligence Adoption in Your Organisation

Ensure you have a Communication Plan

• Give Regular Updates on the progress consistently. As discussed above, share success stories, highlight milestones, and address any challenges transparently and quickly.
  • Share Case Studies as covered above.
  • Deal with setbacks quickly and efficiently. If there’s a problem with data or the UX, tackle them above everything else.
• Measure your Success
  This is an odd one, as it’s common sense, right? You’re running a BI project, and yet, if you’re honest, I wonder how many BI projects measured their success via KPIs and Metrics. Not many would be my guess! These are essential, though, so consider:
  • KPIs and Metrics: Define the OKRs and KPIs for BI adoption for your project. This could be usage, it could be the number of reports and dashboards produced and the number of legacy reports/spreadsheets that have been removed from service. A must is data quality, too.
  • Feedback Loops: Collect feedback from users. Are they finding value? Are there pain points? Use this input to refine the plan. Add Surveys to your reports and dashboards and encourage their use. Learn from the points made.

Do you agree with the above? What is your experience?

Hoping you’ve found our series of posts on Navigating Business Intelligence Adoption in Your Organisation useful.

If you want to chat this through, get in touch! I’ve love the opportunity to speak with you.

All the best, Simon.

Here are links to all the parts:

   Empowering the Executive Sponsor
   Fostering a Culture of Data Literacy
   Make Data Relatable through Story Telling
   Craft a Comprehensive Delivery Plan
   Ensure you have a Communication Plan and Measure Your Success 

This Guest Blog has been written by our Community Member and Consultant Simon . For questions, please leave a comment below.

Navigating Business Intelligence Adoption in Your Organisation

A well-structured delivery plan is the backbone of successful BI adoption. It ensures that BI solutions are implemented and embraced by all stakeholders. Such a plan aligns BI initiatives with the organisation’s overall goals, bridging the gap between technical implementation and business impact. By involving all areas of the business, the plan ensures that no department is left behind: whether it’s finance, sales & marketing, operations, or HR, everyone has a role to play in your BI journey.

• Build a Phased Roll-Out Strategy
  • Start small and scale gradually, rather than a big-bang approach. Break down the BI implementation into manageable phases. Begin with a pilot project or a specific department and specific use cases. You must ensure you deliver the first one before starting on the second.
  • Iterate and Learn throughout the project. Each phase provides valuable insights. Use feedback to refine subsequent releases. For example, start with a basic dashboard for sales analytics, then expand to inventory management and customer service. The important thing here is you iterate. You build. Then, you build some more.
• Stakeholder Engagement
  • Identify champions and have executive sponsorship. Appoint BI champions within each department or team. Ensure these champions are trained, buy into the vision, and want the project to succeed. These advocates will then drive adoption, answer questions, and encourage colleagues to use the tools, speak up with ideas, and ask for more support and insights as they need.
  • Training and Support to ensure that end-users receive proper training. Offer ongoing support through workshops, webinars, and documentation. If your reports and dashboards are well-designed, training should not take long. So, organize “lunch and learn” sessions to train on the tool, but ensure there’s always the ability for the users to ask for help.

Do you agree with the above? What is your experience?

In the final part of this five part blog post, we'll discuss how you ensure you have a Communication Plan and measure Your Success

This Guest Blog has been written by our Community Member and Consultant Simon . For questions, please leave a comment below.

Full Topic list

   Empowering the Executive Sponsor
   Fostering a Culture of Data Literacy
   Make Data Relatable through Story Telling
   Craft a Comprehensive Delivery Plan
   Ensure you have a Communication Plan and Measure Your Success

Navigating Business Intelligence Adoption in Your Organisation

Data becomes more relatable when we weave it into compelling narratives or stories. This makes it relevant to your organisation, and members of your organisation are more likely to buy into it BI if they can see what actual impact it has to day-to-day working lives. Consider how you might make use of this kind of approach:

• Hold Regular “Data Day” Events where different departments showcase how BI tools have transformed their work. This helps your team as, instead of looking at abstract theories, your team focus on real-world scenarios.
  • Share Success Stories that showcase tangible benefits. Highlight how BI has directly influenced revenue growth, cost savings, or operational efficiency. For example, perhaps a team or individual has who developed a predictive model to optimise supply chain logistics. Recognise their achievements and explain the tangible benefits to the organisation (e.g., saving $$$, reducing waste, reducing carbon).
  • Encourage lively discussions during these sessions, where attendees can ask questions, share their experiences, and discuss ideas for improvement.
  • It can just as easily be “Data & Donut Hours” if your organisation is smaller or time pressures don’t allow for more time.
  • Keep it real. What will make this succeed is other people inside your organisation showing how simple things such as dashboards or reports have had a real and positive impact on how they work.
• Create and share internal case studies highlighting specific challenges that your BI project solves.
  • Shout about what has been achieved. Remember that when working in Data and BI, the production of a “report” can seem like “just a report”. But most dashboards and reports – when designed well – save time, help customers, help your business and bottom line, and make your lives easier. Shout about this!
  • Run an Awards Program that celebrates teams or individuals who use BI in novel ways. Acknowledge their contributions during company-wide meetings or through internal newsletters.

Do you agree with the above? What is your experience?

In our next blog post, we'll discuss how to Craft a Comprehensive Delivery Plan

This Guest Blog has been written by our Community Member and Consultant Simon . For questions, please leave a comment below.

Full Topic list

   Empowering the Executive Sponsor
   Fostering a Culture of Data Literacy
   Make Data Relatable through Story Telling
   Craft a Comprehensive Delivery Plan
   Ensure you have a Communication Plan and Measure Your Success 

Navigating Business Intelligence Adoption in Your Organisation

Adopting Business Intelligence is more than the mere assortment of tools; it’s about nurturing an environment where data is esteemed and leveraged for decision-making. There are plenty of ways that this can be achieved, but here are some of the key ones:

• Organize workshops and training sessions. These help demystify BI technology and principles, making them accessible to all. Consider the following ideas:
  • Schedule regular, interactive workshops that cater to different expertise levels, from beginners to advanced users. Don’t just schedule “must attend events” either; create drop-in sessions for people who have a question or two, plus encourage users to book one-on-one private sessions if they have questions they don’t want to ask to a wider audience.
  • Use real-life scenarios to demonstrate the impact of BI on decision-making processes.
  • Provide hands-on experience with BI tools to build confidence and competence.
• Create a Centre of Excellence or Community of Practice. To build a supportive network where individuals can share insights, challenges, and triumphs related to BI. Make this team effective by organising:
  • Workshops and Training Sessions to enhance BI skills across the organisation. These sessions can cover topics like using company-wide templates, data visualisation, report creation, and advanced analytics techniques, even internal design concepts.
  • Mentorship Programmes: Pair your experienced data analysts (either internally or from your BI partner) with less seasoned members of the organisation to help with knowledge transfer. Encourage mentorship relationships where insights flow freely, for example, where seasoned analysts guide junior team members through complex data modelling techniques, ensuring everyone benefits from collective expertise.

Do you agree with the above? What is your experience?

In our next blog post, we'll discuss how to make Data Relatable through Story Telling

This Guest Blog has been written by our Community Member and Consultant Simon . For questions, please leave a comment below.

Full Topic list

   Empowering the Executive Sponsor
   Fostering a Culture of Data Literacy
   Make Data Relatable through Story Telling
   Craft a Comprehensive Delivery Plan
   Ensure you have a Communication Plan and Measure Your Success 

Navigating Business Intelligence Adoption in Your Organisation

The journey towards successful BI implementation often starts at the top. Having an Executive Sponsor is not just beneficial, it's crucial. At a board level, this individual can champion your chosen application and drive its adoption across the organisation.

• Address your sponsor’s needs first. This might seem counter-intuitive and against the principle of adding value where it’s needed first. However, addressing the executive sponsor or board's requirements in the first instance earns their buy-in and turns them into champions for the cause. Their enthusiasm and support can trickle down, creating a ripple effect throughout the company.
• Create an Executive Dashboard. If done successfully, the executive team will want and expect similar dashboards in their teams and departments. This serves a dual purpose:
  • This will provide immediate value to the executive team by giving them insights into critical business metrics.
  • It’ll serve as a powerful demonstration of the potential impact of BI across the organisation.
• Don’t Forget the big picture. The sponsors need to be reminded of the big picture so that the project delivers on their specific wants and needs and to see beyond their own report, dashboard, or department – looking at the project as a whole. This will help drive adoption in a wider sense than just one team.

Do you agree with the above? What is your experience?

In our next blog post, we'll discuss the importance of Fostering a Culture of Data Literacy. 

This Guest Blog has been written by our Community Member and Consultant Simon . For questions, please leave a comment below.

Full Topic list

   Empowering the Executive Sponsor
   Fostering a Culture of Data Literacy
   Make Data Relatable through Story Telling
   Craft a Comprehensive Delivery Plan
   Ensure you have a Communication Plan and Measure Your Success 

Did you know that the Pyramid Learning Hub not only offers all the functional courses to get you certified in Pyramid, but we also offer a growing variety of Use Cases that show you how to build a sample Report from scratch. They combine the necessary know how across all Modules that you need to complete the task.

Use Case: Predictive Modeling

In about two hour we guide you through the predictive modeling functions of Pyramid. So what is the task?

The background

For this Use Case, let’s imagine you are a data analyst/scientist working for a sports car manufacturer. A brand new sports car has recently been manufactured and the General Manager of Sales has assigned you a task.

You will utilize Pyramid and its Decision Intelligence capabilities to predict which of your previous customers will buy this brand-new model.

The challenge

The main objective of this tutorial will be to predict a category, a binary variable 'Yes / No' to answer the question 'will customer x buy this new car?' 

 This training is based on Pyramid 2020.17. You can find the corresponding Data Models for your training in the Explore Pyramid Environment

What you will learn in this Use Case:

• To build, train and test a Logistic Regression model.
• To build a Confusion Matrix.
• To analyse the Confusion Matrix results to calculate the predictive accuracy of your model.

To sign up for this Use Case go to the Learning Hub (LMS Subscription required)

https://learn.pyramidanalytics.com/courses/predictive-modeling-logistic-regression/

If you have questions about the Learning Hub or suggestions for this Use Case, reach out to me: Rory

Did you know that the Pyramid Learning Hub not only offers all the functional courses to get you certified in Pyramid, but we also offer a growing variety of Use Cases that show you how to build a sample Report from scratch. They combine the necessary know how across all Modules that you need to complete the task.

Use Case: Finance Business Analyst:

In less than one hour we guide you how to build a Sales Forecast. So what is the task?

The background

As a finance analyst it is critical to be able to generate reliable forecasts of sales for different segments of the business to guide decision making.  These forecasts must be kept up to date on a daily basis, as new data becomes available it needs to provide guidance for each segment of the business.

The challenge

Generating daily updated forecasts that can be customized to different user needs is a time-consuming process that can introduce errors and inconsistency leading to sub-optimal decision making.

The solution

In this course, we will show you how to create a dynamic report that will let users generate and visualize sales forecasts for various regional and promotional views of all sales. Just Click on the Picture to jump to the course.

This training is based on Pyramid 2020.17. You can find the corresponding Data Models for your training in the Explore Pyramid Environment

What you will learn in this Use Case:

• how to create a line chart in Discover utilising a dynamic list
• adding forecasts to charts and how to extend the date range
• comparing forecast sales with actual sales
• how to use region filters with group options

To sign up for this Use Case go to the Learning Hub (LMS Subscription required)

https://learn.pyramidanalytics.com/courses/finance-business-analyst/

If you have questions about the Learning Hub or suggestions for this Use Case, reach out to me: Rory

Read more on our Main Webpage: https://www.pyramidanalytics.com/blog/super-bowl-dashboard/

You will be able to directly interact with the data on the page as they have been embedded Live. Ask the chat bot to tell you more about any details. Play with the Data and leave a screenshot of your findings in the comments below!

BUT as we are the community, there is more ;-) if you are a Member and Part of the Explore Pyramid Crew. You can access the Environment and see how it was done. The Dashboards are part of the Challenge Folders. We even include the Datasets so you can build your own Super Bowl Dashboard.

 P.S. Will Taylor Swift make it in time? Leave your guesses below ;-)

Did you know that the Pyramid Learning Hub not only offers all the functional courses to get you certified in Pyramid, but we also offer a growing variety of Use Cases that show you how to build a sample Report from scratch. They combine the necessary know how across all Modules that you need to complete the task.

Use Case: Retail Business Analyst:

In less than one hour we guide you how to build a Sales and Product Report in Pyramid. So what is the task?

As a business analyst in the retail sector, do you want to know what products/brands contribute the majority of your sales by region or operation?

This course we will show you how to create a Discover report that can display this breakdown of sales contributions.

This training is based on Pyramid 2020. You can find the corresponding Data Models for your training in the

Explore Pyramid Environment

What you will learn in this Use Case:

• how to create a basic sales report in Discover (sales by product)
• adding date and group filters with a pareto context calculation
• how to create a combo chart incorporating the pareto calculation
• to duplicate the report and to then add a trellis chart to the duplication

To sign up for this Use Case go to the Learning Hub (LMS Subscription required)

https://learn.pyramidanalytics.com/courses/retail-business-analyst/

If you have questions about the Learning Hub or suggestions for this Use Case, reach out to me: Rory

 The Data Shark Podcast by Omri Kohl

Tune in and subscribe to the new Podcast by our co-founder and CEO Omri Kohl.

The BI space can be super-challenging to comprehend at first.

This is exactly why Omri Kohl, our CEO and co-founder, decided to launch his very first podcast!

His talk covers the latest industry trends, common mistakes, and his own entrepreneurial journey.

Even if your role has nothing to do with analytics, learning this subject could help you set the right KPIs and understand how to measure them.

Interested to see what Omri has to say? Listen, download and subscribe today:

• Apple podcasts: https://podcasts.apple.com/il/podcast/the-data-shark-podcast/id1720742534
• Spotify: https://open.spotify.com/show/5ezq8GVTCE3xQDlc3qiKiJ
• Youtube: https://www.youtube.com/@TheDataShark

Make sure you hit the subscribe button because there is a lot more to come in the next few weeks.

Episode List

Episode 9

On Apple: My Investor came to the show! - Natalie Refuah

On Spotify: My Investor came to the show! Natalie Refuah

On Youtube: My Investor came to the show! - Natalie Refuah

Episode 8

On Apple: How will AI Revolutionize Our Decision-Making Process? - Avi Perez

On Spotify: How will AI Revolutionize Our Decision-Making Process? - Avi Perez

On Youtube: How will AI Revolutionize Our Decision-Making Process? - Avi Perez

Episode 7

On Apple: Building a Sales Engine with Analytics - Brian McCormac

On Spotify: Building a Sales Engine with Analytics- Brian McCormac

On Youtube: Building a Sales Engine with Analytics- Brian McCormac

Episode 6

On Apple: What Made BI Mission Critical? - Miki Lumnitz

On Spotify: What Made BI Mission Critical? - Miki Lumnitz

On Youtube: What Made BI Mission Critical? - Miki Lumnitz

Episode 5

On Apple: The investor perspective - Yoav Tzruya

On Spotify: The investor perspective - Yoav Tzruya

On Youtube: The investor perspective - Yoav Tzruya

Episode 4

On Apple: Building a winning Team - Roi Barak

On Spotify: Building a winning Team - Roi Barak

On Youtube: Building a winning Team - Roi Barak

Episode 3

On Apple: Changing the Game - Avi Perez

On Spotify: Changing the Game - Avi Perez

On Youtube: Changing the Game - Avi Perez

Episode 2

On Apple: The ROI of BI Projects

On Spotify: The ROI of BI Projects 

On Youtube: The ROI of BI Projects

Episode 1

On Apple: Building a Data Driven Culture

On Spotify: Building a Data Driven Culture 

On Youtube: Building a Data Driven Culture

Leave us a review in Apple, Spotify and Youtube and a comment below.

Click here to see the listing of all new features added to 2023.10.

Fixes in Pyramid 2023.10

Model

• Fixed an issue where using BigQuery as a destination was not possible when set to utilize a service account
• Fixed an issue where a calculated column incorrectly returned a Null value
• Fixed an issue where an Azure Databricks source did not show the list of databases
• Fixed an issue where the properties panel for Multi Files sources failed to refresh upon navigating through various Multi Files sources
• Fixed an issue where users couldn't change the column data types for Multi Files source
• Fixed an issue where users could not edit Calculated Columns in a model
• Fixed an issue where running a query in the Master Flow returned a “No authorization for performing operation” error
• Fixed an issue where the "Time to keep model processing logs" setting in a schedule was set by default to Always instead of System Default
• Fixed an issue where changing the data source on a direct model resulted in the removal of tables from the model
• Fixed an issue setting a Variable value to Null
• Improved multi-measure unique naming logic
• Fixed snapshot isolation errors when using a for loop to connect to an API in model
• Fixed an issue where the initial column in a table displayed with quotation marks when utilizing a CSV source
• Fixed an issue where a variable could not be validated without validating the entire flow
• Fixed an issue where a column data type was erroneously set to Date instead of DateTime
• Fixed an issue wherein milliseconds were inadvertently omitted when saving a DateTime column to a model destination
• Fixed an issue where strings were truncated to 80 length when creating a model using a ClickHouse source to an IMDB target
• Fixed an issue where Snowflake Date Values were showing one day before the actual date

Discover

    Queries

• Fixed an issue where a Discovery that was using a PCH did not retrieve data as expected
• Fixed an issue where a report that was opened from an action was filtered incorrectly
• Fixed an issue where Auto Discover did not work for an IMDB or MSSQL models with Time Intelligence
• Fixed an issue where a "The PQL function 'EXIST' is not supported" error message appeared after using cascading filters
• Fixed a cascading slicer time-out issue
• Fixed an issue where using Focus on a member in a grid returned no results
• Fixed an issue where an error message appeared when using an aggregated table in a report
• Fixed an issue where custom ordering calculations with parameters no longer allowed parameters to impact the results
• Fixed an issue where the YTD function in a Date Calendar filter did not return the current date
• Fixed an issue where a Last Child measure returned no results
• Fixed an issue where the Row Limit that was configured in the report did not apply as expected
• Fixed an issue that could lead to an inconsistent result limit between the initial run of the report and subsequent runs
• Fixed an issue where Multiple infraEntryPoint were generated when hovering over a member

    Visuals

• Fixed an issue saving small decimal values in the Conditional formatting band fields
• Fixed an issue where adding certain dimensions and measures to a report resulted in a blank screen
• Fixed an issue where eliminating a specific data point resulted in the removal of more than the selected item
• Fixed an issue where Sort from Analyze Further had failed
• Fixed an issue where Conditional Formatting Data bars did not appear for negative values when using the % Of Max algorithm
• Fixed incorrect order of values in the Legend when using the Hebrew UI language
• Fixed an issue where the context menu did not appear after right-clicking a grid cell with a basic user in embedded content
• Fixed an issue where long member names in Hebrew were cut off from the right instead of from the left
• Fixed an issue where text in a grid was wrapped although Wrap Text was off
• Fixed an issue where a blank screen appeared after right-clicking a row header in a grid
• Fixed an issue where labels were cut off in a Tornado chart
• Fixed an issue where collapsing an item in a grid did not work as expected when the query was using a parameter
• Fixed an issue where the Chart Total Labels ignored the culture settings of the underlying data source
• Implemented a new "Auto Fit Labels" formatting setting to offer improved customization options

Present

• Fixed an issue where clicking the Reset button did not remove the filter selection from a visual
• Fixed an issue where swapping an attribute with a hierarchy in a Present resulted in an error
• Fixed an issue where the context menu did not appear after right-clicking a grid when in full-screen mode
• Fixed an issue where an Illustration Tooltip that is based on a dynamic text, didn't update when the underlying discovery was filtered
• Fixed an issue where a filter kept working after the column it was based on was unselected in the model
• Fixed an issue where the Reset button in runtime did not refresh the data as expected
• Fixed an issue where an Illustration, based on a measure with applied Overlay, was not properly filtered in a Present
• Fixed a presentation-related issue leading to out-of-memory errors in the InMemory server
• Fixed an issue where users couldn't edit bookmarks if the content was accessed via a URL link
• Fixed an issue where clicking on a bubble map did not update the visuals when interaction was set
• Fixed an issue where selecting "All" in a cascading filter caused a query to fail
• Improved legend scaling
• Optimized loading time of presentations
• Fixed a situation where visuals within a slide were incorrectly filtered when the filters on the slide were disabled
• Fixed an issue where Pre Query Filters did not refresh the visual
• Fixed an issue where a "Not enough information" message appeared in a mini tab after manually switching mini tabs
• Fixed an issue where interactions appeared to be pointing to incorrect slicers when scrolling

Tabulate

• Fixed an issue where the browser tab froze after adding a Visual Area
• Fixed an issue where the browser froze after adding a new formula in a new sheet of an imported Excel
• Fixed a filter-related issue that was causing a failure in loading a Tabulate
• Fixed an issue where SLICERCAPTION in a tabulation showed an incorrect value in a present
• Fixed a save issue when a cell referenced a pyramid formula

Publish/Subscriptions

• Fixed an issue where a report with a dynamic date filter did not update as expected
• Fixed an issue where scheduled publications did not run intermittently
• Fixed an issue where publications and subscriptions didn't run after upgrading to 2023.01 until they were resaved
• Fixed an issue where a Schedule did not have the Add Link-back option under Output settings

Formulate

• Fixed an issue when using a BW source where a list that used a global parameter in a Contains filter had failed
• Fixed an issue where formulations with member/parameter caption in the Dynamic Caption field had failed to run
• Fixed an issue where a formula didn't return the caption of a hierarchy current member
• Fixed an issue where the templates for the StrToMember() and StrToSet() functions in formulations based on OLAP cubes were incorrect
• Fixed an issue where CurrentMember() and other offset functions have failed for a Custom Column
• Improved performance of some formulations that use constant functions
• Fixed the issue with Number Formats templates displaying a single quote instead of double quotes when using an SQL data source
• Fixed an issue where a comment in script mode caused the formulation to fail

Illustrate

• Fixed an issue where a duplicated illustration did not run

Content Management

• Fixed an issue where items could not be deleted or purged
• Fixed an issue where using Cut and then Replace on a different folder,  did not remove the content from the source folder
• Fixed an issue where a blank screen occurred after opening a discovery from Favorites
• Fixed an issue where the contents of workgroup folders were not visible to viewers
• Fixed an issue where replacing a model definition that was owned by a different user had failed
• Fixed an issue where content that was moved/copied did not get the correct roles
• Fixed an issue where exporting a discovery caused an RTE crash
• Fixed an issue where replacing a Model timed out and failed
• Improved layout of the "Delete Contentions on Shared Item" panel
• Fixed an issue where a Viewer user was able to access a folder but could not access its child folders
• Fixed an issue where folders appeared in a reversed alphabetical order

Printing/Export

• Fixed an issue where printing a Present with a measure parameter had failed
• Fixed a blank page issue when printing a present or publication that contained a tabulate
• Fixed an issue where legend colors were incorrect on a printed publication
• Fixed an issue where a measure list didn't print to CSV/EXCEL when in raw/tabular grid
• Fixed an issue where a Matrix Grid with a large number of visuals did not print correctly in Present
• Fixed an issue where a measure that is formatted as Currency did not show the currency symbol when printing to Excel
• Fixed an issue where exporting a discovery consisting solely of measures to Excel resulted in a format that was not compatible with pandas in Python
• Fixed an issue where a large grid that was exported to a PDF was not properly formatted
• Fixed an issue where, if the caption of a measure was modified, printing from embed displayed the original caption instead of the updated one
• Fixed an issue where a visual appeared blank in a PDF print of a publication
• Fixed an issue where a printable slicer that had all items selected showed a blank selection in the print

Admin

• Fixed an issue where Azure Blob Storage data sources did not appear in the Source Manager
• Fixed a geojson parsing issue which was causing custom maps imports to fail
• Fixed an issue where the Test button in the Add Data Source panel consistently displayed a Success message when utilizing "Service Account" as the Authentication Method
• Fixed an issue where SSH data sources did not show up in the Source Manager
• Added support for installation of optional features of a Python package
• Fixed an issue where the System Schedule failed to open
• Fixed an issue where the Task Manager did not show error messages
• Fixed an issue connecting to S3 storage
• Fixed an issue where a server statistics retrieval problem triggered an error message popup in the admin console
• Fixed an issue where a new conversation template lacked a functional link for the collaboration
• Fixed an issue where Security tables were not visible in the 'Member Security' tab under Source Manager within Model Settings
• Fixed an issue where the Server Instances screen was cut off in some resolutions
• Fixed an issue where the "Remove" button erroneously appeared when selecting an unrecognized database in the Databases tab under Data > Source Manager
• Fixed an issue where Additional Parameters were not sent properly on Snowflake connection
• Fixed an issue where additional parameters were ignored for AWS Athena connections
• Fixed an issue where Weka files didn't get deleted automatically
• Fixed an issue where connecting to an OLAP cube failed due to a Folder Overlay problem

Security

• Updates to various 3rd party libraries for vulnerabilities

API

• Fixed an issue where /API2/content/getFolderItems did not bring back deleted items

Mobile

• Fixed an error when using a Jump action on the iPad

Install/upgrade

• Fixed an issue which was causing the upgrade from 2020 to 2023.01 to fail
• Fixed an issue where adding services on upgrade, the added services did not start
• When deploying Kubernetes, the metrics server is now only accessible internally
• Fixed an issue where Python and R did not install on the AI server if other services were already installed

Login

• Improved login performance
• Fixed an issue that was causing the login process to fail

Other

• Online help updated
• Fixed an issue where the IMDB stopped running at on a daily basis at midnight UTC
• Fixed an issue that was causing the client browser to crash after working in Pyramid for a while
• Fixed an issue that was causing PROM errors to appear in the system logs 

If you have an instance of Pyramid that was initially installed as version 2020.28 or later (including 2023), or that uses an MS SQL Server or PostgreSQL repository, you can ignore this exercise.

Before upgrading to Pyramid 2023.10 it is important to first run the below command as an Oracle admin user on the Pyramid Oracle repository database. This is a one time adjustment to the Oracle repository database.

GRANT CREATE VIEW TO <PyramidReproName>;

Replace "<PyramidReproName>" with the name of your Pyramid repository database.

Once the above has been done, confirm that the user now has rights to create views. After which the upgrade to Pyramid 2023.10 can be performed.

Click here to see the issues fixed in 2023.10. 

Discover

Querying

Visuals

Content Manager

Present

Formulate

Model

Tabulate

Solve

Printing

Embedding

Admin

Admin Hub

General Infrastructure

K8s Infrastructure

APIs

Discover

Data Labels for 100% Stacked Bar need % of Total Option like Pie Charts
CSV delimiter type selection in discover export
Searching for Multiple Members in a Single Slicer Search
Ability to include a custom reference line on a chart
Actions not to include unary operator hierarchy in destination
List builder from Frontend
Easy Tagging using the @sign

Present

Present Print Output Not Showing Parameters
Slide Jump Actions enhancements
Next slide action
Export with Parameters/Slicers shown
List builder from frontend

Tabulate

Tabulate Slicer not returning data
Remove Tabulate Grid Lines
 

Model

Add ability to authenticate with BigQuery using a Service Account
Change the owner of model schedules when pro user is deleted
Add table name when hovering on tile

Administration

Better Handling of the Print Button

sin  Back to Pyramid 2023.01 release announcement

  This Blog has been written by Debora Veh from our Partner Prodato

Applying calculations correctly in Pyramid Analytics.

The Pyramid Decision Intelligence platform combines Data Preparation, Business Analytics and Data Science in a single Data and Analytics environment. As a partner of Pyramid Analytics, we would like to present tips and tricks especially for new users to make the start with this platform as easy as possible.

Many will have already noticed that the seemingly same calculation can produce different results. But why is that, and which result is the correct one? What is the difference between Row-Level Calculation and Aggregations? And how are they implemented in Pyramid Analytics?

In this article, we will first explain the terms and then illustrate them with a use case example in Pyramid Analytics.

When do you use Row-Level Calculation?

Row-Level Calculation is a row-based calculation. All calculations are first performed at the row level and then aggregated afterwards, just like normal fields. Thus, the calculation is performed for each row in the data. Additionally, merging (row calculation) fields is more performant than splitting fields.

Use Case:

If you want to calculate sales, i.e., Price * Quantity, you calculate this at row level. Row level is important here to consider different prices for different products. In our example, the respective prices for different bicycles and bicycle accessories are multiplied by the number of sold items.

In Pyramid Analytics, a Row-Level Calculation is created either in the Model module via Calculated Columns or in the Formulate module. In Model, a new column is generated for the data and the result is calculated row by row. In Formulate, an aggregation block such as "Sum" is selected. Here the row level, for example an ID, is selected as a list and the desired calculation as data point.

When do you use Aggregations?

Aggregates are column-based calculations. The values are first aggregated, and then the calculation is performed on the aggregated result. It should be noted that aggregations can be very time consuming when dealing with large amounts of data. Aggregates must not be created in Model but should be created in Formulate or in Discover. Some values cannot be summed up, e.g., profit margin, otherwise they lose their meaningfulness.

Use Case:

Sums are used to calculate the share of profit in sales, i.e., profitability. Sums are also used to calculate profitability, Gross profit / Net sales * 100. Here, the total profit, of all products sold, is compared to the total net sales. This results in a percentage value; a subsequent summation would distorte the significance.

The choice of the respective calculation method is crucial for the informative value of the key figures independent of the platform.

Examples of calculations:

In the following, the implementation in Pyramid Analytics is explained using the presented use cases.

Row-Level Calculation in Model:

For a Row-Level Calculation, SUM ([Measure A] *[Measure B]) is calculated row by row in Model and then aggregated:

Row-Level in Formulate:

The same calculation looks slightly different in Formulate.

Two steps are necessary:

1. Create the desired calculation and save it as "Formulate" in our case. Price*Quantity

   

2. Step 2: Select "Sum" under Aggregation Blocks, then select a list, in our case TransactionsID, to include the row base.

   

   
   For the Data Point, the calculation created above can now be selected. Individually created measures can be found under the folder symbol "FX". It opens below "My Content". All formulas based on the selected model are displayed here. 

   

Aggregate:

With aggregates, the columns are first aggregated individually via Formulate and in the second step the calculation [{SUM([Measure A])} - {SUM([Measure B])}] / {SUM([Measure A])} is executed:

This formula calculates profitability: {(SUM (Sales)) -(SUM(Expenses))} / (SUM(Sales)). However, you can also calculate it directly in Discover:  

The formula above can also be easily replicated via Discover by selecting the two measures and calculating a new measure.

If you would aggregate sales instead of using row level, you would get useless results. Here, all prices would be added up and then multiplied by the number. The result is thus distorted since no distinction is made between inexpensive and more expensive goods. On the other hand, the calculation for profitability by using Row-Level Calculation could reach a total of over 100%, since the totals are not taken as a basis for 100%.

In the following figure, the green box shows the results calculated at Row-Level. The red box on the right side shows the aggregate example. Here, the dark blue "Grand Totals" row is the crucial one. The two Row-Level calculations include the sum of the lower rows, while the aggregation also adds up to a meaningful percentage, rather than adding up the percentages underneath.

As you can see, the Totals differ from each other, so you need to think carefully beforehand about which calculation type is the correct one for your use case.

In Sales_Model and Sales_Formulate_Rowlevel (green box) the individual transactions are calculated:

(Price A* Quantity A + Price B *Quantity B + Price C * Quantity C ... ).

In Sales-Expenses/Sales (red box) all transactions are summarized:

[(Sales A+ Sales B+ Sales C) - (Expenses A + Expenses B + Expenses C)] /( Sales A+ Sales B+ Sales C).

For clarity, here is a snippet of the individual transactions:

The "Grand Total" row is identical to the data in the previous screenshot, but this time the data has been displayed at a very small granularity, at the transaction level, so you can now clearly see the difference. While the "Sales_Model" columns reflect the respective calculation (Price* Quantity), the results for "Sales_Formulate_Rowlevel" are different to this. Only the totals for each row are shown, because the TransactionID was used to calculate the formula. The percentage calculation in the right column "Sales-Expenses/Sales" now shows the profitability for each row, please notice that the total value still does not exceed 100%.

Attention: On transaction level different numbers are calculated, only the totals do not differ for Row-level Calculation in Pyramid Analytics! If the same dimension, which was used for calculation in Formulate, is used in the grid, Sales_Formulate_Rowlevel will not show the desired level, but the total sum. This behavior may be desired for certain applications, but you should be aware of this behavior in advance. Aggregate behaves as expected regardless of granularity.

Because of the behavior shown above, it may be advisable to do your row-level calculations in Model.

Conclusion:

The choice of the particular calculation method makes a big difference in the results. This can be the difference between useful and useless results. Row-based calculations are implemented in Pyramid Analytics in "Model" via "Calculated Columns", or via Formulate via Aggregation Blocks. Aggregates, on the other hand, are calculated via the "Formulate" or "Discover" modules. The choice of calculations should be carefully considered to suit the intended use and to always get the right result.

Row-Level Calculation is used when the granular level is important for the calculation as in the calculation of sales. Aggregates are used to calculate for business metrics such as profitability or return on investment.

Ongoing Support for Pyramid 2020

A new service pack (2020.28) for the last version of the platform (Pyramid 2020) has been released in tandem with the release of Pyramid 2023. It contains fixes for most customer issues resolved in 2023 (as listed below).

2020.28 will allow customers to remain with the older platform until they have had time to test and deploy 2023.  The release of new service packs for 2020 will continue until the end of 2023. 

Details on the release of 2020.28 can be found here.

Fixes in Pyramid 2023

Model

• Fixed a visual issue where relationships disappeared after processing a model
• Fixed an issue where the Model failed to retrieve the table list of an Oracle data source
• Added "Show More" and "Copy to Clipboard" buttons to error messages that appear in the SQL Editor
• Fixed an issue in the master flow where links between nodes in the For Each loop got removed after moving them
• Fixed an issue where a For Each loop stopped running after 1000 iterations
• Fixed an issue that was causing a hierarchy order to change
• Fixed a "Cannot read properties" error when connecting to Microsoft Access data source
• Fixed an issue where it was not possible to run a Custom Query on a Microsoft Access File
• Fixed an issue where a Python script node error did not present a clear error message: AttributeError: 'NoneType' object has no attribute 'shape' changed to "outputDF is not a data frame"
• Fixed an issue where an Azure Blob source, only some of the files appeared in the Select a File dropdown
• Fixed an issue where Pyramid tried creating unique indexes when processing a direct query with Azure Synapse
• Fixed a mapping error when using Unpivot when the UI language is set to Hebrew
• Fixed an issue where elements that were dragged into a large data flow appeared in the wrong location when using the Hebrew UI language
• Fixed an issue where Table Preview in the data flow on a BigQuery DB was using select *
• Fixed an IRDB paging issue that caused a specific query to return inconsistent results
• Fixed an issue where there was no spinner after opening a data flow and clicking on a select node, indicating that the Preview is running
• Fixed an issue where a message saying "The process is queued for execution" appeared in the Progress panel, although the model was not run
• Fixed an issue where Pyramid tried to create a new database, although Use Existing Database was selected in the Snowflake Target node
• Fixed an issue where processing a model based on BigQuery which was using OAuth had failed with a "OAuth2Credentials instance does not support refreshing the access token" error
• Fixed a "Table does not exist" error when running a data model that writes to BigQuery
• Fixed an issue where incorrect roles were assigned to a new/imported model
• Fixed an OutOfMemoryException error that was thrown although there was sufficient available memory

Discover

• Queries

  • Fixed an issue where  a query failed when using two Parent Child hierarchies and an AVG formula in a report
  • Fixed an issue where a Search in a global parameter filter did not work
  • Fixed an issue where a report appeared blank after a change to a combined elements filter
  • Quick sort by values is now disabled when the cross-axis is empty
  • Fixed an issue where using Change Caption on a BW attribute caused the measure format to change
  • Fixed multiple cache issues
  • Fixed an issue where using Sub Query Mode with Explicit Lists caused an MDX error
  • Fixed an issue where a Sort created duplicate Rows
  • Fixed an issue where the 'Hide query error messages from non-admin users' setting didn't work for reports that were already opened
  • Fixed an issue that was causing a report to fail when it was using a Parent-Child Hierarchy with an unreachable circular reference between elements
  • Fixed an MDX error when using an MTD calculation on SAP BW
  • Fixed an issue where Grand Totals were calculated incorrectly in an OLAP cube
  • Fixed an issue where a discover failed to run with a "failed run SQL query in model source" error when loaded

• Visualizations

  • Fixed an issue where a line chart was cut off when the chart had a zero value and Auto Scale Bounds
  • Fixed an issue where the Custom Y-axis Scale bounds did not allow negative values
  • Fixed an issue where a report did not show results when moving between visuals unless Autorun was on
  • Fixed an issue where some X-Axis labels were cut off
  • Fixed an issue where axis values appeared with a scientific notation
  • Fixed an issue that was causing the browser to crash when switching between Discovery tabs
  • Fixed an issue where the Dimension Panel and Data Source information did not load in a Discovery report
  • Fixed an issue where a Viewer user in Analyze Further could not add a Longitude measure into the Map Location dropzone
  • Fixed a slicer issue that was causing a blank screen when clicking analyze further
  • Fixed an issue where a blank screen appeared while working on a Discovery
  • Added a new "Always start from 0" option in the Scale Bounds allowing to hide negative values

Present

• Fixed a cascading filter issue which was causing the interacted content to fail after selecting all items in the filter
• Fixed an issue where adding multi-select tree filters from the same hierarchy to a Present caused the filters to keep loading
• Fixed an issue where targets could not be added to a tiled presentation
• Fixed an issue where a report in Analyze Further could not be modified
• Fixed an issue where a parameter that was dragged from a discover to a present could be set as a tree slicer
• Fixed an issue where a direct link to a Present opened the wrong present when using OpenID while in Incognito mode
• Fixed an issue where a filter did not run when running the presentation
• Fixed an Interaction issue from a grid to a mini tab
• Fixed an issue where changing the initial selection of a calculation that used a global parameter caused an error
• Fixed an issue where the Dynamic Text did not update on a Cover Page
• Fixed an issue where Present Mini tabs failed to load after clicking on undo/reset
• Fixed an issue where a Present based on a SQL data source was timing out after an upgrade to 2020.27
• Fixed an issue where multiple grid rows were highlighted after clicking on a single item on rows

Publish

• Fixed an issue where a change to the subscription time of a Presentation caused its filter selection to reset to the default value
• Added paging to the Schedule Manager for Viewer Users
• Fixed an issue where schedules stopped running due to a time zone change
• Fixed an issue where a subscription was printed with an incorrect filter selection
• Fixed an issue where bold text in a Publication was not applied to a printed PowerPoint output
• Fixed an issue where deleted filter caused a scheduled job to fail
• Fixed an issue where images on the Content page covered visuals in a scheduled publication print
• Fixed a printing issue with a publication that had a page repeater and a page trigger

Formulate

• Fixed an issue where a format string behaved differently after an upgrade
• Fixed a blank screen issue when editing a parameter

Illustrate

• Fixed an issue where a dynamic illustration was updated incorrectly based on a filter selection

Content Management

• Fixed an issue where a message saying "There was an error opening the file" appeared when trying to run or edit a Present
• Fixed an issue where the Search results returned deleted content for a Viewer user
• Fixed an issue where a user couldn't create folders under Workgroup content
• Fixed an issue where a user couldn't delete/purge items
• Fixed an issue where data sources marked as Favorites did not appear in the Favorites panel

Printing / Export

• Fixed an issue where exporting a Publication to PowerPoint had cut-off columns of the grid when using Scale to fit
• Fixed an issue where a pdf export was blank after exporting a map in discovery and selecting the No Scale option
• Fixed an issue where exporting to CSV did not work for a Tabular gird with Sort enabled
• Fixed an issue where a print to CSV contained the filter selection of the report
• Fixed an issue where a shape used as a background of a grid appeared on top of it in a print
• Fixed an issue where a drill-through action export contained more records than expected
• Fixed an issue where printing a tiled presentation with an illustration to a PDF produced a blank print
• Fixed an issue where zero values in a grid did not appear in an Excel export
• Fixed a formatting issue in Excel with content that contained multiple charts
• Fixed an issue where a Unicode character that appeared in an Excel print prevented creating a calculation within the Excel

Admin

• Fixed an issue where the "Reset Password Every XX Months" when using Database Authentication did not work as expected
• Fixed an issue where a SQL Connection parameter caused a connection error
• Fixed an issue where a Mongo DB connection did not work through Pulse
• Fixed an issue where NULL server name values got added to the db after an upgrade and caused an error when trying to view the databases in the Admin Console
• Fixed a Multitenancy issue where a domain admin could not view a data source on a different tenant
• Fixed an issue where an admin couldn't set up the auth method to Windows auth alternative account for SQL server
• Fixed an issue where changing Database List Technique did not reflect in the UI
• Fixed an issue where making bulk changes to the role security in the Data Source Manager did not take the selection in the Tenant Name filter into account
• Fixed a SQL data source connection issue where Pyramid did not use the custom port in the Port field
• Fixed an issue where the Pyramid ad groups failed to sync when using an Oracle repository
• Fixed an Azure Synapse connectivity issue after the 2020.27 upgrade
• Fixed an issue where testing a Databricks connection failed with a missing JDBC connector error
• Fixed an issue where an error 500 occurred when setting up an Oauth data source with incorrect details
• Fixed an issue where errors appeared in the system logs each time a user who didn't have access to the internet logged in
• Added a new "Enable characteristic structures" checkbox in the SAP BW server setting to fix an issue where BW measures appeared as attributes
• Fixed an issue where if Provisioning was disabled, Pyramid still tried to sync the AD groups set up in Provisioning jobs based on the "AD sync time interval" setting or when Run Now was clicked in the System Schedule
• Fixed an issue where Pyramid always used Sync Method 1 when clicking on Run Now in the System Schedule
• Fixed an issue where the System Info did not show accurate RAM info
• Added the option to manually enter a project name into the Big Query data source connection settings
• Added a message indicating that the "Enforce SSL secure cookies and pages" setting will take effect after restarting the web servers
• Added support for the "af-south-1" region for Amazon Athena

APIs

• Fixed an issue where purging a folder using the API didn't purge all the subfolders
• Fixed an issue where Pyramid showed an empty Proxy Account 2 after using /createUserSaml and adding a proxy2 value
• Fixed an issue where /access/getUser showed the principle name as the proxy account
• Improved performance of findcontentItem
• Fixed an error when trying to find the ID of a folder using /content/findContentItem

Mobile

• Fixed an issue where the iPhone PyramidMobileAuth header had no value when logging in through SAML
• Fixed an issue where a preset that was opened with a Direct Link on an iPhone didn't resize correctly after rotating the screen
• Fixed an issue where the mobile application used the English UI language, although Hebrew was selected
• Fixed an issue where interactions didn't work as expected in a dashboard that was opened with a direct link on a mobile device
• Fixed an issue where a filter was unresponsive in Present on the mobile app
• A new Android app version has been published
• Fixed an issue where a Direct Link to a folder in mobile went to the Recent folder and not the linked folder

Other

• Fixed an issue where a Present appeared with the desktop layout when running on a tablet
• Fixed an issue where the upper toolbar of the iPhone was overlying the Present
• Fixed an issue where the size of bubbles on a map was inconsistent between a report and a report snapshot in a conversation
• Fixed an issue where after upgrading to Pyramid 2020.27, users couldn't log in via OpenID when using a proxy
• Fixed an issue where Snowflake OAuth failed to insert a token into the database or read it
• Fixed an OpenID log-in issue where it was not possible to log in due to a missing / in the redirect URL
• Fixed an issue where OpenID failed to authenticate when an array was sent in the OAuth Token
• Fixed an OpenID log-in issue after upgrading to 2020.27
• Fixed an issue where Pyramid was unreachable after restarting the routers
• Fixed an installation failure issue when using Oracle 19c as a repository
• The Pyramid Maintenance Tool now works on Linux

Security

Numerous third party libraries in the platform have been upgraded to eliminate vulnerabilities - covering both Java and JavaScript.

Performance

Numerous adjustments to the application have been made to improve client-side performance and memory management.

Modeling

• Data catalog export to an external repository
• Table connection view in the Model
• Add a dimension usage to IMDB models

Discover

    Visualizations

• IBCS add-ons or additional visuals
• Advanced design in the Presentation
• Grid sizing shrink columns to data
• Chart labels show Min, Max, Most recent and allow overlap
• Dynamic or adjustable size for panel used with tree drop down
• React component feedback
• Enable data labels visibility for every slice in a pie chart

    Analytics and Functionality

• Totals for stacked column chart
• Totals individual column aggregations
• Re ordering slicers in the hidden panel
• Selecting all in filtered filters should select only filtered entries
• When searching in a multi select tree slicer the all select should only select the filtered results
• Allow bin by size to use parameter
• Drill up to all member
• Expand down all elements in a column
• Auto selecting items when searching in a slicer
• Multiselect tree slicer description tooltip or expansion
• Runtime option to remove a dimension on a grid
• Select all from selection option for multi select filters
• Size and position for panels as per shapes
• List reordering
• Show server database model path on analyze further
• Ability to hide the start new conversation option when using custom workflows
• Hide roles as recipients in conversations
• Change the default email text
• Multi-select filter available in 'Analyze Further'
• Slicer enhancements
• Viewer License: Some features have been removed (conversations, submenus in Dropzones) in the current version
• Provide ability to use Variable in Rolling Average Context Calc

Present

• Saving slicer selection views in a presentation
• Re ordering slicers in the hidden panel
• Re order hidden slicers in presentation
• Simple calculations with different dynamic texts

Embed

• Reset back buttons in embedded version
• Embed new smart discovery option
• Pyramid embed allow for full screen option
• Bring runtime navigation through on embedded dashboards
• Pyramid Embed - Allow for Full-Screen option

Content Export

• Print to excel breadcrumbs
• Allow publications to have an expiration date
• Allow removal of subtitles breadcrumbs from scheduled excel publications
• Dynamic subscriptions on presentations

Content Management

• Search items by id
• Allow save in analyze further when a report is opened via an action
• Ability to set save to automatically save as new version

Administrative

• Specify emails to which admin alerts are sent threshold level at which alerts get sent
• Add logging to the router service
• Add 'App Experience' option to disable LearningHub or change its URL
• User usage analytics
• Ability to embed help desk widget

Authentication and security

• More granular authorisations for data model and data source
• Windows authentication for PowerBI workspaces
• Save login data via mobile app using SAML

  Back to Pyramid 2023 release announcement

This Blog has been written by Mastaneh Nikroo  from our Partner Reply.

In today’s world, data plays a crucial role in organisations and is one of the determinants of their success. All businesses and enterprises are focusing on their data management approach and creating added value through their data products. Data is growing at a faster rate than ever due to new technologies such as IoT and Cloud. Indeed, 90% of the world’s data was created in the last two years and every two years, the volume of data across the world doubles in size.

By growing the size of organisations and also the amount of data they produce or consume, the existence of efficient data management and data architecture is inevitable. An inefficient data architecture creates bottlenecks in accessing the necessary data for different teams and increases the environmental footprint of the data storage. By optimising the data flows and accesses, Data Mesh creates value for the enterprise, all stakeholders, and the environment.

In this article, we will explore Data Mesh Architecture and how Pyramid Analytics can be used to migrate to this data architecture. The first section introduces the concept of data mesh and its importance and necessity in data governance. The next section explores the breadth of tools in Pyramid Analytics’ platform that can help facilitate with the transition to a Data Mesh Architecture.

What is a Data Mesh

Originally, organisations used central data teams to manage their data, however this process proved to be inefficient in large organisations with different domain teams, as a central data team would often become a bottleneck by being unable to grant access to necessary data sources for different domain teams on time.
As a result, Data Mesh Architecture was developed as a much more streamlined way of managing data. Data Mesh decentralises the ownership of data from the central team to domain teams and allows the domain teams to conduct cross-domain data analysis on their own. This results in domain-orientated decentralisation which creates better and more efficient access to data sources in each domain team, which in turn creates business value.  The Data Mesh Architecture generates a platform for creating, monitoring, discovering and accessing data products.

Main Characteristics of Data Mesh

There are four main features that distinguishes a Data Mesh when compared to other types of data architecture:

• Domain Ownership: This shifts the responsibility and accountability of the data to the domain teams.
• Self-Serve Data Platform: This feature provides access to high-level infrastructure abstractions for users.
• Data as a Product: It solves the problems related to data quality and data silos. According to this principle, data is not a siloed product but a business product that benefits different stakeholders.
• Federated Computational Governance: creates shared responsibility between the domains and the central Data team and allows adequate autonomy for each domain.

Thanks to these principles, each domain team will be in charge of its own operational and analytical data, data security, and compliance. Data Mesh enables the domain teams to be able to ingest operational data, create their own analytical data, and grant access for other teams to use their published data.

Using the Pyramid Tenant System to Migrate to Data Mesh Architecture

The Content Management System (CMS) in the Pyramid platform facilitates the migration to Data Mesh Architecture. CMS provides the possibility to define different groups of users and refer to each group as a tenant. All users in a tenancy share the same privileges in terms of accessing the software. Besides, the Multi Tenancy feature enables different tenants to co-exist on a single instance of the platform and the central data team has visibility and control across all tenants. 

In addition, CMS provides the chance for the central data team to assign domain admins who can freely manage all the users, roles, data sources, profiles, logs and licencing within their respective tenant domains. Content Management System (CMS) in the Pyramid platform facilitates the migration to Data Mesh Architecture. CMS provides the possibility to define different groups of users and refer to each group as a tenant. This allows different tenants to coexist on a single instance of the platform and the central data team has visibility and control across all tenants. In addition, the CMS admin (enterprise admin) provides the chance for the central data team to assign domain admins who can freely manage all the users, roles, data sources, profiles, logs and licensing within their respective tenant domains. A tenant admin can set up tenant-specific data source connections which cannot be viewed or accessed by users in a different tenant. Users within a tenant can be added to specific roles which the tenant or enterprise admin can use to control row-level security to the connected data sources.

How would this look like in the CMS:

Overview of total available Data Products in the System

Available Data Sources in the Data Landscape

Selection of Curated Data sources with Access Management and Metadata Information

Cross-Tenant Content and Roles

Cross-tenant content refers to the content seen and accessible by different tenants without having to create duplicates and it can only be set and managed by the Central Data Team. Cross-tenant roles allow users to join roles outside of their own tenancy. This feature allows domain admins to add and remove users, from outside their domain/tenancy, to existing  roles without Enterprise admins.

Conclusion:

In summary, Data Mesh Architecture offers a much more efficient way of managing data and through Pyramid’s CMS and other features such as Multi-Tenancy, the transition to data mesh can be achieved with relative ease.

by Mastaneh Nikroo from our Partner Reply, read the full whitepaper on DataMesh and ESG here: https://www.reply.com/data-reply/en/Data-Mesh-ESG-and-Leveraging-Pyramid-Anaytics 

Using reports and dashboards to analyse business performance and make decisions is a fundamental aspect of a Decision Intelligence strategy. However, it is possible to get a little carried away when developing this type of content as we have so many options and possibilities to make our visualizations ‘stand out’, particularly when using color and images. In this series of articles we will look into different aspects of report and dashboard design.

Building Dashboard for Visually Impaired users

To kick off, we will look into colours, most specifically, how some choices will work out for people with colour blindness. 1 in 12 men and 1 in 200 women suffer from some type of colour blindness or CVD (colour vision deficiency). Bearing this fact in mind, we should be aiming at making our visualizations as user-friendly as possible.  

In this entry, we will showcase five color combination do’s and don’t’s, explained in simple terms, so they’re easy to remember and apply in everyday visualizations. At the end of this article, you will find some user-friendly color palette suggestions. 

Types of Color Blindness 

There are different types of color blindness, although most users with CVD will struggle with reds, oranges, yellows, browns and greens. You can see below an example of how these colors might look like to users with Deuteranopia and Protanopia.  

1. First Tip: Avoid using green and red. These are the colours that will present most issues to your CVD  audience. Some shades of these colors can be extremely problematic as seen in the example below. 

   

   

   Other color combinations to avoid are: 
   GREEN + brown
   GREEN + blue 
   GREEN + grey 
   GREEN + black 
   LIGHT GREEN + yellow 
   BLUE + grey 
   BLUE + purple 
2. Second Tip: However, if you really need to use one of these combinations, a good solution will be using a lighter shade of one of the colors; and a darker shade of the other one, so the difference won’t lie in the actual colour itself, but on the contrast between shades.   
   In this example, we have used a darker red and a lighter green. The difference now is much more obvious for CVD users. 

   

3. Third Tip: We can follow the same dark/light shade principle; this time, using a monochromatic palette.  
   For example, using grey scales are a great, safe alternative to using colour. The color palette presented below will be perceived the same for CVD and non-CVD users. 

   

   If you would still like to use color, stick to one monochromatic palette, e.g. using blue from the darkest shade to the lightest.

   

    
4. Fourth Tip: If you would like to use two colors, try to use blue and orange. These will always be your ally, as they are considered the best color-friendly palette because of their evident contrast! 

   

5. Fifth Tip: USER-FRIENDLY COLOR PALETTES 
   Visit the Venngage website for more information on user-friendly, accessible color palettes. Some suggestions: 
   Palette 1 

   

   
   Palette 2 

   

   
   Palette 3 

   

   
   Palette 4 

   

Learn more about how to set your own color palette in Pyramid and use these Themes across a variety of Modules in this Learning Live Session.  How to use Themes in Pyramid

Can you think of any visualizations you have worked on recently that could be more user-friendly if applying these easy principles? Let us know your experiences with Report Design and how you handle these Topics in your Pyramid Environment.

Thank you for your time.

*Title Picture from: https://www.colourstudies.com/blog/2019/9/5/colour-blind-test 

We're working on a number of new 'mega' features for Pyramid right now - you may have seen the announcements about Tabulate and Solve  - and there will be lots more included in the upcoming 'Archimedes' release for 2023.

A question that we get asked all the time: "How do you decide what to put in each new release?"

To answer this question, there are three main things we consider:

1. Requests from our customers and partners (check out our User Community and customer forums).
2. What is going on in the market - both generally and among similar technologies in the space.
3. An appraisal of our original design paradigm for Pyramid and the platform.

These are not in any particular order and need to be carefully balanced.

How to set priorities?

While the above three items are somewhat obvious, the priority for executing them is not.

For example if you look at requests from specific individuals these may be very important for them but not always helpful to the vast majority of other users and customers.

When it comes to the marketplace of course we want to continue to define the decision intelligence space and stay ahead of the competition in the three main areas of the complete platform we provide.

(For those that don't know us we uniquely include data prep, business intelligence & Reporting AND data science into one integrated platform).

However, it is also the case that some product developments can be driven by an individual deal or deals where a large-scale enterprise commitment to Pyramid demands a similar investment by us in specific capabilities.

And of course bugs are important and need to be prioritized as they can be widely applicable. 

Concentrating on the Core

As far as our original design principles go these all come together in our 'Data Factory for Decisions' concept. (See link for a video on that.)

We believe in delivering on a start-to-finish platform for all our users' decision making needs. My goal is to deliver a holistic solution that will make it easy to do something that has always been incredibly complex: to support intelligent decision making for business.

This year will see us getting very close to completely delivering on the entire factory. 

So, please keep inspiring us with new Ideas in the Ideas Forum (logged in Users only)

New Semi-Additive Measures: First Child and Last Child in Model (beta)

Two new semi-additive measure aggregations have been added to the semantic modeling options. This means that while they may total for some dimensions, they may be aggregated in a different dimension. For example, consider a financial application. One of the Measures may be Cash Balance. If I consider the month of March, the value of Cash Balance will not be the total of Cash Balance added up for all the days in March, but it will be value of Cash Balance on the last day of March or the last day where there is data. In this instance Cash Balance would be declared as a “Last Child” aggregation, so that in a regular Hierarchy where Date was the child of Month, the value would be that last day where data existed, or the 31st of March. Similarly, the opening Cash Balance for March would be the value of Cash Balance for 1st of March, or the First Child.

Support for such semi additive Measures, coupled with extensive support for Parent / Child Hierarchies means very sophisticated financial analytic applications become possible and remarkably easy to implement with Pyramid.

Support for SAP BW Double Structures in BEx Queries (beta)

Structures are objects that can be defined in SAP BW BEx Query Designer. A structure forms the basic framework of the axes in a table (rows or columns). The setup of the structure determines the sequence and number of key figures or characteristic values in the columns and rows of the query.

Pyramid now supports the derivation of the set of measures created by the definition of these “Double Structures”, where one structure holds key figures (Measures) and the other the set of characteristics (Dimension Members). This makes all the calculated items immediately available to users without having to recreate them using Formulate. In many organizations, this may be hundreds or even thousands of calculated, derived values.

New Ribbon Tooltips

Users can access tooltips on all buttons in the ribbons by hovering the cursor over the button. Clicking on the Learn More link will open the more detailed online help pertinent to the topic. This enables users to immediately understand the functionality of each button, while optionally gaining a deeper understanding by viewing the topic in online help without having to search for it.

Model / Alert task cleaner

The admin can set the amount of time to keep data alerts and model processing logs. By running the task immediately, the task cleaner allows cleaning up of model and alert tasks with the ability to define how often the logs are deleted. This reduction of data assists in maintaining a more compact, responsive system.

New Page Size default

The default page size for printing can now be set per tenant. The administrator can define the default page size that will be used for all publications. The user won’t have to select it each time a new publication is created.

New Mobile Info button

Mobile users can access information relating to the content. By identifying content details like creation dates, source databases and content names, the user can engage meaningfully with content authors when requesting changes or clarification on content issues.

Chart Gallery

A new ribbon gallery showing different predefined chart styles allowing users to quickly switch the chart design. Users can choose from a selection of six different presets to apply to their charts and ensure a standardized look across all their charts in terms of the display of the Y-axis, legends, grid lines, data labels and report titles. This extends the same feature previously provided for grids and maps to most cartesian charts.

New Workday schedule option (beta)

Reports can now be scheduled to run on specific workdays of the month. The first workday of the month can be set to a Sunday or a Monday by tenant - to accommodate the different work weeks in different countries. This allows users to run a report on a specific workday of the month. For example, a schedule can be set to run on the third workday, allowing for month-end processes to be completed on the first two days before running the report.

Enable or Disable Auto-Discover

Users can enable or disable the Auto-Discover option in their profiles. This allows users to set a default for all Discover reports enabling them to only run reports when required. This is particularly useful for users running reports with long processing times where they only want to run the report after making all the changes.

New REST APIs for authentication and updating data model flows

The API now supports management of additional features related to the Model Flow. There are additional new features and improvements to Authentication APIs.

Implementing parent-child hierarchies has long been an Achilles’ heel in most BI tools from query design, processing, structure, to rendering of the hierarchy in a lucid, coherent fashion. If this wasn’t enough of a headache, applying member-level security to the hierarchy makes the challenge extremely difficult (if not impossible). Pyramid’s model tool allows for the easy definition of parent-child hierarchies (see blog), and Pyramid’s PYRANA Query Engine resolves the hierarchical structures and provides extensive, fluid, and intuitive reporting. This facilitates direct queries on supported data sources without requiring data ingestion or data source manipulation.

In this blog, we’ll focus on the setup of member-level security for parent-child hierarchical structures.

The Problem

The complex requirement to display and roll up or aggregate members within a parent-child hierarchy provides a burdensome challenge for most BI tools. Parent-child hierarchies are often used in financial accounting analytical applications or org-chart analytics. Often, these types of analytics are butchered by various hacks or clumsy workarounds.

The additional requirement to restrict users’ ability to view specific members further complicates the delivery of an elegant, tight, and intuitive solution. Depending on the business requirement, parent elements may be visible, while child items are hidden; or parent totals might need to include or exclude child members not visible to the user. Analysts are often left with no other viable option than to create multiple custom reports. In addition to the time-consuming task and the difficulty in maintaining the solution, it also becomes the ultimate antithesis of “a single version of the truth.”

The Solution

Pyramid’s parent-child hierarchy solution facilitates direct query on the existing data source, using the parent-child hierarchy to aggregate and display data as needed. Administrators can apply role-based security to include or exclude specific parents and child members within a hierarchy. When members are not selected, users will be able to view other members together with their amounts while the parent node still accurately accumulates all child nodes. By selecting a parent or child node, all ancestors up the hierarchy tree will be visible. The optional “visual totals” switch enforces totaling only for members that the user can see.

Business Case

Kate is a BI Analyst at Glennies Trading where they have a Databricks data warehouse with Pyramid for their analytics.

Kate wants to allow users from all departments to view both overall sales and sales per region, but users should only see the details for their own region. Kate creates a role for the northeastern region in the admin console. Users belonging to this role will be able to see details for all branches in the northeastern region, but they can only see totals for all other regions.

When a user with the northwestern sales region role views the sales report, Pyramid secures the member-level data from the parent-child hierarchy and only displays the secured data.

A user from the southwest region will only be able to view their branch data, together with other regions’ overall data.

Even though total company sales can be viewed by all regions (as it adds to positive competitiveness), when it comes to Standard Cost of Sales, users from one region must not be able to view other region’s expenses as that will give them insight into the company’s overall profitability (which is restricted information). Kate clicks on the visual totals option to ensure total expenses will only reflect expenses accessible to the user, so nobody can calculate total expenses and by extension, the company’s profitability.

Now when she views the report, she only sees the totals of the items that she can view. Importantly, note how the parent total consists only of members for which it has access.

Summary

Pyramid’s flexible, intuitive security for parent-child hierarchies provides control over how members are viewed, queried, and aggregated within hierarchies. By allowing administrators to include or exclude specific parent and child members within a hierarchy, relevant applications can be optimized to provide world-class reporting and analytics.

Analysts often use specific colors to identify distinct hierarchies or measures in visualizations. Predetermined colors can be explicitly selected per hierarchy or measure, but this can be an arduous task when multiple hierarchies are displayed. Default color sets are routinely used by some BI tools to assign colors to categories. Pyramid provides additionally flexibility by allowing default color sets to change when different themes are selected.

However, even when default sets are used, the color assignment can change for specific element selections (for example, New York could change from green to blue) when adjusting the query and the selections (like drilling down, or performing analysis using “analyze further”). This color change can be confusing, and even more baffling when two visualizations in a dashboard initially have the same legends and subsequently use disparate legends.

Pyramid’s “member color lock” feature ensures colors for the targeted items are persistent and do not change even if members are removed or slicing is performed.

Example

In this example dashboard, Australia appears in the Revenue column chart but has been removed from the Expenses column chart. So, for the chart on the right, the standard colors have re-assigned, and we end up with the same countries having two different colors.

 By simply selecting the member color lock button from the Design ribbon, the colors for the visuals are locked.

After locking the colors, the same colors persist even after eliminating members.

Summary

The use of specific colors helps users to immediately identify hierarchies, measures, and members in a visualization. After performing slicing or dicing, assigned colors can change, creating confusion.

Pyramid’s one-click persistent color feature maintains the same color in all visualizations, providing users with consistent and intuitive visualizations.

Adding subtotals to a table or grid enables users to quickly grasp values per dimension or hierarchy. Subtotal functions are often toggle switches that provide subtotaling for either all dimensions or none. This can result in displaying unnecessary subtotal details, confusing and frustrating users.

Pyramid allows users to customize which hierarchies will have subtotals (or not). These can be set for both columns and rows in a classic matrix grid visualization. Additional total functionality allows positioning of totals before or after details. This also includes being able to specify the totaling functions including sum, count, average, min, max, median, and standard deviation.

Example

This Revenue report includes standard subtotaling, but as last name, first name, and email are displayed, the subtotaling is added to all items. This results in unnecessary totaling detail for first name and last name, creating a somewhat unusable report.

Using Pyramid’s Total Dialog allows users to easily select position, type of totals, and the type of aggregating function to apply to both rows and columns. Importantly, the user can deselect the LastName and FirstName hierarchies, ensuring subtotaling is only applied to the City level, making the result clear, concise, and more useful.

Summary

Pyramid users have complete control over totaling in grid visualizations including the level at which subtotals are calculated and displayed—for both rows and columns. This means the subtotaling of unnecessary hierarchies can be avoided, resulting in more intuitive, uncluttered visualizations. Together with Pyramid’s additional flexible positioning and total aggregation functions, users can design reports to perfectly match their requirements.

Server-based software with governance allows different functionality to users dependent on the roles and security structures that are applied to them. For example, role-specific security might hide financial measures from clerical roles, or customer names may be hidden from unauthorized users to comply with Payment Card Industry (PCI) standards. This requires users to log in and test before deploying tenant-, role-, or user-based content.

Pyramid’s impersonate feature allows administrator users to “Log in as a User” to emulate non-administrator users, fully experiencing all content and menu options as if they were the user. This enables comprehensive troubleshooting of all user and role-based logic, without requiring user testing (which might involve ghost logins or other workarounds) before releasing a feature. Advanced logic that uses user-based functions and security can also be tested by administrators.

Example

The administrator views the customer dimensions available from the Discover module, where all personal attributes including name, address, email, and phone are available.

The administrator sets up role-based security for the ‘Analysts’ role by disabling the Customer name, address, email, and phone attributes. All users logging in under the Analysts role will not be able to see any of these details.

The administrator then clicks on the Impersonate button for a user categorized under the “Analysts” role.

Upon opening a Discover report, none of the personal details are available for display.

Summary

Pyramid administrators can “impersonate” any user by logging in as that user without their credentials, enabling quick and easy testing of user-related issues before releasing features into a test or production environment.

As more applications and databases are developed it is important for analytics platforms to be able to access and ingest data from these disparate sources. Pyramid’s Custom Data Connector feature allows administrators to easily add and configure custom JDBC connectors (or “drivers”), adding them to Pyramid’s list of pre-configured JDBC drivers, so users can ingest data from these data sources as if they are a built-in option without requiring any further configuration. Administrators can set the caption and description and upload the “JAR” file for the JDBC driver and use them instantly without requiring any additional coding. Pyramid’s robust governance model also ensures that the drivers are secured in multi-tenant deployments.

Separately, Pyramid has partnered with CData. CData sells over 200 custom JDBC connectors that can be integrated into the platform.

Example

An administrator wants to add a JDBC driver for Zendesk to provide users access to ingest their help desk data and integrate with their line-of business systems for reporting. The administrator accesses the custom connectors dialog box and enters the caption and description and uploads the Zendesk Jar file. The user then selects the “Other Sources” category, so the connection can be accessed when working on the “Model” tool.

Once the connector has been set up, the connector appears with all “Other Sources” options and even a non-administrator users can drop the connector onto a model as if it were a standard source without requiring any technical knowledge of how to set up connectors.

CData

The CData JDBC driver collection is extremely large and excels with data sources that don't typically come with their own drivers. Click here to read more.

Summary

JDBC data connectors enable users to ingest and combine a variety of data from external sources. Pyramid provides administrators the ability to easily configure custom connectors without new code from Pyramid. Non-technical users can then seamlessly access these data sources in the Model tool as if they were part of the platform itself. Finally, Pyramid’s robust governance model allows the drivers to be secured and deployed in a multi-tenant environment.

Pyramid has had various options to shown different values in the same chart or report, including:

We have added a new option: the ability to use the trellising chart capability with the option to also change chart types per metric via simple, yet sophisticated drop zone tools. This flexibility provides more options for users to better show and understand their data.

Why is it needed?

Comparing data with multiple measures in a single chart can be misleading with different scales that apply to the different measures. For example, gross revenue for different sales representatives might be in a range between $500k and $800k, while their net profit might be between $100k and $200k and their margin percentage between 5% and 10%. Allowing multiple charts with different y-axis calibrations greatly improves comprehension by magnifying variations of smaller amounts—and removing the ambiguity of using a single measure scale. This then allows users to show distinct chart types for each metric to better depict the underlying data. For example, instead of using a column chart for margin, a user might want to use a line chart for improved graphic illustration.

Example

This chart illustrates how sales, net profit, and margin can’t be meaningfully depicted on a single chart.

The range for the margin amount is between 37% and 39%—so low that it isn’t recognizable on the y-axis.

So, one way to address this is to use combo charts with primary and secondary axes.  

The chart below suddenly allows the margin metric to be clearly visible in the same chart plot as the other two metrics, using both a different chart type (line) and a different y-axis.

If the measures were of similar scale, then a shared-primary axis chart could also work. For example, revenue and expenses have a similar range of values and can be accommodated on the same y-axis.

In the chart below, both revenue and expense values are shown on a single plotted chart, sharing the same y-axis.

Another technique, when the measures had different value scales, is to let the application trellis the results so the y-axis scale changes per plot of each measure.

In the chart below, we get three charts: one for sales, margin, and net profit. By separating them into three distinct plots, each one has its own y-axis values and scale.

But what if we want different chart types for each measure in the above trellised plot, so that the margin can be displayed as a line chart, sales as a column chart, and net profit as an area chart?  

This can now be achieved by using the new “trellis” sub-drop zone choice and choosing the appropriate chart option.

You can see the effect in the chart below, with each measure in its own plot, its own y-axis and scale, and its own chart type.

Summary

There are many nuances to drawing multi-metric charts. Pyramid supports various approaches through the simplicity of its drag-and-drop drop zone interface. Through this framework, multiple measures with different scales are more easily understood in separate charts with different y-axis ranges, making the analysis easier to read and use, and therefore more impactful.

Pyramid’s built-in multi-factor authentication (MFA) option adds a rock-solid layer of security to your BI application if your authentication provider does not offer it.

Passwords alone have proven to be an insufficient means of security in today’s world. More than 55 percent of enterprises use MFA to provide an additional layer of security. While some customers who have implemented Single-Sign-On (“SSO”) enterprise identity provider technologies (like SAML or OpenID) may have access to MFA from those systems, many customers who use other technologies like LDAP or Active Directory cannot access these capabilities. Pyramid’s built-in MFA framework with “Time-based One Time Passwords” allows customers to deploy and use MFA with the click of a button.

The Problem

Professionals are facing increasing security threats from tactics like phishing, pharming, and hacking. In response, enterprises now recognize the need to bolster their security.

Most hacking-related breaches are caused by stolen or weak passwords. Classic authentication requires a user login and password, but these are becoming easier to hack and steal. Furthermore, passwords are a headache to remember and change on an ongoing basis, so users often choose to reuse the same password between systems and devices. Consequently, a hack on one system may end up compromising many other systems.

The Solution

Multi-factor authentication (MFA) is a security protocol that requires multiple methods of authentication from independent categories of credentials to verify a user's identity. Pyramid provides MFA where the user authenticates their credentials via Pyramid — this includes the internal authentication system, LDAP, or Active Directory.  

Enrolled users are prompted to enter a machine-generated Time-base One Time Password (or “TOTP”) to log in, along with their username and password. This TOTP solution generates a unique code every 30 seconds using special authenticator applications (usually on the user's mobile device) making it extremely difficult to circumnavigate. The authenticator apps, from vendors like Google or Microsoft, are usually free.

This ensures that even if a user's credentials are stolen, access is blocked without the independent security code from the authenticator app. Administrators can enable MFA for the entire Pyramid deployment, or for each individual front-facing web server. Further, administrators can also reset the MFA token associated with a given user, ensuring that if the authenticator app is compromised, access can be centrally blocked.

Example

The administrator sets up MFA in the web services panel, applying the authentication for all form-based logins into the platform.

Users then log in. When they do, they must enroll themselves into the MFA by scanning the displayed QR code into their downloaded authenticator app. Once enrolled, the user is required to enter their username and password as normal and then supply their unique TOTP code, generated by the authenticator app, before gaining access to the system. 

Summary

Passwords alone are an insufficient means of protection against security threats. Pyramid’s in-built multi-factor authentication safeguards your data environment with advanced security, without the hassle of using third-party tools or other applications.

The customization tools and workflow APIs allow customers to build their own data-driven action lists, commentary systems, annotation models, documentation tools, and interfaces with other technology platforms directly into Pyramid using standard HTML, JavaScript, and CSS style sheets.

Extending Conversations

Pyramid’s original conversation workflow framework allows users to annotate and comment on data points, reports, and dashboards using an out-of-the-box tool set. The new Custom Workflow tools use the same demarcation mechanics in the platform which annotate data points (or cells), hierarchical members, reports, or dashboards. However, instead of limiting the workflow to just comments written between colleagues in a conversation thread, the customization opens up the platform to any kind of content and payload; presented in any visual format; and stored internally in Pyramid or externally in any other application.

The Problem

Co-workers collaborating on reports often need to use workflows with custom input fields. For example, upon identifying a patient issue in a patient diagnostic report, a clinician may want to write some action items to the patient management system. While the process of tagging and tracking the patient and their data in Pyramid is a simple click, the format and protocol for the action items is not a simple text message. So, a classic conversation mechanism only solves half the problem.

Most reporting environments don’t extend their collaboration interfaces, resulting in users needing to manually resolve the problem with external tools. This means workflow processes are initialized and run in separate silos from analytics—an approach that is mostly clumsy and inefficient.

Business Case

Vera is the lead clinician for ABC Health Insurance Inc. ABC Health uses Pyramid on their Oracle data warehouse that incorporates medical claims data for their insurance company. The platform has recently been upgraded to include a new patient “action” system using Pyramid’s Custom Workflows, allowing users to annotate relevant patients with intervention flags based on an analysis of their conditions and history that is produced and consumed from Pyramid. The flags, however, are stored in their external patient tracking software, which is used downstream to action the items flagged upstream. The workflows have been setup to both write new status flags to the software as well as read the status flags back into Pyramid for edit and review in the context of the reports and data that generated them.

Vera browses her weekly high-risk patient report and notices that eight new patients have been flagged as potential candidates for HBA1C testing (related to diabetes). She initiates a high-risk patient workflow to one of her nurses to indicate the course of action that should be taken.

The workflow process writes flags to the patient tracking software to indicate the patients require bloodwork and follow-up with a liaison officer. No additional software or manual reminders are required in this integrated solution. The custom workflow has been designed to also allow Vera to set a due date for the actions items and designate a specific nurse to track the patient.

In the future, anyone reviewing the analysis of the selected patients will see the annotation flag inside Pyramid and can use the same workflow mechanism to read the tags that have been set by Vera.

Summary

Most reporting environments have limited annotation interfaces, requiring users to utilize other tools. This results in inefficient processes when trying to deliver actionable outcomes from analytics. Pyramid has extended their conversation workflow framework to a Custom Workflow tool allowing any content, presented in any format, to be stored internally in Pyramid or externally in any other application. The customization tools and workflow APIs allow customers to build their own data-driven action lists, commentary systems, annotation models, documentation tools, and interfaces with other technology platforms directly into Pyramid using standard HTML, JavaScript, and CSS style sheets.

In most application designs, space limitations or aesthetic considerations require the units, currencies, or both (e.g., USD and EUR) to be included in the same column as the measure column. Reporting on multiple units and currencies in most BI tools usually requires separate columns to be displayed for the units or currencies in addition to the measure column—and can typically only support the static format mask approach. Alternatively, a separate hierarchy is needed to provide context to the numbers to show what currencies or units may be applicable to the set of figures. The better approach is to format the data with the right labels from the data itself—making it more intuitive for users and generally much more foolproof.

Pyramid provides built-in capabilities to set both prefix and suffix label formatting for measures, allowing users the option to set data-driven currency and unit data formatting in Pyramid’s Model toolset. This enables the visualizations to seamlessly display currency and/or unit data in all reports and visualizations.

Example

This report displays sales of dried fruit and nuts per unit and currency. For easier readability and better design, the units and unit type must be displayed in a single column, and the currency and amount should also be displayed in a single column.

In the model tool users can select the currency as the prefix column for the amount measure, and the unit type as the suffix for the units measure.

The resulting report now neatly incorporates the combined prefix and suffix for the disparate measures, providing a more elegant, ergonomic report.

Summary

Currencies and numeric units are a critical element in data analysis. Too often, they are not incorporated in an intuitive, easy-to-use manner within BI tools. Pyramid’s data-driven numeric formatting allows data values to be formatted fully, using either static format masks or those driven from inside the dataset itself.

By definition, self-service analytics is about letting users work creatively with data to produce their own data discoveries and visualizations and ultimately drive better decisions. The self-service component means changing queries, calculations, visualizations, and anything else needed to deliver useful information.

One contentious area, however, is the changing of the data structures themselves. On one hand it offers full control over the analytics process. However, it also lets end-users potentially break or tweak data models and structures in a way that is inconsistent with how they are designed or should be used. This breaches “governance” and makes the general process of self-service analytics significantly weaker because there is no single source of the “truth”. In short, centrally defined reports ensure governance and shareability of enhancements made to a report but provide a challenge for users wanting to customize their view of the report.

To address both paradoxical needs, Pyramid lets end users build content, queries, and calculations as needed within a governed framework. This includes tweaking data models—but without breaking technical structures or corrupting the “truth” along the way.

Personalization options allow users to customize the models and set their own captions for measures and hierarchies within a report. Hierarchy types can be set (this is useful for triggering different analytical capabilities) while metric data formats can also be changed to any numeric format. When making changes, users have the option to set the personalization for the current report, for all their reports (and sessions), or, if they are an administrator, apply the changes for different roles in the system.

In this way, Pyramid offers the best of both worlds by allowing personalization of reports while still retaining the governed, centralized report definition.

Example

The user of this report wants to use the term “Revenue” instead of “Sales”. In addition, the user wants to use the term “Branch” instead of “City” and wants to replace “dates year” with the title “Year”. The amounts must be displayed in the thousands format (K), rounding to the closest thousand.

By right clicking on the sales chip, the user can adjust the caption, the format string, and the label format. The user can also choose to apply these changes for this report only, for all of their subsequent reports, or for all roles that an administrator user has the security authorization to apply this for.

After applying the changes, we can see how the captions have been adjusted on the chips, in the report, and even in the title of the report. The numbers have also been adjusted to display the “K” format. Any other user will not see these changes, as the user specified that these overlays will only be applied “For Me”.

Pyramid has retained a single definition for this report, retaining governance and shareability, while providing the flexibility to customize the report per user.

Summary

Pyramid users can personalize reports by changing data model elements to customize things like the report title, column names and measures formats—while still retaining a central definition with its inherent enterprise-wide governance and security.

Real insight into data is usually only gained after traversing and mining mounds of data, until the golden nuggets are finally found. Rather than manually drilling into the data, using intuition, or educated guesses to choose dimensional elements to find what’s driving results, “Explain” automatically determines the elements that contribute to a data point's value.

The Problem

A huge amount of time-consuming effort is usually expended to source data, manipulate it, and arrive at insights. Analysts need to devote considerable time combing through a myriad of permutations of hierarchies and metrics, looking for drivers influencing high concentrations of values and/or average values. An automated AI function would be the ideal solution to this quagmire. Often BI tools don’t provide such capabilities directly on any data source requiring data ingestion first. And then they can only perform automated analysis on predefined metrics and hierarchies, effectively relinquishing the self-service, on-demand functionality.

Pyramid’s solution

Pyramid’s “Explain” feature performs automated analysis in a single, on-demand click without requiring any data preparation or complex development. Insights can be gleaned immediately against any data source. An advanced machine learning algorithm is used to automatically traverse all hierarchies and metrics, finding the most significant drivers and influencers behind the values in question.

The Explain feature deploys a two-pronged approach, analyzing both high concentrations of values and influencing factors, revealing the most significant items and metrics.

The feature combines automatically generated textual commentary with graphical representations. While an advanced interface exposes a larger set of contextual reports and AI-generated explanations for each selected node.

The depth and intensity of the algorithm can be customized to suit the user’s requirements in an intuitive, user-friendly interface. While all generated insights, reports and dashboards can be saved as standard artefacts and further enhanced using Pyramid’s Analytics platform.

Business Case

Amy, a business analyst at XYZ Telco, is using Pyramid to analyze data from her SAP system using HANA Calculation Views. During her analysis, she discovers that monthly charges for customers that have multiple phones lines and online security is $1,044. She wants to understand why it’s so high, and what other items in the data best explain its value?

Amy right clicks on the data point and selects “Explain”, triggering the underlying query engine to retrieve all the details for that data point across the ENTIRE data model and present it to the Explain machine learning engine for automated analysis.

Within a few seconds, Amy can start to review the results (shown below). First, she reviews the first tab that explains what items are dominating the value drivers shown in the “eyeball” diagram. It shows that customers with streaming TV (StreamingTV = “Yes”), are the most significant element and account for $884.25. Within that, two-year contracts account for $638.90, and within that, $463 is accounted for with customers who have technical support. By clicking on the middle oval, she can also see textual commentary describing the breakdown on the right-hand side, with a contextual column chart depicting how the Two-year contract monthly charges compares with other members in the contract hierarchy.

Next, Amy wants to determine what factors are pushing the average value of monthly charges higher for the specified datapoint. This may give her a sense of the relative sizing, versus absolute value concentrations.

She clicks on the second tab, (the influencers tab), where the AI engine traverses all hierarchies AND other metrics in the data model, using average values in determining the impact, rather than simple summation. Here it shows that when the contract tenure is greater than 63 days, it has an impact of just 1, while within that, a fiber optic internet service has a 1.2 impact (which means those customers with this option pay 20% more on average in monthly charges). And finally, any customers with streaming members impacts the average value of monthly charges by 30% or 1.3. (Factors over 1.0 contribute to increasing average values, while factors below 1.0 contribute to decreasing average values).

Now, Amy wants to increase the depth and breadth of her analysis – and moves to the more advanced interface.

She first selects three branches and four levels in the search parameters and views the results in a tree. Here Amy can see two additional branches on each level. So, in the first level she can now view two-year contract and partner = “Yes”, on the second level she can view Partner = “Yes” and Tech Support = “Yes” within the context of Streaming TV = “Yes” etcetera. The tree also stretches all the way down to a fourth level where she can view Online Backup = “Yes” and Payment method = “Credit card automatic” within the context of TechSupport = “Yes”.

And finally, Amy wants to perform a detailed analysis on the Online backup node on the fourth level. By clicking on it, the advanced interface also exposes a dashboard containing four contextual reports with automatically generated explanations.

The Explain feature has effectively allowed Amy to perform a detailed analysis with a single click in a few minutes, directly against her SAP HANA datasource without requiring any ETL, ingestion or data manipulations.

Summary

Pyramid’s “Explain” feature performs automated analysis directly against any data source in a single, on-demand click. “Explain” finds the most significant drivers and influencers for a selected data point by automatically traversing all hierarchies and metrics. It analyzes high concentrations of value and influencing factors for increasing or decreasing average values, combining textual commentary with graphical representations to highlight results.

Pyramid provides enhanced no-code tools to allow users to better maintain existing data flows and ETL logic in its data modeling toolset – making the process of creating and maintaining such complex flows easy, accessible, and fully self-service centric.

The Problem

After having built data preparation pipelines to cleanse, transform and enrich data, model designers often need to adjust them at a subsequent time. For example, when migrating from a development to a production environment, the data sources or targets need to be adjusted; after a change in the data warehouse tables are removed, columns are renamed.

Some of the changes may simply involve the changing of input tables, while all other logic and manipulations remain intact. Alternatively, designers may want to adjust a node in the data pipeline and then walk through the downstream adjustments required.

Instead of creating a sequence of events that disrupts the pipeline such that it is rendered useless, Pyramid identifies the adjustments and provides a specialized mapping wizard to walk the designer through the process of rewiring the logic to the changes. Ultimately this maintains all subsequent logic without requiring a full do-over.  The column mapping wizards also let designers correct any variances and issues WITHOUT CODE, sparing the designer the burden of having to recreate all the logic and data manipulations in the model.

Example

This model has been created in a development environment, with a masking process, custom Python code and a Distinct process used for manipulating and enriching the source data.

In moving the environment from testing to production, the server, and subsequent table names have changed in the production environment. The different names used results in an error displayed for the table names.

By simply clicking on the error and selecting the correct table name, the model “validates” correctly. All pre-existing logic downstream in the pipeline remains valid, so the model designer does not have to recreate any of the logic.

If an error exists on a column due to a mismatch or different naming of a column the joins are changed to dotted read links.  By clicking on the link between the table and the subsequent process, a column mapping panel is opened where the model designer can rectify the error. Tools are available to either individually or heuristically map or unmap source to destination columns. After clicking on the apply button and saving the model, the new model mapped to the current datasource is fully operational, without having to recreate all logic from scratch.

Summary

Model designers often perform simply changes like the changing of input tables or adjusting a node in the data pipeline. Instead of disrupting the pipeline and rendering it useless, Pyramid provides a new code-free mapping wizard allowing the designer to rewire the logic to the new changes, sparing the designer the burden of having to recreate all the logic and data manipulations in the model. Saving time and money!

Parent child hierarchies are used to depict the hierarchical, iterative relationship between all members that appear in the same table, enabling a graphic portrayal of the relationships, and facilitating the aggregation of all members according to the hierarchical structure. Reporting on parent child hierarchies is a critical function required in applications that use data structures like a chart of accounts, organizational structures, bill of materials, and many others. Hierarchical reporting for parent child relationships requires specialized iterative capabilities and is usually difficult or impossible to resolve with alternative work arounds. Pyramid’s extensive support for parent-child hierarchies, delivers elegant reporting with full hierarchy expansion and collapse, and provides hierarchical accumulation, directly on supported data sources without requiring data ingestion or data source manipulation.

In the first of this two-part series, we’ll explore how Pyramid’s extensive data model capabilities support parent child hierarchies, and in the next blog we’ll analyze how Pyramid’s querying and visualization capabilities cater for parent child structures, resulting in fluid, intuitive dashboards, and reports.

The problem

The unconventional nature of the parent child hierarchy does not lend itself well to classic reporting and data aggregation. Rolling-up or aggregating all members of a parent child hierarchy in a relational data model requires iterative logic to snake through the hierarchy one item at a time. This places an enormous burden on resources, making it infeasible to process data in its’ native format. As a work around, some solutions involve complex pre-processing to flatten the hierarchy, ensuring each child record contains a distinct parent column for all preceding levels. This forces building additional columns back to the data source; or duplicating data to a new data source. And most often, it produces structures that can go stale quickly, so constant hydration is required – challenging the very notion of “a single version of the truth.”

Pyramid’s solution

Pyramid’s “Model” app provides a code-free, drag-and-drop interface to define parent, child, and descriptive columns in a data model. Pyramid generates the hierarchical structures on the fly, automatically recognizing all levels present in the hierarchy, and generating metadata for each of the levels in the hierarchy dimension. The newly generated data model facilitates direct query on the existing data source, using the parent child hierarchy to aggregate and display data as needed.

In addition, Pyramid provides several options for calculating rollup aggregates within the elements of the hierarchy to meet business logic requirements. So, an operand per member element can be used to determine whether each item is added, subtracted, or ignored when aggregating up the hierarchy.

Specifically for accounting applications, another feature – cumulative measure aggregation – can also be added to a data model that applies accumulation of a measures for balance sheet numerical operations. Numerous complex accounting problems can be solved easily and quickly when both the accumulation and operand functionality is combined.

Business Case

Carla is a BI Analyst at EZ Trading where they have a SnowFlake Data warehouse with Pyramid for their Analytics. Their chart of accounts defines the parent child relationship, with many different levels or reporting, resulting in a typical ragged hierarchy.

For example, this Discover report reflects how the Balance Sheet is the parent of Assets and Assets is the parent of Current Assets. Current Assets is in turn the parent of both Cash and Receivables.

Carla wants to create a data model that will reflect all levels in the financial structure. She used the Model tool to define the parent child relationships - using the AccountKey to define the Child Key, the ParentKey for the Parent, and the Accounts column to be used as the caption.

Next Carla wants to display all values in her chart of accounts as positive, including expenses and sales returns. However, she wants to subtract expenses from revenue to result in a net revenue amount. Carla uses an “operator” column to indicate what to add or subtract when aggregating lower levels - so she simply drops the “Operator” column into the Unary Operator property. Finally, she defines "Children" as the Rollup type to that any parent element is simply the aggregation of child values.

Once the model has been saved, Pyramid instantly generates the hierarchical structure, automatically recognizing the six levels present in the hierarchy, and generating metadata for each of the levels in the Account hierarchy dimension.

Carla then uses her new model in a report to display the relationship between the accounts and all children and descendants.

We see that asset and liability amounts are displayed as positive amounts, but nevertheless, in the background, the unary operator is used to ensure that the balance sheet amount is 0 (by subtracting liabilities from assets).

Carla then wants to accumulate the balance sheet amounts from the company’s inception, so she creates an additional measure for the amount column, using the cumulative method to aggregate the amount. This will allow Carla to display this year’s balance sheet amounts without having to apply any complex calculation to include data not shown on her balance sheet report.

And finally, Carla displays the cumulative balance sheet amounts in a hierarchical tree structure, depicting the annual cumulative effect with simplicity and clarity without requiring complicated manipulations and formulas.

Summary

Parent child hierarchies are critically used in financial and other applications to graphically portray relationships between members.

Hierarchical reporting is usually extremely difficult or impossible to resolve, while aggregating members presents an additional burden in terms of both design and resources. Pyramid’s “Model” app provides a code-free, drag-and-drop interface to define the various elements of the parent child structures on the fly, generating a comprehensive data model, facilitating direct query on the existing data source. Several aggregations are available to meet business logic requirements. Cumulative measure aggregation applies accumulation of measures for balance sheet operations.

The Problem

Cumulative totals are generally required when reporting balances over periods of time for balance sheet accounts in financial accounting. Usually users need to create complex, inefficient models and formulas to replicate the accumulation effect or worse, they manipulate the database into producing raw balancing figures in tables. When done as formulas, the results are constantly recalculated for every cell in every report – further placing tremendous stress on the reporting system.

The cumulative option to aggregate the measure on the granular level ensures the accumulated totals are available and accurately calculated at source.

Better yet, they automatically accumulate across the chosen time hierarchy in the query – so the balance effect by different period models (calendar vs fiscal) is automatically handled without any programming or additional effort from the user. 

Last, cumulative aggregates are the perfect companion feature to parent-child hierarchy capabilities, running directly on any data source surfaced in Pyramid. Together, the two symbiotic functions make the process of building and delivering accounting based analytics accessible, fast, and entirely uncomplicated.

Example

Balance sheet values are always displayed as cumulative values. This report uses the classic context-based cumulative total to provide cumulative totals for all assets. Notice how the amounts accumulates per annum for all amounts existing on the report, so amounts for year 2020 is cumulative from previous years starting in 2017.

When choosing to focus on a specific year – for example 2020, the cumulative effect is lost, displaying only amounts for the year 2020, because the context calculations are driven from the reporting “context” only. Notice how the amounts differ from the previous report.

A new measure – “Cumulative Amount”  - is now added by using the “Cumulative” measure aggregation method in the Data Model tool.

Adding the new measure to the report for 2020, we can see that the cumulative effect is not lost.

By using hierarchy chips for accounts and time, the user can expand or collapse the balance sheet or financial year at will, exposing whatever elements of the tree are relevant for the analysis, so the user can track how the allowance for debt has increased per annum, per quarter and by month.

And finally, by simply swapping the calendar date hierarchy with a fiscal date hierarchy, Pyramid automatically and effortlessly recalculates the cumulative amount and displays all hierarchies in their expandable and collapsible trees. By combining the power and flexibility of cumulative measures with parent-child hierarchies, Pyramid demonstrates it’s superior support for financial analytics.

Summary

Pyramid allows users to aggregate measures on the granular level in the data modelling phase. This cumulative feature enables users to independently view accumulated totals of an existing measure without having to create complex models or formulas that constantly recalculate for all cells. This provides accounting-based analytics with instantly available, accurate totals, automatically accumulating across calendar or fiscal date hierarchies.

 Back to Pyramid 2020.20 release announcement

Model

• Fixed an issue where unexpected results were returned in Discovery when using a diamond-shaped relationship in a model
• Fixed a UI issue where it looked like the Pyramid Even Schedule was removed after closing and reopening the ETL
• Fixed an issue where an error message appeared after clicking on the Undo button and going to the Master flow
• Fixed an issue where using Divide in a Calculated Column had failed
• Fixed an issue that limited the output of a 'Calculated Column' to 255 characters
• Improved error message when using a space in the Target column of a Python script
• Fixed an issue where using an Excel target in a model created folders when it should not
• Fixed an issue where VARBINARY fields got discarded and weren't brought through to the data model screens
• Fixed an issue where an Oracle direct query join had failed

Discover

• Fixed an issue where Hex values of custom colors were overwritten to slightly different values
• Fixed an issue where Viewer users were unable to see the Decedents option in the context menu of a member in case Formulation was disabled in the user's profile
• Fixed an issue where an ODATA link did not open a report that had a dot (.) in the column header
• Fixed an issue where Ask a Question did not render a map in a report
• Fixed a visual issue where filters expanded when using the Hebrew UI and based on specific Windows and browser zoom levels
• Fixed an issue where Include Context in an Action did not work as expected when the user deselected items in the filter after selecting All
• Fixed a performance issue related to using Set Element Order on a hierarchy with millions of items while the elements tree is open
• Fixed an issue where a hierarchy was mistakenly marked as a time hierarchy
• Fixed an issue where a report that was filtered by a background filter and was opened in a pop up didn't show which member it was filtered by
• Fixed an issue where a parameter did not show in a report when used in a switch list
• When using a custom color in Columns/Rows/Data of a grid, fixed an issue where Pyramid didn't indicate which custom color was used
• Added the "Remember user's last selection" option to all filter types
• Fixed an issue an endless spinner appeared when opening a tabular model with an unprocessed dimension
• Fixed an issue where filters with long member names didn't seem as expected when using Hebrew UI language
• Fixed an issue where the URL icon appeared in the grid for a URL column even if the URL value was empty
   

• Queries

  • When using a BW data source, fixed an issue where an error message appeared if the query returned a special character
  • Improved handling of dimensions disconnected from the fact table
  • Fixed an issue where an error message appeared when switching between Time Calculations
  • Fixed an issue where the Rank calculation in SQL produced unexpected results when there were multiple columns in a grid
  • Fixed an issue where a calculation that is based on parameter failed when placed in Filters
  • Fixed an issue where a blank member from an outer join was not visible in the selection tree
  • Fixed an issue where "Bin by size" had failed when the value was equal to or higher than 10 million
  • Fixed an issue where the order of non-alphanumeric characters produced unexpected results
  • Fixed an issue where a switch aggregation caused an enumeration error when placed in 'Combined Elements'
  • Added an option to remove Scope Isolation from the query
  • Fixed an issue where a custom logic that used a parameter returned empty results
  • Fixed an issue where running a very big query crashed the Runtime Engine

• Visualizations

  • Fixed an issue where entering a high From value and a low To value in Custom Scale Limits caused the Axis Labels to disappear
  • Fixed an issue where a data point appeared for a null value
  • Fixed an issue where it was not possible to change back to the previous visual by clicking Undo after switching to an Open High Low Close chart
  • Fixed an issue where a chart legend showed eliminated items
  • Optimized pie chart labels visibility

Present

• Fixed an issue where a Dynamic Text that was placed in the Content master page was not updated as expected
• Fixed an issue where some slides did not appear in the Presentation Launcher depending on the Windows and Browser zoom levels
• Fixed several Cross Model Mapping issues where a filter returned unexpected results after selecting all items
• Improved Cross Modell Mapping performance
• Fixed an issue where a "The following parameter has no input" message appeared when using an interaction between a parameter-driven calculation and a grid
• Fixed an issue where an input parameter was not found in a component that was added to a slide and causing the component to fail with a General Error
• Fixed an issue where a parameter in an interaction was unselected after copy/paste
• Fixed an issue where a presentation was corrupted after saving it while editing an unsaved one-off illustration
• Fixed an issue where a disabled cascading filter was still being used in the query
• Fixed an issue where clicking on Reset in a slide cleared a Dynamic Text value that was injected using a Target
• Fixed an issue where selecting All in a cascading filter returned unexpected results
• Fixed an issue where deselecting items after selecting All in a filter and then using Analyze Further opened the report with All selected in the filter
• Fixed an issue where the 'Tooltip Designer' dialogue was cut off on a screen with 1366x768 resolution
• Fixed an issue where Cascading filters with highlight interaction failed to load

Publish

• Fixed an issue where a Placeholder did not populate in a Subscription
• Fixed an issue where the execution list of a schedule could not be sorted by date
• Fixed an issue where a Publication schedule ran twice when it was expected to run once
• Fixed an issue where a scheduled publication failed when the publication contained a report based on a 'SAP BW' data source and using 'Logon Tickets' as the auth type
• Fixed an issue where a scheduled job name that was saved in Hebrew appears as ???
• Fixed several issues where a schedule didn't run at the correct time or didn't run at all
• Fixed an issue where the content was attached to Subscription email although the license did not have this option
• Fixed an issue where a schedule didn't run if a trigger launch condition contained a Null
• Fixed an issue where a scheduled Publication didn't run if the schedule had an empty filter

Formulate

• Fixed an issue where clicking on Refresh Metadata after selecting a 'Named Set' in a list showed Member Not Found as the selection
• Fixed an issue in the List editor where after clicking on one of the items in the Selected panel, it did not appear in the Select Elements panel
• Fixed an issue where the 'Contains' function failed when it used a level that is ordered by a different column
• Fixed an issue where Search Elements within a formulation did not return results unless the hierarchy was expanded
• Fixed an issue where a Dynamic Caption did not update as expected
• Fixed an issue where unselecting a single member in a formulation resulted in several members being removed
• Fixed an issue where a Filter node did not appear in List when using German UI language
• Fixed an issue where formulations had failed for members with long names when column type was Decimal on IMDB

Illustrate

• Fixed an issue where an Illustration showed an incorrect color
• Fixed an issue where renaming a one-off illustration had failed
• Fixed an issue where Pyramid did not correctly indicate the font type of a Dynamic Text
• Fixed an issue where it was not possible to create a dynamic text based on a report with BW parameters
• Fixed an issue where Strike Trough didn't work for some text boxes
• Fixed an issue where a Dynamic Text was removed after editing it without saving

Content Management

• Fixed an issue where copy and pasting content from My Content into Workgroup content didn't add a role
• Fixed an issue where the 'Search' did not return results for users from a non-default tenant searching for cross tenant enabled content located in the default tenant
• In the Data Source Changer, it is now possible to apply the "Duplicate" and "Cascade to Usages" setting to all items
• Fixed an issue where the Migrating Wizard did not use the Proxy Settings, configured in the 'Admin Console' under 'Networking'
• Fixed an issue where a "Request Error - Parallel process interrupted" message appeared when running Analyze Structure
• Fixed an issue where Structure Analyzer didn't find errors in nested calculations
• Fixed an issue where Hierarchies selection in the Runtime setting of a Presentation defaulted back to all after an import
• Added a 'Cancel' button to the spinner, which appears when opening a report
• Fixed an issue where models appear more than once in the Data Source Changer
• Fixed an issue where presentation data sources changed incorrectly after an import
• Added an option to select between creating new copies of the content or replacing content when using the Migration tool
• Fixed an issue a NullPointerException error appeared after deep pasting a Present
• Fixed an issue where content that was removed from a presentation was re-created in a folder once the presentation was copied and deep pasted
• Fixed an issue where migrating content to 'Workgroup Content' created a folder in the top-level which couldn't be removed

Printing / Export

• Fixed an issue where using 'Copy Raw Data' on an extensive report resulted in an Out of memory error
• Fixed an issue where column headers were cut off in a print when using a custom font
• Fixed an issue where Custom Visual printing did not work when loading external libraries
• Fixed several issues where printing had failed
• Fixed an issue where a large grid failed to print with a "The number of Cell Styles was exceeded" error
• Fixed an issue where exporting a report with more than eight attributes on rows/columns had failed
• Fixed an issue where the font size in a PowerPoint print was different than the one in the one set in Pyramid
• Fixed an issue where only one chart was exported to Excel when exporting a Presentation with a trellised chart
• Fixed an issue where empty columns appeared in an Excel export
• Fixed an issue where a Custom Caption did not appear on a scheduled print
• Fixed an issue where a duration column (hh:mm:ss) was formatted incorrectly when printed to Excel
• Fixed an issue where the Excel sheets appeared in an incorrect order when printing a Present
• Fixed an issue where a Present print from one slide had a slicer selection from another slide in the file name
• Fixed an issue where after copying 'Raw Data' with the <> sign and pasting in an Excel file, the <> did not appear
• Made German localization improvements in the 'Paper Type' dropdown

Admin

• Fixed an issue where logs were exported partially
• Fixed an issue where a filtered User list only returned items from the current page
• Fixed an issue where the second page of the transaction logs appeared empty
• Added a new Member Security Cache timeout option
• Fixed an issue where the Pyramid logs weren't cleared based on the pre-defined interval
• Fixed an issue where a custom theme had caused slowness in Pyramid
• Fixed an issue where Publication Schedules did not appear in the Admin after an upgrade
• Fixed an issue where Provisioning sometimes failed with a "Snapshot isolation transaction aborted" error
• Fixed an issue where it was not possible to update a non-admin SAML username
• Fixed an issue where the Conversation button was not removed in Viewer mode when Workflows (Social Collaboration) was disabled
• Fixed an issue where canceled items appeared in the Schedules > Models list after selecting to hide inactive items
• Fixed an issue where it was not possible to delete IMDB databases
• The Display name of a data source was increased from 36 to 255 characters
• Fixed an issue that caused the license upload to fail
• Fixed an issue where an incorrect number of licenses appeared under 'Licensing'
• Fixed an issue where sending an email via AWS had failed with a 'Could not convert socket to TLS' error on a k8s environment

Security

• Fixed an issue where a login screen appeared when logging into Pyramid using SAML ADFS Windows Authentication
• Fixed an issue where the restrictive Profile permissions were applied to a Role in case both restrictive and permissive Profiles were assigned to it
• Fixed an issue where, after switching from AD Authentication to SAML Authentication , a user was unable to log in if it had the same username as one of the AD users
• Fixed an issue where the SAML setup Test button didn't validate the SSL certificate

API

• Fixed an issue where dataSource/recognizeDataBase did not validate the DB name
• Fixed an issue where 'API2/access/createTenant' did not provide an error message when failing due to an already existing tenant
• Fixed an issue where 'API2/tasks/findSchedule' returned spooled run jobs in addition to scheduled jobs
• Fixed an issue where 'API2/tasks/runSchedule' logged two entries for the output of the same task
• Fixed an issue where 'API2/content/getFolderItems' returned fewer items than expected
• Fixed an issue where 'API2/content/import content did not return anything in the 'importDscMap' property

Installer

• Fixed an issue where adding AI service to a multi-server installation had failed
• Fixed an issue where the initial username was saved incorrectly in the DB when using Russian characters on Windows installations
• Fixed an issue where installing Kubernetes using RDS PostgreSQL failed if the username had an @ in it
• Fixed an issue where unticking 'Auto Login' caused the Kubernetes deployment to fail

Mobile

• Fixed an issue where the Scroll position was changed while scrolling a presentation in the mobile app
• Fixed an issue where the Play button in the motion bar did not work in the iPhone app

Other

• Made several corrections to the online help
• The license expiration notice will now only appear for Enterprise Admins

Installer Download

For example, in a company where salespeople are assigned to different regions, one might be able to view customers in the North, another can only view customers in the South, and a third, the manager, can view both regions. As security requirements often have more complex rules governing access policies, a flexible, high-performant approach is required to resolve the different scenarios. Complicating things, few database engines offer a comprehensive solution for enacting row-level security. This goes from bad to worse in organizations that use multiple technologies—where BI managers find themselves enacting multiple security regimes.

Pyramid’s comprehensive security framework provides solutions for these types of issues—including security controls for access to functionality, content and reports, databases, data columns, and ultimately data rows. The entire framework pivots off a user/role-based model that can be designed centrally and applied to any supported database using point-and-click tools, with a common security model.

For row- or member-level security specifically, this can be amped up significantly with a more scalable approach, where row-security rules can be materialized using tables and values in the database itself to deliver a fast, efficient mechanism for implementing row-level security.

In this blog, I am going to focus on the most complicated type of security implementation: row-level security.

The problem

Without applying access security, users can view all content and data in the database. In a multi-user or multi-tenant database this is not acceptable since most projects require users to only see authorized data and content.

While many tools often offer functions suitable only to specific levels of expertise (professional, intermediate or beginner), access to reports, databases, and even specific data columns should be controlled by security depending on users or roles. Row- or member-level security enables restrictions on data row access and it’s usually the most essential layer of security in realistic deployments.

Some BI tools deploy a basic security model that only caters for simplistic requirements, while other BI tools do not apply the security filtering on the query, but rather query all the data and only apply the security filtering on the results—an inefficient mechanism. There are some tools that offer the mechanics but are very unscalable when applied to thousands of different users, each user with their own security profile. And then there are many other tools can only offer row-level security on their own proprietary database technologies. Last, applying security at this level requires an enterprise approach that cannot be achieved in a desktop environment—which then makes many desktop-based technologies difficult to secure.

Pyramid’s solution

Pyramid’s security framework is designed so that administrators can deploy security operations centrally and then apply it to one or more data technologies to restrict access to functions, content, databases, data tables, columns, and most critically: data rows. For row-level security, Pyramid provides both basic and data-driven options.

The access restriction logic is always incorporated into the final query, ensuring complete conformity between all aggregated and detailed reports. The same report can then display different results depending on a user’s profile.

In a basic row-security scenario, using simple point-and-click commands, administrators can assign hierarchy members explicitly to users or roles. This can be done for one or more hierarchies, and to set security on entire hierarchies and measures. However, this is inconvenient if there are hundreds or thousands of security permutations for users and roles. In this case, a data-driven framework becomes a necessity.

A typical example of this is where a user must be granted viewer permission to all transactional data in one or multiple regions. The regions exist as a column in the customer hierarchy, which in turn is related to the transactional sales data. By mapping the user with one or more regions in a separate security table, access can be controlled through the relationships between the security, customer, and sales transaction tables.

So, by identifying the user using a standard scripting function, all subsequent relationships in the data model are injected into the query, ensuring all security rules are applied within the query. By designating the mapped user table as a security table, non-administrative users cannot access the table and can’t use it to build reports and queries.

Best of all, the security mechanics in the data-driven approach are highly scalable, using the database’s inherent capabilities to resolve who should see what without any tremendous memory or querying overhead.

Business case

Galina manages ILT’s enterprise security for three thousand users. They use SAP HANA for their data warehouse, a Snowflake database for their web logging, and Pyramid for their analytics. Galina adds a new data model for customer sales, where Galina applies security to ensure that customer sector users can only view customer data from their own sector. Several users belong to multiple sectors. A mapping security table has been created that defines which sectors each user is able to view. James, a manager, has access to retail, corporate, and commercial sector customers. Donette is a sales representative for both corporate and commercial sectors and Kris works only in the commercial sector.

In this example, the Security table is linked to the Customers table via the Customer Segment, and the Customers table is linked to the Sales Transactions table via the CustomerKey This chain of linked tables enables a user’s view to be automatically filtered by the system-controlled Userid of the logged in user.
 

This will ensure that each user, when logged in, will only be able to query data pertinent to their defined customer segments. For example, James will be able to query records 1 to 3, Donette will only be able to query records 1 and 2, and Kris will only be able to query record 3. No users will be able to query record 4 as none of them have been assigned to the Government sector.
 

Galina maps the Security Table in the data model and designates it as a security table. Galina also links the Customer Segment in the Security Table to the sector in the Customers table.

Galina must now apply security functionality to the Security Table, identifying the logged-in user and linking the user to the Userid member in the Security Table. By doing so, the security parameters will be injected into the query at run time, ultimately linking the fact table to the logged in user. Galina adds a user role from the data source manager in the Admin console that will be used by all these users. She then assigns PQL logic to the Userid hierarchy from the security table. The one-line PQL code utilizes the string to member function to convert the username (derived from the logged-in user), returning a data “member” with the current Userid from the Security Table. The corresponding Customer Segment(s) links to the associated segment in the Customers table, further connecting to all related transactional data to ensure robust member-level security.

In effect, the PQL function dynamically changes the resolved data member used in the query joins based on the user that is logged in. So, when James logs in, it resolves to “[Security].[UserId].[James]” and when Kris logs in it becomes “[Security].[UserId].[Kris]”. These are then inherently used in any queries to filter the underlying tables and secure the data and results.
 

When James views the Sales report, he views totals for all records belonging to the retail, corporate, and commercial segments. When Donette views the same report using the same data model and the same role, the report only displays data from corporate and commercial segments. Kris only retrieves totals for the commercial segment when viewing the same report.

James’ view

Donette’s view

Kris’ view

Summary

Pyramid’s comprehensive security framework provides solutions for controlling access to functionality, content/reports, databases, data columns, and ultimately data rows. The entire framework pivots off a user/role-based model that can be designed centrally and applied to any supported database using point-and-click tools, with a common security model.

Row-level or Member-level security is a critical requirement in analytics, ensuring the right data is accessible to the right people from the highest level of detail to the lowest. A flexible solution is required to cater for the complex security rules governing access policies, often difficult to implement in row-level scenarios. Many BI tools only cater for basic requirements and are inefficient when deploying member-level security. Other tools either apply query algorithms, use unscalable solutions, or only offer row-level security on their own proprietary databases.

Pyramid’s row-level basic security framework allows easy point-and-click security assignments. The more advanced, scalable approach for larger security scenarios uses tables and values in the database in conjunction with Pyramid’s Query Language (PQL). Apart from being scalable and efficient, the entire framework pivots off a user/role-based model that can be applied to one or more supported databases or technologies using the same administrative tools and system.

This post originally appeared at https://www.pyramidanalytics.com/blog/details/blog-implementing-row-level-security-on-any-database

Other Resources

Member-level Security - Help

Dynamic-member Security - Help

Dynamic Member-Level Security Video

In my previous blogs, I demonstrated how Pyramid provides automatic date parameters to interrogate different data slices at will; how to add intelligent calculations in a few clicks; and how to edit and adapt them. In this last post, I show how a user can vary the date range of the data being analyzed, offering him full control and flexibility over how the data is displayed. This is a critical element of time intelligence data analysis that is often missing in comparable BI applications.

Business case

Paul, the data analyst at Intensive Health Care from my earlier post, would like to choose a date for his report and dynamically select the start and end dates for his date range selection.

Current report

Paul creates a “from” and “to” date parameter (parameters are simply a predefined list of items that can be injected into another function). He then uses them inside the “Range List” function in the List designer (located within the Formulate module). This function asks the user to designate a range of elements from a start element (“from”) to an end element (“to”). The list engine then creates a functional listing of items between the two points—perfect for building a range of dates.

New report format

Paul now has a fully dynamic date range report that will give users full control over the dates shown in the report. As you can see in the image, after selecting June 2018 through February 2019 from the two parameter drop downs, the previous report changes to just the monthly data between the two date selections.

Conclusion

In this series of blogs on time intelligence, I have briefly covered Pyramid’s tools for date-time analysis. The automatic date-time grouping function is available during data modeling (ETL) and also on-the-fly (See “Time intelligence: Date-time groupings for better visibility”). The advanced formulation tool for calculating date-time calculations (year-to-date, month-to-date, etc.) are available as ready-built functions (See “Time intelligence: Date-time calculations made simple”) and can also be used in building more advanced user-defined functions (See “Time intelligence: Custom calculations for greater perspective”). In this final blog, we have seen how Pyramid offers powerful tools to create parameters and date range functions that gives users pinpoint control of date ranges.

The principle of both providing immense power to the business user, combined with the speed and ease to create dynamic functions, are the hallmark of Pyramid’s approach to bringing powerful BI functions to analytics.

This post originally appeared at https://www.pyramidanalytics.com/blog/details/blog-time-intelligence-how-date-lists-and-ranges-accelerate-insights

Other Resources

Time intelligence: A critical element in all analytic projects - Blog

Time intelligence: Date-time groupings for better visibility - Blog

Time intelligence: Date-time calculations made simple - Blog

Time intelligence: Custom calculations for greater perspective - Blog

Range List Help

Parameters Help

Example: Date Range Parameters Help

My previous posts on time-intelligence covered Pyramid’s powerful and automatic date-time calculation creation capabilities. We saw how users can create custom calculations over a range of dates, and then compare results with other periods. We also saw how Pyramid provides one-click wizards and menus to create effortless date-time calculations. Collectively, these tools provide a comprehensive, ready-to-go solution for non-technical users facing common analytical problems.

Despite the utility of Pyramid’s built-in time intelligence capabilities, users often need to create custom date-time calculations. In most BI tools, if the user has specific needs, it is almost impossible to achieve the exact result.

Pyramid provides two foundational tools for creating custom calculations: “lists” and “formulas.” Using these two capabilities we can build and add any type of date-time logic needed for analytics.

In this post, I will provide an example and outline of these tools and show how they can be used in date-time calculations. While these are advanced capabilities, they demonstrate how complex analytical logic can be implemented with Pyramid.

Date-time formulation tools

A general approach to date-time formulation requires the definition of three elements: a group of items on which to perform the calculation; a date that defines the grouping of the items; and a measure on which to perform the calculation.

A classic example of this is month-to-date sales, which we will illustrate below. In our example, the group of items includes all transactions that occurred during the month. The date is an input used to define the day in the month which will be used to define when the transactions occurred. And the sales amount is the measure used to quantify the transactions.

Custom lists

You can create dynamic lists (or “sets”) with Pyramid’s graphic list designer. For example, we can use the Month to Date (MTD) function to create a group of items that are all dates in the month up to the selected date. We can further enhance the dynamic MTD calculation by using a date parameter—which will allow us to dynamically pick the end date for that range—and change the dates used in the MTD logic. (“Parameters” are simply a predefined list of items that can be injected into another function.)

Custom formulas

The formula tool is used to create calculated values (as opposed to lists of items). Pyramid’s Formulate module has a graphic drop-and-drag user interface that enables you to build custom date-time formulas. For example, a user can sum all expenses incurred during the current year by creating a formula that automatically calculates the year-to-date (YTD) total for different metrics in the data set.

Business case example

Paul, a data analyst at Intensive Health Care, has produced a report that compares current month sales metrics with other period metrics. In addition, his CFO has requested a view of these figures from two years ago. This calculation must appear in the same report and all analyzed data should include this calculation.

Paul’s current report looks like this: he has MTD, WTD, QTD, and YTD calculations that are driven by a date parameter—currently set to November 15, 2019. This effectively calculates all sales in November through the 15th; then all sales in the quarter through to November 15th.

Now he needs to duplicate his base YTD calculation (created with the Formulate tool above), rename it as “YTD Previous -2,” and use the “Add Years” function to subtract two years, as seen in the highlighted text below. (Using a negative number with “Add Years” is effectively subtracting those years).

Adding this calculated member will result in an additional “YTD Prev -2” column, as shown in the table below. This column now displays YTD figures for January 1, 2017 through to November 15, 2017 (pivoting off the selection of November 15, 2019). More impressively, it does it for all metrics in the report, not just Sales.
 

Key takeaways

As you can see, Pyramid provides powerful tools to create custom lists and formulations that comprehensively extend basic time-date calculations. These advanced calculations let users examine data from non-traditional perspectives. In my last post in the time intelligence series, I’ll cover how Pyramid offers incredible control over selecting date ranges.

This post originally appeared at https://www.pyramidanalytics.com/blog/details/blog-time-intelligence-custom-calculations-for-greater-perspective

Other Resources

Time intelligence: A critical element in all analytic projects - Blog

Time intelligence: Date-time groupings for better visibility - Blog

Time intelligence: Date-time calculations made simple - Blog

Time Intelligence: How date lists and ranges accelerate insights - Blog

Aggregate (PQL) Help

YTD (PQL) Help

Add Years (PQL) Help

Create a formula in Formulate Video

As covered in my previous blogs on time-intelligence, adding date-time groupings into your data makes the most basic analysis and reporting of data functional. Depending on your business, seeing sales by month is generally far more functional than seeing it by day. Understanding quarterly profit is easier to digest than seeing it week by week.

Grouping data into logical time periods is helpful but does not always provide the full story. Business users are looking to understand the changes in values over time, not just static data snapshots, which usually requires clever calculations.

Date-time calculations

It’s common for a user to want to compare calculated date values over a range of dates against similar date ranges For example, a user may want to compare the difference in quarter-over-quarter sales,. Another user may want to compare year-to-date expenses with previous year-to-date expenses. The creation of these dynamic date-time calculations plays a critical role in the analytics platform. And once they have been made, they should be reusable in many dashboards and reports.

Pyramid’s analytic tools and query language functions allow date-time calculations to be easily built, covering an almost limitless set of calculation varieties. These capabilities include a freeform function editor to build any type of required logical expression for advanced users and one-click wizards and menus for non-technical users—to auto-generate the different types of logic without code or complexity.

The power of this approach allows these calculations to work in almost any query configuration—across any other dimensions and applicable to any and all metrics. And like all shared business logic elements in Pyramid, once these calculations have been set, they can be reused in multiple dashboards and reports.

Business case

John, the CIO of Intensive Health Care, would like to analyze his sales, net profit, expenses, overhead costs, and quantity. His analysis of several time views periods for the above metrics in a single dashboard will give him a bird’s-eye view of all key metrics for year-to-date, quarter-to-date, month to date, and week-to-date amounts.

The challenge

Using Power BI, John is required to separately write code for each metric, multiplied by the number of time-period calculations required. Using our example, this will mean 20 different sets of code (five metrics times four calculations). Yikes! Worse, they are hard coded to the current date only. Clearly not a BI analyst’s dream solution.

DAX example of coding for a single MTD calculation on sales

so MTD :=
IF (
    HASONEVALUE ( Dates[ISO Year] )
        && HASONEVALUE (Dates[ISO Month Number] ),
    CALCULATE (
        SUM ( Sales[Sales Amount] ),
        FILTER (
            ALL ( Dates ),
            Dates[ISO Year] = VALUES ( Dates[ISO Year] )
                && Dates[ISO Month Number] = VALUES ( Dates[ISO Month Number] )
                && Dates[Date] <= MAX ( Dates[Date] )
        )
    ),
    BLANK ()
)

Pyramid’s tidy, codeless solution

Pyramid, on the other hand, offers a quick, codeless solution to add all above time intelligence variables in a single click using the “Time Intelligence” wizard in the Discover app—shown below.

Once the calculations have been created and added to his report, John can continue adding as many metrics independently without requiring any further coding. In fact, he can select all the date-time calculations in one step, allowing him to view and analyze them in a single visualization.

If he elected to parameterize his logic, he can also choose the date that will drive the date-time input selections for determining the WTD, MTD, QTD, and YTD date ranges—making both the use of the calculations and their logic highly flexible at the same time.

See how all the calculated values for WTD, MTD, QTD and YTD change if we change the date in the parameter below:

This ability to create multiple metrics for various time periods in a few seconds instead of a few hours is a solid example of the power and flexibility of Pyramid’s calculation engine. To further boost the capabilities, advanced users can build fully customized date-time calculations using the underlying functional and querying logic. I’ll cover this in my next blog.

This post originally appeared at https://www.pyramidanalytics.com/blog/details/blog-time-intelligence-date-time-calculations-made-simple

Other Resources

Time intelligence: A critical element in all analytic projects - Blog

Time intelligence: Date-time groupings for better visibility - Blog

Time intelligence: Custom calculations for greater perspective - Blog

Time Intelligence: How date lists and ranges accelerate insights - Blog

Time Calculation Wizard Help

Using the Time Calculation Wizard Video

In my previous blog post, I introduced the concept of grouping amounts in data periods by adding date components to your data to make the “bucketing” of information by date-time period easier and deliver more effective analytics. In this blog, I’ll explain this process in more detail.

Adding date-time buckets

To add “buckets” or groupings, we need to break down the date into all its essential properties: day, week, month, quarter, year, and all the various combinations used in reporting. Pyramid achieves this with two complementary methods: by either physically storing the derived groupings of date-time in the database as part of the data preparation process (technically called “materialization”) or by virtually adding these time groupings as calculations—determined dynamically when used.

Both methods have their purpose and are used in different circumstances. Having both options will give you tremendous scope for solving your analytic and reporting challenges easily.

Storing date-time groups in the database

Let’s begin with the physical storage approach. Users typically choose to physically store date-time groupings in the database for performance reasons, and because it offers greater flexibility when designing and configuring the various groups or buckets. In Pyramid’s “Model” application (where data is cleansed or prepared) tools, the “Time Intelligence” node allows users to add this capability in two clicks [see Pyramid Help for more details]. This lets a user choose which time groupings to use and select when different time periods should be calculated from (for example “fiscal” years, etc.).

Once the node is set into a data flow, new columns are generated as shown below in the “before and after” image.

Alternatively, users can manually create custom date-time groupings using the “Calculated Columns” node and the vast date-time PQL function library. Yet another option involves using R or Python scripting to build out highly sophisticated time logic. These are more advanced choices, which I won’t cover here.

The drawback of this approach is that the results need to be stored in the database—or “materialized.” This poses issues when the data source is read-only, or if the data model is meant to query the database on the fly.

If this is the case, the second option comes into play.

Virtual date-time groups: Calculations on the fly

An on-the-fly solution is ideally suited when sourcing data from a data source when it is not possible to save a new derived column back into the database.

Pyramid’s Discover tool provides simple right-click functions to create and add a virtual time grouping to your data model that can be dropped and dragged into any report like its “stored” cousin.

Once the “year” option is selected, a virtual year column is created, as shown below in the “before and after” image.

While the virtual approach can offer the same outcomes as the stored approach, it comes with a few drawbacks. First, on very large datasets, its performance can be slower. Also, there are less options for creating complex time periods and setting the logic for each grouping in a different way (like “fiscal” periods).

In the end, it’s up to users to choose the method that suits them and their data, limitations notwithstanding.

MDX models vs. SQL models

The last comment in this blog covers some technical minutia. Unlike SQL-based data sources, MDX-based data models (like Microsoft OLAP or SAP BW cubes) generally should have the date-time groupings designed into the cubes natively; since it’s often impractical to add them afterwards. This does not apply to time-based calculations (like “year-to-date” or “year-on-year”)—which will be covered in my next blog.

This post originally appeared at https://www.pyramidanalytics.com/blog/details/blog-time-intelligence-date-time-groupings-for-better-visibility

Other Resources

Time Intelligence: A Critical Element In All Analytic Projects - Blog

Time intelligence: Date-time calculations made simple - Blog

Time intelligence: Custom calculations for greater perspective - Blog

Time Intelligence: How date lists and ranges accelerate insights - Blog

Date Parts Help

Time Intelligence Help

Date-Time Calculations Help

Date-Time Hierarchies Help

The two core challenges

Date-time groupings and “buckets”

Central to most analytic systems is the requirement to apply time to an analysis or report. The first and usually simplest component is to view amounts grouped in date “buckets” or periods. We can usually achieve this after identifying the existing date-time field on a data set—which is usually on the “grain,” or transactional detail of the stored data in the database. Once we have this date element (or elements if there are multiple dates), we can use time as a key element in all reporting.

To illustrate this, consider a grid of sale amounts per transactional date that has been converted to a column chart.

Seeing the data in this example at the individual day level is too granular. We need to add logical super groupings of dates and times to allow the reporting to show more useful period buckets. Grouping amounts into date periods by adding new columns to the data set will allow us to add time intelligence to the original data set.

Using our example, it is difficult to discern performance over the course of the year. One obvious approach is to total the results by month, which we solve by creating a separate “bucket” for each month and summing all sales by each month. From the visual below, we can now see the monthly grouping, a much clearer picture of our sales performance for that year.

Creating multiple buckets for different date and time scenarios will enable us to analyze our data with more flexibility and clarity.

Date-time calculations

In the next stage, let’s assume I want to see the sales totals for 2017 and compare them to 2018. Here, I not only do I want to see the data bucketed into annual figures, I also want to see the absolute difference and the percentage change between the annual numbers.

This is the second component of time intelligence: creating calculations over a range of dates and comparing them against previous periods.

Pyramid’s time intelligence armada

The above scenarios are simplistic but cut to the heart of what most analyses need to repeatedly do: visualize data over time. However, what seems a simple task can be difficult with the wrong analytics tool. Indeed, the challenge of making all logical elements of dates and time can become overly complicated or unworkable. Many BI solutions require users to contort data unnecessarily and manipulate report designs to accommodate complex date and time logic, or leave the user with a handful of clumsy options that force them to do the “clever” stuff in offline tools like Excel.

Pyramid includes an array of tools, wizards, and functions to solve both sets of time-intelligence challenges. The capabilities are highly effective and user-friendly, and work for all data sources and query languages supported by Pyramid (MDX and SQL)—covering the basics all the way through to highly sophisticated functionality.

The next set of blogs in this series will cover these elements in greater detail.

This post originally appeared at https://www.pyramidanalytics.com/blog/details/blog-time-intelligence-a-critical-element-in-all-analytic-projects

Other Resources

Time intelligence: Date-time groupings for better visibility - Blog

Time intelligence: Date-time calculations made simple - Blog

Time intelligence: Custom calculations for greater perspective - Blog

Time Intelligence: How date lists and ranges accelerate insights - Blog

Date-Time Calculations Help

Date-Time Hierarchies Help

Differences between two metrics are very commonly used to analyze period-on-period changes. Calculating differences often highlights other issues that require further investigation.

The problem

A business user is reviewing monthly sales figures and would like to see the “ups” and “downs” in the numbers as the year has unfolded. In order to achieve this, he would like to see the movements in sales represented in a data visual that is easy for him to grasp the trend.

Waterfall chart

In our example the business user might want to graphically depict the differences in a waterfall chart that is a great visual to see movements highlights while showing increasing amounts in green and decreasing amounts in red.

The headache found in most BI tools

In most BI tools, this involves tricky coding that requires multiple, complicated, and time-consuming steps. For example, building differential values and waterfall charts in Power BI can be a long and challenging process. Worse, the solution offers no flexibility and hard-codes elements of the query.

With Pyramid you can create an additional calculated difference column in a simple, one-click step with no code required—while still leaving the user the capability to change the calculations intact.

Metric differences in a single click

With a single right click on the sales metric, we can choose a differential calculation.

And the period-on period sales difference is instantly shown in our grid visualization.

With one more click, we can use Pyramid’s out-of-the-box waterfall chart to draw both the movements and the color of the movements in a graphic.

Conclusion

With Pyramid, a user can create a difference calculation that:

• Does not need developer assistance
• Only requires a few clicks
• Can use the same definition without requiring additional definitions when he decides to analyze by different period type (or any other dimension)

Pyramid provides a highly versatile, powerful, code-free and user-friendly tool for calculating numerous business analytic applications and reports.

This post originally appeared at https://www.pyramidanalytics.com/blog/details/blog-calculating-differences-without-code

Other Resources

Stop Writing Code: Running Totals Made Easy - Blog

Pareto Analysis: Using the 80/20 Rule - Blog

Waterfall Help

Context Calculations Help

Waterfall Charts Video

Waterfall Settings Video

An Italian economist by the name of Vilfredo Pareto observed that 80 percent of the land in Italy belonged to 20 percent of the population. In his research, he found that this rule applied in many diverse domains. Since then we have used his principle when trying to find patterns in our businesses and in our lives. With the explosion and availability of data and analytic capabilities we are now more capable and empowered to find more and more of these 80/20 nuggets that give us actionable insights.

Pareto implementation challenges

Most modern BI tools do not allow for easy, code-free methods of creating flexible calculations like running totals (see previous post). And like running totals, the Pareto analysis requires the calculation of cumulative percentages of totals. Such complexity and difficulty in implementing basic formulations make the delivery of comprehensive analytics challenging for most end-users.

To gauge such complexity, it’s worth looking at the steps needed to add Pareto in competing solutions:

• Power BI
• Tableau

In contrast, Pyramid offers a one-click solution to adding Pareto metrics to your analyses that will be usable in any visual.

Identifying your best customers: Pareto in one-click

Apex Cycles wants to identify their top customers that account for most of their sales for the current year. Furthermore, they would like to identify which products and branches contribute towards most of their sales. How can we use Pyramid to improve, simplify, and speed-up this analytical process?

First, we start by building a simple column chart of sales by customer.

Then, by simply right clicking on the Sales metric, we can instantly add a Pareto metric based on sales that automatically show cumulative sales as a percentage of the total.

For added clarity, we put this Pareto metric on the secondary axis as a spline chart. And the Pareto report is complete! Uncomplicated, intuitive, and entirely dynamic.

Reading from this report, we can see that the first five customers account for 80% of all sales.

Focus on products

Now if we want to complete the same analysis for products instead, we can simply swap out customers with product and instantly get the new analysis without any further manipulation or code.

Here, we can see that four products account for roughly 80% of sales.

Conclusion

With Pyramid the user can create a Pareto analysis that:

• Does not need any developer input
• Only requires a few clicks
• Can instantly re-use the same function even after changing other selections in the report

Pyramid is a highly versatile, powerful, code-free, and user-friendly tool for creating business calculations like Pareto.

This post originally appeared at https://www.pyramidanalytics.com/blog/details/blog-pareto-analysis-using-the-80-20-rule

Other Resources

Stop Writing Code - Running Totals Made Easy - Blog

Calculating Differences Without Code - Blog

Pareto Help

Context Calculations Help

The problem

Consider the following business problem. A business user sees monthly amounts of sales and would like to track when important milestones have been reached. In order to achieve this, he would like to see running totals of sales so he can track when a $1m milestone was achieved. Due to the ad-hoc discovery nature of his investigation, he might also want to track this on different levels of granularity, such as by quarter, by month, or by week.

In the image below, the user is not able to see when the $1m, $2m and $3m milestones were achieved.

Creating running totals is often part of an ad-hoc reporting exercise that requires finicky coding with a dependence on developers and the associated software development lifecycle. In Pyramid, however, you can create running totals in a simple one-click step—no coding required.

The headaches found in most BI tools

For each calculation the user will need to write code specific to the combination of dimensions and measures he wants to view in his visual or report. This is normally beyond the capabilities of most consumers, and even some proficient end-users will require assistance from an experienced developer. This introduces the common time-to-result lag of specifications, documentations, development, testing etc. In our example, the developer will need to create three different variables for company, salesman and branch, each with three different time options for quarter, month and week, resulting in nine separate variables, each requiring their own code. YIKES!

Here’s a sample of DAX code for a running total (this would have to be copied and adjusted nine times):

At this point, business users will either spend a large amount of time creating all the variables and code required for this solution, or decide that it is not worth the effort and instead use off-line tools like Excel that destroy the centrality of analysis and reporting.

Running totals in a single click

With a single right click on sales metrics in Pyramid’s Discover tools, the user can add a dynamic “Cumulative Total.”

And when you swap out months or any other hierarchy or add additional hierarchies, the running total is automatically recalculated, no further coding required.

Conclusion

With Pyramid the user can create a running total that:

• Does not need any developer input
• Only requires a few clicks
• Can instantly re-use the same function, even after changing other selections in the report

This post originally appeared at https://www.pyramidanalytics.com/blog/details/blog-stop-writing-code-running-totals-made-easy

Other Resources

Pareto Analysis: Using the 80/20 Rule - Blog

Calculating differences without code - Blog

Context Calculations Help

Cumulative Context Functions Help

Context Calculations Video

How to share comments on data that appears in many reports

How can I collaborate with users I don’t know—who are using reports that I don’t know about—using a shared analytics and BI platform? Pyramid provides a solution that enables me to effortlessly collaborate with all users accessing shared data models, regardless of their access to specific reports or dashboards.

The power of annotating data models without attachment to specific reports

Without Pyramid’s solution, I would first have to source any and all reports that access this data point. Alternatively, I could provide comments in a specific report knowing that any other variation of this data in another report or dashboard would not include the commentary. It’s common for a user to abandon the task because of its complexity and difficulty. The organization is left all the poorer for its inability to share insights and collaborate on data and analysis.

Use Case

The following scenario illustrates Pyramid’s commenting capabilities:

• John, the CFO for Apex Bicycle Company, sees the sales figures for the “Mountain-200 Black bicycle, 46” for the Manufacturer Woolson is down to zero after three consecutive months of positive sales from January to March 2019.
• After an initial investigation, he wants to alert all users that there is an issue with the vendor, and they should be aware that this could impact future sales in other states.
• He knows that there are no other users that view his report, but there are three additional departments (Sales Department, Product Department and Procurement Department) that track sales, including the mountain bike referenced above. In some cases, numerous users in each department are running between 10 and 20 different sets of analysis for each department.
• He would like all users within the three departments to be aware of this issue and to consider this in all reports that reference the declining sales figures.

Pervasive Collaboration

• With Pyramid, Mike can initiate a conversation thread on the relevant sales data points and invite stakeholders to participate in further discussions.
• As all stakeholders across all departments access Pyramid’s platform through a centrally hosted browser, Mike knows that all users will be instantly alerted when he comments on the data, regardless of which specific report or dashboard they are using or reviewing. Better yet, the comments will also show up in any ad-hoc review of the data as well.

• In addition, Mike can take a snapshot of the data—along with specific query at the time of his comment—so that all users will have a historical view of the data at the time the comment was made in case the data changes.

In summary, Pyramid is the only BI tool supplier to provide a powerful, ubiquitous, simple, user-friendly, and pervasive collaboration tool that works on reports and data models alike with live, simultaneous collaborative features.

This post originally appeared at https://www.pyramidanalytics.com/blog/details/blog-amplify-your-feedback-pervasive-collaboration-with-pyramid

Other Resources

Discovery Conversations Help

Start a Conversation Help

Comments and Annotations Video

Effective collaboration by multiple stakeholders is very difficult to achieve when all members utilize a desktop version for analysis and server versions for collaboration. Pyramid provides business users with a collaboration tool on a platform that all users are actively using exclusively for all their BI functions. 

Why is collaboration so important?

Today, more than ever before, businesses have converged on the idea that our decisions need to be collaborative. An individual may not uncover insights by analyzing data alone. Knowledgeable stakeholders and colleagues can provide additional insight into why data may be trending up or down based on external factors that are not immediately apparent.

Does this scenario sound familiar?

John, the CFO for Apex Electronics using his desktop BI tool, sees the sales figures for the current quarter is down by 3%, after a consistent 2-year upward trend of between 5% and 8%.

After further interrogation of his cube (drilling, slicing and dicing), he discovers that sales in the Boston store’s electronics department have decreased by 30% in December, dragging down company-wide quarterly sales. 

Difficulties collaborating with traditional desktop BI tools

• Team members using a desktop version of their BI tool are unable to share their reports and visualizations after drilling, slicing and dicing their data. In order to share their results with others, they must upload/publish individual reports or analysis to the web-based solution (usually with inferior capabilities), and in the process force their fellow-collaborators to change platforms too. 
• Users can’t view historical data at the time of initial collaboration and compare it against current data and report views.

• Users are unable to record voice messages and attach multimedia files.

Data security is bypassed when a snapshot of the report is sent via email to all fellow collaborators as all members don’t always have the same access rights and users could view unauthorized data.

Ease of collaborating with Pyramid

• All users use the same universal web-based client, so the initial collaborator can share their reports and discoveries in a central repository after drilling, slicing and dicing. No change in platforms required.
• Comments and annotations within analytic reports and dashboards provide direct access to both current and historical versions of both the report and component data.
• Users can record voice messages and attach multimedia files in a user-friendly interface.
• Data security is applied on a per-user basis, so users can only view data they are authorized to view.

Threaded Conversations and Comments

In the Pyramid application, comments and conversations among colleagues can take place directly within analytic reports and dashboards. Conversations greatly expand the depth of analytics by creating social hubs around analyses. Sharable insights make it easier to discuss results and to create actionable lists for users on the platform. Data and report snapshots allow users to track changes over the lifecycle of a conversation thread.

Comparison between Pyramid vs Classic Collaboration

Below, we compare the approximate time it takes to produce a single data visualization using a desktop-based BI application versus Pyramid.

With Pyramid’s commenting functions, users have a powerful, ubiquitous, simple and user-friendly collaboration tool built right into their analytics platform.

This post originally appeared at https://www.pyramidanalytics.com/blog/details/blog-actionable-data-insights-in-minutes-not-hours

Other Resources

Discovery Conversations Help

Start a Conversation Help

Comments and Annotations Video

As organizations adopt advanced ML techniques into daily analytics, we see key three trends emerging. First, Python is now the predominant language being used by data scientists for all things ML and beyond—outpacing R and replacing SAS. Second, there is a groundswell of demand for access to these capabilities by “citizen data scientists” and classic data consumers. This feeds a third trend: a heavy push to merge data, analytics, and data science pipelines into a singular, interconnected technology arc.

Learning, evaluating, and predicting

The process of learning, evaluating, and predicting using ML is classically performed in three distinct stages. In the initial stage, the ML code is run on a sufficiently large sample of data to learn and create an algorithm that will be used as a model to predict the required result. Following this, the model is evaluated and adapted, if necessary, to achieve the required accuracy. Finally, the model is classically used on a powerful server against a massive set of production data to predict results.
 

The challenges

Python has become the language of choice for ML projects because of its accessible language structure and large collection of rich package libraries. However, up to 80 percent of data scientists’ time can be spent finding, cleansing, and organizing data—which is far less about ML and more about “ETL.” Furthermore, the language is still not for everyone, and can exclude most non-code-savvy data users from testing, developing, and deploying machine learning models to produce useful outcomes.

Finally, as I have written previously about Pyramid’s Python integration, most BI tools—like Power BI and Tableau—do not offer a real-world “application pipeline” for integrating Python and R-based ML projects with classic data visualization, analysis, and reporting.

Instead of allowing users to learn, evaluate and predict, then build content in distinct phases, they expect users to insert and execute Python code and build reports and dashboards, all in one step. This is often performed on a desktop against a subset of data, severely decreasing the likelihood of accurate results. There is no framework for crunching millions of rows of data to build ML models (“learn”); and there is no venue to reapply models for predictions. In many respects, most BI tools treat Python (and R) as a side-show gimmick, without providing a serious framework to produce accurate, scalable ML-driven analyses.

Pyramid’s solution

Pyramid features deep integration of both Python and R, tracking with the key trends above. It acknowledges Python’s and R’s widespread use among data scientists, while simultaneously giving citizen data scientists the ability to perform basic ML functions—and it enables all of this in an integrated environment that lets all users perform analytics across the full pipeline.

Aspect 1: Deep Python and R integration

Pyramid has made Python (and R) a first-class citizen in its architecture and product strategy. Pyramid integrates Python into its data modeling tools, allowing users to utilize Python code as a data source or as a data manipulation and calculation element. Python ML models can, in distinct phases, be trained, tested, and then deployed to run predictions on millions—or billions—of records and can be shared and re-used in other data sources.

Governance of the Python scripts is also maintained with managed access and content versioning. The ability to write back results from the Python code to the original data source adds further value. Pyramid provides a marketplace of free, reusable Python source code for non-technical analysts, with a large library of predefined functions. Further editing, authoring, testing, and running of Python code can all be performed from within Pyramid. This approach is central to delivering a proper “learn and predict” framework for real data sets.

Aspect 2: Empowering citizen data scientists with real ML tools

Pyramid enables both data scientists and non-coding citizen data scientists to perform testing and development of ML models, while using Pyramid’s powerful model tool to perform basic ETL tasks—freeing data scientists to focus solely on data science and ML coding. The process of data extraction and preparation for a learning script normally performed by a Python developer can be performed by individuals without deep Python skills using drag-and-drop actions, saving huge development effort. This approach democratizes access to serious ML-based data operations.

Aspect 3: Bringing the whole ML workflow into a single environment

Pyramid’s entire platform drives a self-service paradigm where all users (data scientists, analysts, and non-technical end users) can use one application to run ML project from “soup to nuts.” They can prepare data, build, and process ML models, generate predictions, visualize results, create and launch dashboards, and consume insights to drive better business decisions—all from one spot.

Business case

Lynette is a data scientist for M&P Furniture Finance. They use SAP HANA for their data warehouse and Pyramid for their BI development and analytics. Lynette wants to apply a Python script to predict possible customer defaults on future payments.

She will use her script to learn from a sample of their large dataset, save the model, and then reuse the same model to perform predictions. The script (which is freely available in Pyramid’s marketplace) uses random forest, a slight variation of a decision tree, to predict customer payment defaults. The random forest builds multiple decision trees and averages the results to provide the best prediction.

Lynette initially selects several customer profile columns (income, number of children, number of cars) as inputs to predict the possibility of a customer defaulting on their monthly payment. Right off the bat, Pyramid’s data preparation capabilities come into play. An Income_Period column indicates if the income is annual or monthly. Lynette uses Pyramid’s data cleaning functions in the Model tool to convert all income to an annual amount.

She then includes her Python script to run the random forest algorithm on the data.

import pandas
from sklearn.ensemble import RandomForestClassifier

def pyramid_learn(df):
target_feature_idx = df.columns.size-1;

X = df.iloc[:,0:target_feature_idx]
y= df.iloc[:,target_feature_idx]
clf = RandomForestClassifier(max_depth=5, n_estimators=10, max_features=1)
clf.fit(X, y)
return clf

def pyramid_eval(model, df):
target_feature_idx = df.columns.size-1;

X = df.iloc[:,0:target_feature_idx]
y = df.iloc[:,target_feature_idx]
output = model.predict(X)
correctCount=0
for idx,item in enumerate(output):
if item == y.iloc[idx]:
correctCount+=1
return str(correctCount / len(y))

def pyramid_predict(model, df):
output = model.predict(df)
return pandas.DataFrame({'prediction':output})

This model uses family income, number of children, and number of cars as input columns and produces a Model Score accuracy of 65.7% when run on a dataset of 200,000 rows of data.

The report displays the number of cars, number of children, income, default payment (0 = No, 1 = Yes), and prediction of default payment (0 = No, 1 = Yes). We see the prediction to be in line with the accuracy predicted by the model.

After experimenting with various input columns, Lynette uses Pyramid’s data model tool to combine family income and number of family members to produce a new column—Income Per Member. In addition, she changes the number of cars column to a Boolean column—Cars YN, as the number of cars does not prove to be a significant factor, whereas the absence of ownership of a car is a significant factor. With these changes, Lynette’s Model Score accuracy improves to 82.7%.

Lynette now applies the Python script with the adjusted input columns and saves the model so it can be reapplied to her massive dataset of 50 million rows to predict payment defaults for each credit customer. In addition, this same model can be applied daily to new customers as a scheduled job.

But there’s more to the story.

John is a BI Analyst who is also interested in predicting payment defaults for his dataset of prospective clients used by the call center. His dataset is separate from Lynette’s. He has not yet run any ML predictions on it. While John has limited experience with Python scripts, he can use Lynette’s for his own dataset. He just needs to apply the saved model to his. Then he can immediately identify customers likely to default on the payment. Thus, John can immediately harness Lynette’s efforts, saving the organization time, money, and resources.

Summary

Python has become an increasingly popular language for organizations running ML predictions to help them with key strategic and operational decisions. However, creating meaningful ML applications requires data to be trained on a model in a batch process using powerful servers. Further, the algorithms used to perform the predictions need to be evaluated and fine-tuned to increase their accuracy. Then the predictive algorithms need to be applied to data sets that sometimes contain billions of rows.

These advanced functions cannot be performed on a desktop BI solution with insufficient hardware, on a small subset of data within a report, where the model cannot be saved and the results cannot be stored, as is done by most third-party BI tools.

In contrast, Pyramid provides a robust, scalable Python integration that allows distinct learning, evaluation, and prediction processes in a server-based batch processing environment, where the resultant model can be saved, shared, and reused to perform additional prediction tasks. The prediction can also be saved and reused in all related analyses.

What’s more, normally complex data extraction and preparation tasks can be easily accomplished using Pyramid’s drag-and-drop modeling tool, saving huge development time and effort. This accessible tool also makes it infinitely easier for citizen data scientists who may not have the same level of expertise with Python. Lastly, Pyramid’s entire self-service BI platform provides a one-stop-shopping experience for the entire arc, from the very start to the very end.

This post originally appeared at https://www.pyramidanalytics.com/blog/details/blog-learn-and-predict-with-python-and-pyramid

Other Resources

Python Learn and Predict Help

Python Learn and Predict Examples Help

Advanced Forecasting Analytics with Python and Pyramid Video

Pyramid provides out-of-the-box forecasting that can be performed with a single click. More advanced manipulations of parameters can be achieved using Pyramid’s advanced forecasting dialog. And when a user has a specific forecasting requirement, he or she can deploy custom scripts to achieve this.

In a previous blog, I demonstrated how Pyramid has made Python (and R) a first-class citizen in its architecture and product strategy, using a stock market example to highlight this. In this blog, I will demonstrate how Python (and R) scripts can be used to meet customized forecasting requirements.

The problem

• Third-party BI tool vendors offer a proprietary “black box” forecasting method where users have no understanding of the algorithm being used and no ability to further tweak it to better fit their unique circumstances.
• Data scientists with deep domain knowledge often need to be able to apply their own Python and R scripts to generate a more accurate forecast.
• They may also want to introduce additional parameters or logic to expand the forecasts beyond the current algorithm.

Pyramid’s solution

• Pyramid gives users the power to deploy customized Python and R scripts to perform a forecasting function.
• A marketplace with free reusable Python source code provides non-technical analysts with a library of predefined forecasting functions.
• Existing Python scripts can be further customized to suit more specific business requirements.
• Scripts can then be shared (and optionally versioned) in the governed content management platform.
• They can also be configured to run with specific package versions using specific Python or R versions in a virtual Python environment.

Business case

Janice is a data scientist for R&G Distributors. R&G uses Oracle as a data warehouse and Pyramid for data visualization, reporting, and analytics.

Janice wants to apply a forecasting algorithm that calculates forecasted values based on previous averages. Using Pyramid, Janice creates a graph depicting sales for the last three years.
 

From her Discover report, Janice applies her own Python script that forecasts sales based on previous averages.

import scipy.stats as st
import pandas
def pyramid_forecast(dataLst, period, futures, shouldCalcHistory):

  if period == 0:
    period = 6

  size = len(dataLst)
  movingAvgs = []
  if size < period+1:
    return pandas.DataFrame({'forecast': []})

  if shouldCalcHistory:
    startIdx = period-1
    for i in range(startIdx, size-1):
      sum=0
      for j in range(0,period):
        sum += dataLst[i-j]
      movingAvgs.append(sum/period)

  # Future predictions. First
  for i in range(0,futures):
    sum = 0

    # take the last element of the data into the some
    elementsToTakeFromData = period-i
    for j in range(size-elementsToTakeFromData, size):
      sum += dataLst[j]

    if elementsToTakeFromData < 0:
      elementsToTakeFromData=0

    # take the rest of the elements from the moving avgs itself (includes predictions)
    elementsToTakeFromPredictions = period-elementsToTakeFromData
    for j in range(len(movingAvgs)-elementsToTakeFromPredictions, len(movingAvgs)):
      sum += movingAvgs[j]

    movingAvgs.append(sum/period)

  interval1High = []
  interval1Low = []
  interval2High = []
  interval2Low = []

  startIdx = 0
  if not shouldCalcHistory:
    startIdx = len(movingAvgs)-futures

  for i in range(startIdx, len(movingAvgs)):
    x = movingAvgs[i]
    currInterval1 = st.t.interval(0.95, len(dataLst) - 1, loc=x, scale=st.sem(dataLst))
    currInterval2 = st.t.interval(0.95, len(dataLst) - 1, loc=x, scale=st.sem(dataLst))

    interval1Low.append(currInterval1[0])
    interval1High.append(currInterval1[1])
    interval2Low.append(currInterval2[0])
    interval2High.append(currInterval2[1])

  df = pandas.DataFrame({'forecast': movingAvgs[startIdx:len(movingAvgs)],
             'interval1High': interval1High,
             'interval1Low': interval1Low,
             'interval2High': interval2High,
             'interval2Low': interval2Low})
  return df

Janice then applies the script to her report and selects historical forecasting to view the accuracy on historical data.

Janice then tweaks the algorithm by changing the previous periods from four to six, and increases the accuracy to ninety-five percent. The newly adjusted forecast is displayed automatically after she applies the changes.

Janice can then save the adjusted script to ensure it will be available in other reports and dashboards. Janice could optionally introduce additional input factors to the Python script to further modify and enhance the script and view the desired forecast.

Summary

Pyramid provides multiple forecasting algorithms that can be executed with a single click, as well as an advanced forecasting dialog, where options can be tweaked for improved performance. Most third-party BI tool vendors only offer a standard forecasting method where users have limited ability to adjust the algorithm, with no option to write their own code.

However, data scientists need to be able to deploy customized Python and R forecasting scripts. With Pyramid, they can create and share their own scripts in a governed content management platform. Scripts can also be configured to run with specific package versions using specific Python or R versions in a virtual Python environment. Pyramid also provides a marketplace with free reusable Python source code with a library of predefined forecasting functions.

This post originally appeared at https://www.pyramidanalytics.com/blog/details/blog-forecasting-with-python-and-pyramid

Other Resources