Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dmakan
Frequent Visitor

Ayuda a simplificar mi consulta mientras se está quedando sin memoria

Hola

Necesito ayuda para simplemente hacer mi consulta. Mensaje de error "No hay suficiente memoria para completar esta operación. Inténtelo de nuevo más tarde cuando haya más memoria disponible."

El siguiente indicador ConsecutiveFail que estoy creando está en más de 6 millones de registros donde está devolviendo copias de seguridad consecutivas fallidas:

ConsecutiveFail ?
INTERRUPTOR (
VERDADERO (),
v_NBU[FailValidation] - 0, 0,
CALCULAR (
COUNTROWS ( v_NBU ),
FILTRO (
ALL ( v_NBU ),
v_NBU[FailValidation] - 0
&& v_NBU[BackupClient] - EARLIER ( v_NBU[BackupClient] )
&& v_NBU[Job_start_time] <EARLIER ( v_NBU[Job_start_time] )
)
)
0, CALCULAR (
SUM ( v_NBU[FailValidation] ),
FILTRO (
ALL ( v_NBU ),
v_NBU[BackupClient] - EARLIER ( v_NBU[BackupClient] )
&& v_NBU[Job_start_time] <- EARLIER ( v_NBU[Job_start_time] )
)
),
CALCULAR (
SUM ( v_NBU[FailValidation] ),
FILTRO (
ALL ( v_NBU ),
v_NBU[BackupClient] - EARLIER ( v_NBU[BackupClient] )
&& v_NBU[Job_start_time]
> CALCULAR (
MAX ( v_NBU[Job_start_time] ),
FILTRO (
ALL ( v_NBU ),
v_NBU[FailValidation] - 0
&& v_NBU[BackupClient] - EARLIEST ( v_NBU[BackupClient] )
&& v_NBU[Job_start_time] < EARLIEST ( v_NBU[Job_start_time] )
)
)
&& v_NBU[Job_start_time] <- EARLIER ( v_NBU[Job_start_time] )
)
)
)
Gracias
Dipesh
1 ACCEPTED SOLUTION

Hola

no hay necesidad de abrir un archivo. Simplemente pegue el siguiente código en el editor avanzado de una nueva consulta y ajuste a la ubicación de su archivo de Excel:

let
    Source = Excel.Workbook(
        File.Contents("C:\Users\xxxYourFilePath...\continousStreakRank.xlsx"), 
        null, 
        true
    ),
    Sheet1_Sheet = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars = true]),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Promoted Headers", 
        {
            {"BackupClient", type text}, 
            {"Job_start_time", type date}, 
            {"BackupJobExitStatus", type text}, 
            {"Fail Validation", Int64.Type}, 
            {"Needed", Int64.Type}
        }
    ),
    GroupWithGroupKindLocal = Table.Group(
        #"Changed Type", 
        {"BackupClient", "BackupJobExitStatus"}, 
        {{"PartitionLocal", each Table.AddIndexColumn(_, "PQ", 1, 1)}}, 
        GroupKind.Local
    ),
    ExpandColumns = Table.ExpandTableColumn(
        GroupWithGroupKindLocal, 
        "PartitionLocal", 
        {"Job_start_time", "Fail Validation", "Needed", "PQ"}, 
        {"Job_start_time", "Fail Validation", "Needed", "PQ"}
    ),
    ReplaceNonFailed = Table.ReplaceValue(
        ExpandColumns, 
        each [PQ], 
        each if [Fail Validation] = 0 then 0 else [PQ], 
        Replacer.ReplaceValue, 
        {"PQ"}
    )
in
    ReplaceNonFailed

Encontrará algunas instrucciones para ello en los enlacesde mis notas al pie .

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

13 REPLIES 13
amitchandak
Super User
Super User

@dmakan , ¿Puede compartir datos de ejemplo y salida de muestra.

Hola @amitchandak

Vea los datos de muestra a continuación con la columna de salida al final

Capture.PNG

@dmakan , Puede pegar los mismos datos en formato de tabla. Además, ¿puedo asumir que todas las fechas están ahí? Para hacerlo rápido estoy pensando en usar

fecha: antes (Fecha) -1

Eso significa que necesitamos todas las fechas consecutivas están ahí

