2 Replies Latest reply on Dec 26, 2018 12:49 PM by Michael Kaminski

    Data - Redshift to the Tableau Client

    Michael Kaminski

      I am having a problem with Tableau and some of the tables we have in Redshift.

       

      When I access the data in my data warehouse (Redshift), most of the fields in a particular table are set to “String” values.  Changing a field (within some tables) from a string, to a date, or a decimal, or whole number, will mess up my workbook, specifically its connection to the data warehouse – I won't be able to see or access data.

       

      Basically, when I change some fields to a different data type, everything becomes screwed up, the workbook and connection to our warehouse.  I don’t know what, or where, that type of setting or modification would be made in the Tableau client. Even if it is able to be modified via the Tableau client.  Or is a database, ETL, or data warehouse issue?  Any help is appreciated.

        • 1. Re: Data - Redshift to the Tableau Client
          Deepak Rai

          You need to do Casting Before hand, I mean before bringing data into Table, so that your Data model is not messed up.

          • 2. Re: Data - Redshift to the Tableau Client
            Michael Kaminski

            This the error message I am getting in Tableau:

             

            An error occurred while communicating with the Amazon Redshift data source 'consumers (ohc_prd.consumers) (ohc_prd)'.

             

             

            Bad Connection: Tableau could not connect to the data source.

            [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState XX000] ERROR:  Invalid data

            DETAIL: 

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

              error:  Invalid data

              code:      8001

              context:   Invalid format or data given:

              query:     1675975

              location:  funcs_timestamp.cpp:280

              process:   query0_125_1675975 [pid=22646]

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

             

            SELECT (("consumers"."last_name" || ', ') || "consumers"."first_name") AS "calculation_1037516772657987584",

              CAST(CAST("consumers"."acuity_factor_date" AS TEXT) AS TIMESTAMP WITHOUT TIME ZONE) AS "acuity_factor_date",

              CAST(TRUNC(CAST(CAST("assessment_text_answers"."assessment_section_question_id" AS TEXT) AS DOUBLE PRECISION)) AS BIGINT) AS "assessment_section_question_id",

              DATE(CAST("intake_events"."date" AS TEXT)) AS "date",

              "consumers"."mmis" AS "mmis"

            FROM "ohc_prd"."consumers" "consumers"

              LEFT JOIN "ohc_prd"."assessment_responses" "assessment_responses" ON (CAST("consumers"."id" AS TEXT) = CAST("assessment_responses"."consumer_id" AS TEXT))

              LEFT JOIN "ohc_prd"."assessment_text_answers" "assessment_text_answers" ON ("assessment_responses"."id" = CAST("assessment_text_answers"."id" AS TEXT))

              LEFT JOIN "ohc_prd"."intake_events" "intake_events" ON ("consumers"."id" = CAST("intake_events"."consumer_id" AS TEXT))

            WHERE ((CAST("consumers"."case_management_agency_id" AS TEXT) = '4') AND (CAST("consumers"."status_id" AS TEXT) = '59'))

            GROUP BY 1,

              2,

              3,

              4,

              5