Essbase.Cubes

The Essbase.Cubes function allows you to connect to Essbase, a multidimensional database management system. With this function, you can access data from Essbase cubes and bring it into Power Query. But what’s the M code behind the Essbase.Cubes function? Let’s take a closer look.

Understanding the Essbase.Cubes Function

Before diving into the M code behind the Essbase.Cubes function, it’s important to understand what the function does. The Essbase.Cubes function returns a table of cube metadata for a given Essbase server. The metadata includes information about the cubes available on the server, such as the name of the cube, the number of dimensions, and the size of the cube.

The function takes two arguments: the Essbase server name and the optional Essbase login credentials. Here’s an example of the Essbase.Cubes function in action:

let

Source = Essbase.Cubes(“EssbaseServerName”),

#”Filtered Rows” = Table.SelectRows(Source, each ([Cube Name] = “Sample”))

in

#”Filtered Rows”

let

EssbaseCubes = (EssbaseServerName as text, optional EssbaseCredentials as record) =>

let

EssbaseXmlaEndpoint = “http://” & EssbaseServerName & “/aps/XMLA”,

EssbaseSessionHeader = if EssbaseCredentials = null then [] else [#”X-Essbase-Auth”=EssbaseCredentials[Token]],

EssbaseCubesRequest = Xml.Tables(Web.Contents(EssbaseXmlaEndpoint, [Headers=EssbaseSessionHeader, Query=[#”COMMAND”=”MDSCHEMA_CUBES”]]), “Cube”),

EssbaseCubesTable = Table.FromList(EssbaseCubesRequest, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

EssbaseCubesExpanded = Table.ExpandRecordColumn(EssbaseCubesTable, “Column1”, {“Name”, “CATALOG_NAME”, “DESCRIPTION”, “BASE_CUBE_NAME”, “LAST_SCHEMA_UPDATE”, “CREATED_ON”, “LAST_DATA_UPDATE”, “IS_DRILLTHROUGH_ENABLED”, “IS_LINKED_CUBE”, “IS_VIRTUAL_CUBE”, “CUBE_CAPTION”, “DIMENSION_COUNT”, “MEASURE_COUNT”, “IS_WRITE_ENABLED”, “IS_RESTRICTED”, “IS_SECURED”, “IS_PARENT_CHILD_ENABLED”, “IS_BALANCED”, “IS_CALCULATED”, “IS_TRANSPOSED”, “IS_SQL_ENABLED”, “IS_SQL_READ_ONLY”, “IS_SQL_PUSH_ENABLED”, “IS_SQL_READ_WRITE”, “IS_ASO”, “IS_BASIC”, “IS_CUSTOM”, “IS_NON_EMPTY_BY_DEFAULT”, “IS_READ_ONLY”, “IS_SINGLE_CELLED”, “IS_SUPPRESS_EMPTY”, “IS_HIDDEN”, “IS_VISUAL_TOTALS”, “IS_MINIMIZE_NON_EMPTY”, “IS_OPTIMIZED_FORM”, “DIMENSION_UNIQUE_NAME”, “DIMENSION_IS_VISIBLE”, “DIMENSION_CARDINALITY”, “DIMENSION_DEFAULT_MEMBER”, “DIMENSION_DESCRIPTION”, “DIMENSION_IS_PARENT”],

{“Name”, “CATALOG_NAME”, “DESCRIPTION”, “BASE_CUBE_NAME”, “LAST_SCHEMA_UPDATE”, “CREATED_ON”, “LAST_DATA_UPDATE”, “IS_DRILLTHROUGH_ENABLED”, “IS_LINKED_CUBE”, “IS_VIRTUAL_CUBE”, “CUBE_CAPTION”, “DIMENSION_COUNT”, “MEASURE_COUNT”, “IS_WRITE_ENABLED”, “IS_RESTRICTED”, “IS_SECURED”, “IS_PARENT_CHILD_ENABLED”, “IS_BALANCED”, “IS_CALCULATED”, “IS_TRANSPOSED”, “IS_SQL_ENABLED”, “IS_SQL_READ_ONLY”, “IS_SQL_PUSH_ENABLED”, “IS_SQL_READ_WRITE”, “IS_ASO”, “IS_BASIC”, “IS_CUSTOM”, “IS_NON_EMPTY_BY_DEFAULT”, “IS_READ_ONLY”, “IS_SINGLE_CELLED”, “IS_SUPPRESS_EMPTY”, “IS_HIDDEN”, “IS_VISUAL_TOTALS”, “IS_MINIMIZE_NON_EMPTY”, “IS_OPTIMIZED_FORM”, “DIMENSION_UNIQUE_NAME”, “DIMENSION_IS_VISIBLE”, “DIMENSION_CARDINALITY”, “DIMENSION_DEFAULT_MEMBER”, “DIMENSION_DESCRIPTION”, “DIMENSION_IS_PARENT”})

in

EssbaseCubesExpanded

in

EssbaseCubes