Hola @amitchandak

Es posible que falten fechas, ya que a veces las copias de seguridad no se ejecutan como la siguiente tabla:

Una vez más, la última columna es mi salida de resultado que necesito.

BackupClientJob_start_timeBackupJobExitStatusValidación de fallos
Abc05-Enero-20Exitoso00
Abc06-Enero-20Fallado11
Abc07-Enero-20Fallado12
Abc08-Enero-20Fallado13
Abc09-Enero-20Fallado14
Abc10-Enero-20Exitoso00
Abc14-Enero-20Exitoso00
Abc30-Enero-20Fallado11
Abc31-Enero-20Fallado12
Def01-Enero-20Fallado11
Def02-Enero-20Fallado12
Def19-Enero-20Fallado13
Def20-Enero-20Exitoso00

Hola, @dmakan

Según su descripción, creé datos para reproducir su escenario. El archivo pbix se adjunta al final.

Mesa:

a1.png

Puede crear una columna calculada como se muestra a continuación.

Result = 
var _client = 'Table'[BackupClient]
var _starttime = 'Table'[Job_start_time]
var _failvalidation = 'Table'[Fail Validation]
var _lastsuccesttdate = 
CALCULATE(
    MAX('Table'[Job_start_time]),
    FILTER(
        ALL('Table'),
        'Table'[BackupClient] = _client&&
        'Table'[Job_start_time]<_starttime&&
        'Table'[Fail Validation] = 0
    )
)
return
IF(
    _failvalidation = 0,
    0,
    IF(
        _failvalidation = 1,
        CALCULATE(
            DISTINCTCOUNT('Table'[Job_start_time]),
            FILTER(
                ALL('Table'),
                'Table'[BackupClient] = _client&&
                'Table'[Job_start_time]>_lastsuccesttdate&&
                'Table'[Job_start_time]<=_starttime
            )
        )
    )
)

Resultado:

a2.png

Saludos

Allan

Si este post ayuda,entonces por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

Hola @v-alq-msft

Gracias por la ayuda. Esto ha vuelto a ser mi problema si se ejecuta con los datos de ejemplo. Todavía estoy recibiendo un "error de memoria insuficiente al aplicar al conjunto de datos original que es más de 6 millones de registros. ¿Hay alguna manera de crear una función dentro de las consultas de edición o aplicarla a la consulta T-SQL en el paso de importación?

saludos

Dipesh

Hola, @dmakan

Puede ir a 'Editor de consultas', hacer clic con el botón derecho en el paso que desea aplicar al conectarse a la base de datos, haga clic en 'Ver consulta nativa', copie los códigos.

b1.png

A continuación, debe conectarse a la misma base de datos con los códigos.

b2.png

Resultado:

b3.png

Saludos

Allan

Si este post ayuda,entonces por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

@dmakan , Encuentre el pbix adjunto después de la firma. Lo dudo más rápido que lo que has hecho. Pero puedes comprobarlo y contarlo. Creé dos columnas para conseguir esto.

Por favor, hágamesaber sus hallazgos.

Gracias @amitchandak

Esta solución parece funcionar sin el error de memoria que estaba recibiendo anteriormente.

El único problema es que ahora está contando varios errores por día. Asumo que la aplicación de copia de seguridad vuelve a intentarlo después de los intentos fallidos que se registran en los datos. ¿Alguna idea de cómo podría evitar esto?

¡Gracias por la ayuda!

saludos

Dipesh

Hola a todos

