by ·Comments Off on SQL sys.dm_exec_requests

Product:

Microsoft AZURE SQL database

Issue:

It is running a long time, what is the SQL doing?

Solution:

In SSMS, run below query’s to get some information:

 

SELECT
r.session_id,
r.status,
r.wait_type,
r.wait_time,
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time,
DB_NAME(r.database_id) AS database_name,
t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
ORDER BY r.total_elapsed_time DESC;

 

 

SELECT
r.session_id,
s.login_name,
s.host_name,
r.status,
r.command,
r.cpu_time,
r.total_elapsed_time,
r.logical_reads,
r.reads,
r.writes,
DB_NAME(r.database_id) AS database_name,
t.text AS sql_text
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID
ORDER BY r.cpu_time DESC;

 

 

The key columns are:

  • wait_type → what resource it is waiting for.
  • blocking_session_id → another session blocking it.
  • wait_resource → the specific resource.

 

More Information:

https://www.mssqltips.com/sqlservertip/5521/understanding-and-using-sql-server-sysdmexecrequests/ 

https://dev.to/shiviyer/mastering-performance-troubleshooting-with-sysdmexecrequests-in-azure-sql-3f0j 

 

by ·Comments Off on Select two elements to a view

Product:
Planning Analytics 2.1.19 workspace

Issue:

Select two elements to a view?

Solution:

In PAW create a new book and from Planning Sample instance create a view of plan_budgetPlan cube.

 

Hold CTRL down and click on two elements. Then right-click and from menu select keep.

If you want to select a dimension element by level, you can open set editor, by click on the tree dots.

Click on filter icon, and select level and the level you want.

Click Done, and Apply and the view have the selected elements shown.

 

 

More Information:

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=elements-selecting 

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=elements-selecting-adjacent

https://quebit.com/askquebit/how-to-use-one-title-dimension-selection-to-control-another-dimensions-subset-in-a-paw-cube-view/ 

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=views-visualizations-in-planning-analytics-workspace 

by ·Comments Off on \bin64\tm1sd.exe -install -n servicename -z “path_to_directory_containing_new_tm1s.cfg”

Product:
Planning Analytics 2.1.19
Microsoft Windows 2022 server

Issue:

How install planning sample?

Solution:

Copy the plansamp folder to a new folder like d:\tm1 data\utv\plansamp

Create a \config folder where you place the tm1s.cfg file

Also create a \logs and \data folder. Copy all files from plansamp folder to data folder.

Adjust the Tm1s.cfg to have correct path to folders:

DataBaseDirectory=D:\TM1 Data\Utv\plansamp\data\
LoggingDirectory=D:\TM1 Data\Utv\plansamp\Logs\

Create a ps1 file in notepad with below text:

cd "D:\Program Files\ibm\tm1_64\bin64\"
.\tm1sd.exe -install -n "Planning Sample" -z "D:\Tm1 Data\UTV\PlanSamp\config"

Save the file.

Start powershell as administrator and run the above script

The tm1 samples are not installed automatic in new versions.

Start the service from windows services.

Edit the tm1s.cfg to have the authentication you need. Example  IntegratedSecurityMode=5

 

More Information:

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=offerings-planning-analytics-local

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=later-registering-running-tm1-server-as-windows-service

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=configuration-tm1scfg-file

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=parameters-integratedsecuritymode

by ·Comments Off on Faulting module name: jvm.dll_unloaded

Product:

Cognos Controller 11.1.1003

Issue:

When you inside the Controller client program select a java menu, like groups – command center or maintain – jobs -define. The program exist without a error message.

If you check your Windows event log, you find this error:

Faulting application name: CCR.exe, version: 11.1.1003.12, time stamp: 0x67e3a0f4
Faulting module name: jvm.dll_unloaded, version: 8.0.0.0, time stamp: 0x66856ee4
Exception code: 0xc0000005
Fault offset: 0x0000000000008a90
Faulting process id: 0x3f3c
Faulting application start time: 0x01dd28ba25cff042
Faulting application path: D:\Program Files\ibm\IBM Controller Local Client\CCR.exe
Faulting module path: jvm.dll

..

Application: CCR.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.AccessViolationException

Solution:

A update of EDGE on the computer, have change the way WebView2 works. This gives a conflict with Controller java parts.
Create a text file in notepad with below text:

# Avoid contending with Chromium for low address space
-Xmx1024m
-Xnocompressedrefs
-verbose:sizes

 

Save the file as jvm.options

Place the file in folder C:\Program Files\IBM\IBM Controller Local Client\Integration\ on your laptop.

Start Cognos Controller client and test again.

 

More information:

Performance impact in Threads/Timers initialization while using -Xcompressedrefs

Java Memory Settings – JVM Heap Size – Code Curmudgeon

Verbose Garbage Collection in Java | Baeldung

Heap allocation – IBM Documentation

Critical Java JVM options and parameters

by ·Comments Off on TM1 predict the future

Product:

Planning Analytics 2.1.19

Issue:

How to get a Forecast for a cube view?

Solution:

To make a forecast happen, you need to have a few tings setup in your TM1 model. This is a list of suggestions that may be needed.

The cube you are doing the forecast must have a TIME dimension.  This are in TM1 Architect set on the cube properties.

The dimension for time, need to be in a format that TM1 understands.

Like Only years, or Year – quarters – months hierarchy.

Forecasting requires the time dimension to be placed on the column.

You must have data in all the periods, if you only have data in Quarters, but the dimension contain blank months, the predict may not work. You can get a message like:

Too many missing or zero values in the historical data.

The historical data must represent at least double what is being forecasted. If you forecast 2 years, you need 4 years of old data.

The view need empty periods where the prediction can be created. If you want to look 5 years forward, your TIME dimension need to contain empty elements for 5 years.

To begin forecasting, click an exploration view in a book, then click Forecast and click either Univariate or Multivariate, depending on which type of forecast you want to generate. A univariate forecast predicts the future values of a single variable based on historical data, using the Holt-Winters model.

Use the Set up tab to define the start date and end date for your forecast.

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=options-set-up-forecast-tab

You can preview only one time series at a time. The forecasting preview shows what the results for the forecast look like, based on your input.

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=forecasting-preview

 

If you do not have a }DimensionAttributes cube in your TM1 model, you need to create one from PAW.

Right click on the Dimension and select Dimension Attributes.

Enter the attribute name: DIMENSION_TYPE

Click Add.

Then in the view of the DimensionAttributes cube, find the row for your year dimension and enter TIME as DIMENSION_TYPE.

This is the TIME dimension that can be used by the forecast.

https://revelwood.com/ibm-planning-analytics-tips-tricks-planning-analytics-forecasting-configuring-the-time-dimension/ 

 

You can also use PAW to create a TIME dimension for you – that can be on monthly level.

The TIME dim created by PAW will look like this,  you may use it in your new models.

 

 

Above is only suggestions, depending on the version of PAW this can behave or look different.

More Information:

https://revelwood.com/use-the-planning-analytics-ai-assistant-for-smarter-data-analysis/ 

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=dimensions-time-in-planning-analytics-workspace 

  1. Right-click the Dimensions node in your database on the Databases tree, then click Create time dimension.

    Alternatively, you can create a time dimension by clicking the Configure time dimension option when you create a new dimension. The Configure time dimension option is only available for a dimension with one hierarchy and no members.

  2. If required, enter a name for the dimension.
  3. Select the Start year for the dimension.
  4. Select the End year for the dimension.
  5. Answer the How frequently do you plan? question by selecting the granularity of the dimension.
    Months
    This option gives you three levels: Months, Quarters, Years.
    Quarters
    This option gives you two levels: Quarters, Years.
    Years
    This option gives you one level: Years.
  6. Check the preview to confirm that it matches your expectation, then click Save.
    You can modify the time dimension in the dimension editor after you create it.

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=forecasting-requirements

Forecasting requires the time dimension to be placed on the column. The algorithm requires the time dimension members to be organized in order of increasing time, where each member of a level represents a consistent unit of time. Members within the dimension definition that do not conform to this requirement, for example calculated year-to-date member, can be hidden. Relevant time periods can be chosen to be ignored, resulting in a linear interpolation of values to be predicted from. Forecasting also supports hierarchical time dimensions and nested dimensions as time: year, quarter, and month dimensions.

One consequence of this is that leaves with multiple parents along the time series would result in non-contiguous time, so special care must be taken in these cases. If possible, it is advised to consider adding a special hierarchy for time series forecasting to support the required time configuration.

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=views-forecasting

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=forecasting-error-messages 

Something went wrong while generating the forecast for one or more series
This is a general message for any unsuccessful prediction. Please try again.

IBM Planning Analytics Tips & Tricks: Planning Analytics Forecasting – Configuring the Forecast Settings

 

IBM Planning Analytics Tips & Tricks: Planning Analytics Forecasting – Previewing the Forecast

 

IBM Planning Analytics Tips & Tricks: Planning Analytics Forecasting – Setting up the Forecast