how to create my own customise static dimentions in explore/looker

[shetu]

 I want to create new dimentions /fields and also want to insert my own values.

what will be the code syntax to achieve this in explore ? what will be the lookml script for this?

for an example:

I want create one field name called: "bucket names" and I want to insert my own vales like "autopricing","non-autopricing" etc. how will i do that in explore?

bucket names

auto pricing

non-autopricing

activeprice

currprice

saleprice

[a_shah · Accepted solution]

You can create dummy dataset using sql, and then use that SQL within the dimension sql parameter in LookML view.

In below example I have made dummy data in BigQuery. you can do it based on the database you are using.

select "auto pricing" as bucket_names

union all

select "non-autopricing" as bucket_names

union all

select "activeprice" as bucket_names

LookML:

dimension: bucket_names {

sql: (

select "auto pricing" as bucket_names

union all

select "non-autopricing" as bucket_names

union all

select "activeprice" as bucket_names

)

type: string

}

Hope that helps !

[TomLoizzo]

If I understand this correctly, I think the best way to do this might be to create a dimension using a CASE WHEN Statement in the sql parameter, 

For example: 

sql: CASW WHEN ${some_dimension} = 'X' THEN "Auto Pricing"  
WHEN ${some_dimension} = 'Y' Then "Non-Auto Pricing" 
END

[a_shah · Accepted solution]

You can create dummy dataset using sql, and then use that SQL within the dimension sql parameter in LookML view.

In below example I have made dummy data in BigQuery. you can do it based on the database you are using.

select "auto pricing" as bucket_names

union all

select "non-autopricing" as bucket_names

union all

select "activeprice" as bucket_names

LookML:

dimension: bucket_names {

sql: (

select "auto pricing" as bucket_names

union all

select "non-autopricing" as bucket_names

union all

select "activeprice" as bucket_names

)

type: string

}

Hope that helps !

[shetu]

Hi shah,

I already have view created from a bigquery table called "looker_test_1" . in that view 3 fields/dimension is there but additionally i want to create my own dummy dimension with my own inserted values like i explain above. but while executing this script, i am getting this below error. how will i resolve it?

The Google BigQuery Standard SQL database encountered an error while running this query.

Query execution failed: - Scalar subquery produced more than one element

SELECT
    (
    select "auto pricing" as bucket_names
    union all
    select "non-autopricing" as bucket_names
    union all
    select "activeprice" as bucket_names
    ) AS looker_test_1_bucket_names
FROM `wmt-dev.mydataset.looker_test_1`
     AS looker_test_1
GROUP BY
    1
ORDER BY
    1
LIMIT 500

[shetu]

copied your script but got this below error. how to resolve this error?

Expecting 'end_of_file', 'keyword', '{', '}', ']', 'comma', '*', ':', got 'identifier'

views/looker_test_1:83

[TomLoizzo]

You can try double semicolons ";;" after the last parenthesis and before "type string"

[shetu]

yeah it resolved my previous error but now got a different one. below is the error.

I already have view created from a bigquery table called "looker_test_1" . in that view 3 fields/dimension is there but additionally i want to create my own dummy dimension with my own inserted values like i explain above. but while executing this script, i am getting this below error. how will i resolve it?

The Google BigQuery Standard SQL database encountered an error while running this query.

Query execution failed: - Scalar subquery produced more than one element

SELECT
    (
    select "auto pricing" as bucket_names
    union all
    select "non-autopricing" as bucket_names
    union all
    select "activeprice" as bucket_names
    ) AS looker_test_1_bucket_names
FROM `wmt-dev.mydataset.looker_test_1`
     AS looker_test_1
GROUP BY
    1
ORDER BY
    1
LIMIT 500

[a_shah · Accepted solution]

@shetu - Do you want to create the static dimension in Looker or in BigQuery?

The solution I gave earlier is to create it in Looker. To create this static table in BQ you can just use the below script with proper project and dataset names.

CREATE or REPLACE VIEW <project>.<dataset>.looker_test_1 AS

    select "auto pricing" as bucket_names

    union all

    select "non-autopricing" as bucket_names

    union all

    select "activeprice" as bucket_names

    ORDER by 1;

~Ashish

[shetu]

Hi @Ashish 

I want to create static dimension in looker only not bigquery.

I have used this below solution  in my view that you have provided but got one error when I am adding this dimension to  looker dashboard and click run button . I have mentioned the error at the last. could you please help me to get this resolved?

dimension: bucket_names {

sql: (

select "auto pricing" as bucket_names

union all

select "non-autopricing" as bucket_names

union all

select "activeprice" as bucket_names

);;

type: string

}

Error:

The Google BigQuery Standard SQL database encountered an error while running this query.

Query execution failed: - Scalar subquery produced more than one element

SELECT
    (
    select "auto pricing" as bucket_names
    union all
    select "non-autopricing" as bucket_names
    union all
    select "activeprice" as bucket_names
    ) AS looker_test_1_bucket_names
FROM `wmt-dev.mydataset.looker_test_1`
     AS looker_test_1
GROUP BY
    1
ORDER BY
    1
LIMIT 500

[a_shah]

Ya, I got it. Looker forces the SQL to be in the select clause of query, so this will give issues. I am still checking on it but in the mean time, you may create a view in BQ and then use it in Looker. Will that work?

~Ashish

[shetu]

yeah please check.

No that will not work in my scenario. I can't use the existing view . I need to create static dimension in looker only.

[a_shah]

The way to solve this issue is via derived tables as derived tables accept sql and create table on the fly. Please refer to below script of the view.

view: dummy {

derived_table: {
sql: select "auto pricing" as bucket_names
union all
select "non-autopricing" as bucket_names
union all
select "active price" as bucket_names
order by 1;;}

dimension: bucket_names {
type: string
sql: ${TABLE}.bucket_names ;;}

}

hope this helps.

~Ashish

[shetu]

yeah I have tested your script. it creates derived table with dummy values . but my scenario is different. In my case I have already created a view from a bigquery table.

I cannot define another view element inside a view . nested view is not possible. though I have added the script you have given inside my view. but I am getting Error : Invalid property of View. 

below is my view script: 

-----------------------------------------------------------------

view: looker_test {
sql_table_name: `wmt-dev.reporting.looker_test`
;;

view: dummy {
derived_table: {
sql: select "auto pricing" as bucket_names
union all
select "non-autopricing" as bucket_names
union all
select "active price" as bucket_names
order by 1;;}
dimension: bucket_names {
type: string
sql: ${TABLE}.bucket_names ;;}
}

dimension: markdown_11 {
type: number
sql: ${TABLE}.Markdown_11 ;;
}

dimension: markdown_11_impact {
type: number
sql: ${TABLE}.Markdown_11_Impact ;;
}
}

-----------------------------------------------------------------

what I want to achieve is:
I have already view created called looker_test, which is a bigquery table. it has 2 columns in bigquery markdown_11 and markdown_11_impact
I want to add another col called bucket_names with my own dummy values.
I cannot define this bucket_names column in bigquery. I have to create this dummy column in looker only.
Stuck with this since a week. tried all possible way to get this resolved.

[a_shah]

@shetu - you cannot create view inside a view. But you can create a separate view for derived table (for static dimension) and then join both views in your explore. This should work in your case. Let me know.