Espero que este tipo de operación sea más rápida en el editor de consultas (vea también el archivo adjunto):

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WcnRyVtJRMjBV8ErMK00sqlQwMjAyAIoElyYnpxYXp5XmgKShGEzF6sB1maHrckvMzElNATIModgITCNpMcerxQiOkbRY4NViDNVijKzFEq8WE6gWEyQthgbkhIChCTm6jDHswgg3E7RwMzYkGG4m8HBzcXUD2YhfCwIjaTEiaIshmi2G+IPaGE4jtBiREtSxAA==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type text) meta [Serialized.Text = true])
    in
      type table[
        BackupClient = _t, 
        Job_start_time = _t, 
        BackupJobExitStatus = _t, 
        #"Fail Validation" = _t, 
        Needed = _t, 
        part = _t, 
        #"final Needed" = _t
      ]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"BackupClient", type text}, 
      {"Job_start_time", type date}, 
      {"BackupJobExitStatus", type text}, 
      {"Fail Validation", Int64.Type}, 
      {"Needed", Int64.Type}, 
      {"part", Int64.Type}, 
      {"final Needed", Int64.Type}
    }
  ),
  GroupWithGroupKindLocal = Table.Group(
    #"Changed Type", 
    {"BackupClient", "BackupJobExitStatus"}, 
    {{"PartitionLocal", each Table.AddIndexColumn(_, "PQ", 1, 1)}}, 
    GroupKind.Local
  ),
  ExpandColumns = Table.ExpandTableColumn(
    GroupWithGroupKindLocal, 
    "PartitionLocal", 
    {"Job_start_time", "Fail Validation", "Needed", "PQ"} 
  ),
  ReplaceNonFailed = Table.ReplaceValue(
    ExpandColumns, 
    each [PQ], 
    each if [Fail Validation] = 0 then 0 else [PQ], 
    Replacer.ReplaceValue, 
    {"PQ"}
  )
in
  ReplaceNonFailed

Primero creo un índice anidado ( https://www.youtube.com/watch?v=-3KFZaYImEY ) - pero con un giro que se restablece después de cada cambio en la columna "Validación de error". El uso del 4o parámetro en el paso GroupWithGroupKindLocal hace eso por mí ( https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/ ). Ambos elementos son esenciales para realizar la mejora del rendimiento. (Más información sobre esto aquí: https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-power... )

Luego reemplazo estos índices anidados por 0 para esas filas, donde la validación no falló ( https://www.thebiccountant.com/2017/07/23/transforming-a-column-with-values-from-another-column-in-p... ) ... pero eso no es esencial aquí, podrías haber añadido otra columna con una condición y haber eliminado la antigua.

Problema con la solución DAX que tiene que hacer referencia a toda la tabla en cada fila, mientras que mi PQ-logic fragmentará la tabla primero (usando los algoritmos de grupo) antes de aplicar la operación costosa (eso es lo que EARLIER hace en DAX): Aquí, simplemente agregando una columna de índice.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hola ImkeF

Me gustaría incoportar esta lógica a mi fuente de Excel.

Traté de descargar el archivo pbi pero no se abre: "No pudimos restaurar el modelo dtabase guardado"

¿Podría aconsejar sobre cómo incorporar la lógica a una fuente de Excel.

Gracias

Hola

no hay necesidad de abrir un archivo. Simplemente pegue el siguiente código en el editor avanzado de una nueva consulta y ajuste a la ubicación de su archivo de Excel:

let
    Source = Excel.Workbook(
        File.Contents("C:\Users\xxxYourFilePath...\continousStreakRank.xlsx"), 
        null, 
        true
    ),
    Sheet1_Sheet = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars = true]),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Promoted Headers", 
        {
            {"BackupClient", type text}, 
            {"Job_start_time", type date}, 
            {"BackupJobExitStatus", type text}, 
            {"Fail Validation", Int64.Type}, 
            {"Needed", Int64.Type}
        }
    ),
    GroupWithGroupKindLocal = Table.Group(
        #"Changed Type", 
        {"BackupClient", "BackupJobExitStatus"}, 
        {{"PartitionLocal", each Table.AddIndexColumn(_, "PQ", 1, 1)}}, 
        GroupKind.Local
    ),
    ExpandColumns = Table.ExpandTableColumn(
        GroupWithGroupKindLocal, 
        "PartitionLocal", 
        {"Job_start_time", "Fail Validation", "Needed", "PQ"}, 
        {"Job_start_time", "Fail Validation", "Needed", "PQ"}
    ),
    ReplaceNonFailed = Table.ReplaceValue(
        ExpandColumns, 
        each [PQ], 
        each if [Fail Validation] = 0 then 0 else [PQ], 
        Replacer.ReplaceValue, 
        {"PQ"}
    )
in
    ReplaceNonFailed

Encontrará algunas instrucciones para ello en los enlacesde mis notas al pie .

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Gracias funciona 100%

Ahora debe convertir esto a origen SQL.

😁

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors