public List <YearCategory> Getyearcategory()
        {
            List <YearCategory> Year = new List <YearCategory>();


            AdomdConnection conn = new AdomdConnection(
                "Data Source=LAPTOP-ED7T3RSE\\ETELLERANDET;Initial Catalog=Charlie_BI_AnalysisProject;");

            conn.Open();

            string          commandText = @"SELECT {[Dim Product].[Hierarchy].[Main Category]} ON COLUMNS, NONEMPTY({[Dim Date].[Hierarchy].[Year]}) ON ROWS FROM [Charlie BI F Club] WHERE [Measures].[Fact Sale Count]";
            AdomdCommand    cmd         = new AdomdCommand(commandText, conn);
            AdomdDataReader dr          = cmd.ExecuteReader();

            foreach (var item in dr)
            {
                YearCategory TempYear = new YearCategory();
                TempYear.year    = Convert.ToString(item[0]);
                TempYear.food    = Convert.ToString(item[1]);
                TempYear.nonFood = Convert.ToString(item[2]);
                TempYear.unknown = Convert.ToString(item[3]);
                Year.Add(TempYear);
            }

            dr.Close();
            conn.Close();
            return(Year);
        }
Ejemplo n.º 2
0
        /// <summary>
        /// Возвращает список кубов
        /// </summary>
        /// <returns></returns>
        public Dictionary <String, CubeDefInfo> GetCubes()
        {
            try
            {
                System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Getting Cubes List Started", DateTime.Now.ToString());

                Dictionary <String, CubeDefInfo> list = new Dictionary <String, CubeDefInfo>();
                AdomdConnection conn = GetConnection();
                foreach (CubeDef cube in conn.Cubes)
                {
                    CubeDefInfo info = InfoHelper.CreateCubeInfo(cube);
                    if (info != null)
                    {
                        if (!list.ContainsKey(info.Name))
                        {
                            list.Add(info.Name, info);
                        }
                    }
                }
                return(list);
            }
            finally {
                System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Getting Cubes List Completed", DateTime.Now.ToString());
            }
        }
Ejemplo n.º 3
0
        public object GetMaxVersion()
        {
            var ids = new SSASIdentifiers(_input, _output);

            var versionField = _output.Entity.GetVersionField();

            if (versionField == null)
            {
                return(null);
            }

            if (_output.Process.Mode == "init")
            {
                return(null);
            }

            object result = null;

            using (AdomdConnection conn = new AdomdConnection($"Data Source={_output.Connection.Server};Catalog={ids.DatabaseId}")) {
                conn.Open();
                var mdx = $"select [MEASURES].[{ids.VersionId}] ON COLUMNS FROM [{ids.CubeId}]";
                using (var cmd = new AdomdCommand(mdx, conn)) {
                    using (var reader = cmd.ExecuteReader()) {
                        while (reader.Read())
                        {
                            result = reader[0];
                        }
                        reader.Close();
                    }
                }
                conn.Close();
            }

            return(result);
        }
Ejemplo n.º 4
0
        private void RunSSAS(object sender, EventArgs e)
        {
            //i don't think  Dataset is in the Analysis Services directives
            DataSet ds = new DataSet();
            // provider is the constant olap.  datasource is the same server name you provide for Mgmt Studio or localhost
            // initial catalog is tricky and important.  It is not a standard ms sql database you see in Management Studio,
            // even if your cube was create with tables from a particular database.
            // the only place I was able to see "initial catalog" value was a File -> Open -> Analysis Services Database in 2012 Management Studio
            // it was also the name of the VS2010 solution I used to create the cube.
            AdomdConnection  myconnect = new AdomdConnection(@"provider=olap;initial catalog=GLCubeThree;datasource=localhost");
            AdomdDataAdapter mycommand = new AdomdDataAdapter();

            mycommand.SelectCommand            = new AdomdCommand();
            mycommand.SelectCommand.Connection = myconnect;
            // this query was created by the "Browser" you see for an Analysis Services project
            // if you poke around the icons on the browser table the Design Mode icon will give you the cube query
            // I think it's an MDX query, threre are also xml queries you can run with adomd
            mycommand.SelectCommand.CommandText = "SELECT NON EMPTY { [Measures].[Per Balance] } ON COLUMNS, NON EMPTY { ([Gltime].[Fisc Per].[Fisc Per].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Gltime].[Fisc Per].&[201301], [Gltime].[Fisc Per].&[201302], [Gltime].[Fisc Per].&[201307] } ) ON COLUMNS FROM [GL Cube]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS";
            myconnect.Open();
            mycommand.Fill(ds, "tbl");
            myconnect.Close();
            // the below assigns the results of the cube query to a dataGridView
            // if you drag a dataGridView control to your pallete it will create exactly
            // what you need for the line below to work.
            // your project type has to be a Window Forms Applications
            // this code shown here is in the default Form1.Designer.cs not Form1.cs
            dataGridView1.DataSource = new DataView(ds.Tables[0]);
        }
Ejemplo n.º 5
0
        public HttpResponseMessage GetItemsByDimension(string dim, string order)
        {
            string WITH = @"
                WITH 
                SET [OrderDimension] AS 
                NONEMPTY(
                    ORDER(
                        {0}.CHILDREN,
                        {0}.CURRENTMEMBER.MEMBER_NAME, " + order +
                          @")
                )
            ";

            string COLUMNS = @"
                NON EMPTY
                {
                    [Measures].[Ventas]
                }
                ON COLUMNS,    
            ";

            string ROWS = @"
                NON EMPTY
                    [OrderDimension]
                ON ROWS
            ";

            string CUBO_NAME = "[DHW Northwind]";

            WITH = string.Format(WITH, dim);
            string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;

            Debug.Write(MDX_QUERY);

            List <string> dimension = new List <string>();

            dynamic result = new
            {
                datosDimension = dimension
            };

            using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
            {
                cnn.Open();
                using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
                {
                    cmd.Parameters.Add("Dimension", dim);
                    using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (dr.Read())
                        {
                            dimension.Add(dr.GetString(0));
                        }
                        dr.Close();
                    }
                }
            }

            return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
        }
Ejemplo n.º 6
0
        public DataSet ExecuteDataSet(String query, ref String sessionId)
        {
            if (!String.IsNullOrWhiteSpace(_connectionString))
            {
                using (AdomdConnection _connect = new AdomdConnection(_connectionString))
                {
                    try
                    {
                        using (AdomdDataAdapter adapter = new AdomdDataAdapter(query, _connect))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds);
                            foreach (DataTable tbl in ds.Tables)
                            {
                                foreach (DataColumn dc in tbl.Columns)
                                {
                                    dc.ColumnName = Helpers.GetDimFieldShortName(dc.ColumnName);
                                }
                            }

                            return ds;
                        }
                    }
                    catch (Exception ex)
                    {
                        if (_connect.State == ConnectionState.Open)
                        {
                            _connect.Close();
                        }
                        throw ex;
                    }
                }
            }
            return null;
        }
Ejemplo n.º 7
0
 public DataTable Adomd()
 {
     //连接的字符串
     string conStr = "provider=msolap ;Integrated Security =SSPI ;Data Source= localhost ;Catalog =Scal B2CReport Mdx ;";
     //创建个连接对象
     AdomdConnection con = new AdomdConnection();
     con.ConnectionString = conStr;
     con.Open();
     // 创建个命令
     AdomdCommand cmm = con.CreateCommand();
     cmm.CommandText = @"select
     {[Measures].[订票数量],
     [Measures].[Add Fare],[Measures].[Par Price]} on columns,
     {[Sale Sta View Time].[Create Day].[2011-12-01]:[Sale Sta View Time].[Create Day].[2011-12-31]} on rows
     from [SCAL3 Test]
     where
     {
     {[Sale Sta View Generic].[Is Member].&[1]}
     }";
     //执行命令返回单元集合
     CellSet result = cmm.ExecuteCellSet();
     DataTable table = CellSetToTable(result);
     con.Close();
     return table;
 }
Ejemplo n.º 8
0
        protected virtual AdomdConnection GetConnection()
        {
            AdomdConnection conn = new AdomdConnection(Connection.ConnectionString);

            conn.Open();
            return(conn);
        }
        //Below subroutine will return a data table with results. It takes the DB connection string and query as input
        protected System.Data.DataTable GetDataTable(string ConnString, string query)
        {
            System.Data.DataTable dtOlap = new System.Data.DataTable();
            //Connect to Analysis Server
            AdomdConnection conn = new AdomdConnection(ConnString);

            System.Diagnostics.Debug.WriteLine(conn.ConnectionString);

            try
            {
                conn.Open();

                //Create adomd command using connection and MDX query
                AdomdCommand cmd = new AdomdCommand(query, conn);
                AdomdDataAdapter adr = new AdomdDataAdapter(cmd);
                adr.Fill(dtOlap);
            }
            catch (InvalidCastException e)
            {
                Response.Write("Access denied on " + ConnString);
            }
            finally
            {
                //Close DB Connection
                conn.Close();
            }
            return dtOlap;
        }
Ejemplo n.º 10
0
        public DimensionInfo GetDimensionToHierarchy(String cubeName, String hierarchyUniqueName)
        {
            try
            {
                System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Dimension To Hierarchy '{1}' Started \r\n cubeName: '{2}' ",
                                                          DateTime.Now.ToString(), hierarchyUniqueName, cubeName);

                if (!String.IsNullOrEmpty(hierarchyUniqueName) && !String.IsNullOrEmpty(cubeName))
                {
                    AdomdConnection conn = GetConnection();
                    CubeDef         cube = FindCube(cubeName);
                    if (cube != null)
                    {
                        foreach (Dimension dim in cube.Dimensions)
                        {
                            foreach (Hierarchy hierarchy in dim.Hierarchies)
                            {
                                if (hierarchy.UniqueName.ToLower() == hierarchyUniqueName.ToLower())
                                {
                                    return(InfoHelper.CreateDimensionInfo(dim));
                                }
                            }
                        }
                    }
                }
                return(null);
            }
            finally {
                System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Dimension To Hierarchy Completed \r\n", DateTime.Now.ToString());
            }
        }
Ejemplo n.º 11
0
        public Dictionary <String, HierarchyInfo> GetHierarchies(String cubeName, string dimensionUniqueName)
        {
            try
            {
                System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Hierarchies Started \r\n cubeName: '{1}' \r\n dimensionUniqueName: '{2}'",
                                                          DateTime.Now.ToString(), cubeName, dimensionUniqueName);

                Dictionary <String, HierarchyInfo> list = new Dictionary <String, HierarchyInfo>();
                AdomdConnection conn = GetConnection();

                Dimension dim = FindDimension(cubeName, dimensionUniqueName);
                if (dim != null)
                {
                    foreach (Hierarchy hierarchy in dim.Hierarchies)
                    {
                        HierarchyInfo info = InfoHelper.CreateHierarchyInfo(hierarchy);
                        if (info != null)
                        {
                            if (!list.ContainsKey(info.UniqueName))
                            {
                                list.Add(info.UniqueName, info);
                            }
                        }
                    }
                }

                return(list);
            }
            finally {
                System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Hierarchies Completed \r\n", DateTime.Now.ToString());
            }
        }
Ejemplo n.º 12
0
        public Dictionary <String, DimensionInfo> GetDimensions(string cubeName)
        {
            try
            {
                System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Dimensions List Started \r\n cubeName: '{1}' ",
                                                          DateTime.Now.ToString(), cubeName);

                Dictionary <String, DimensionInfo> list = new Dictionary <String, DimensionInfo>();
                AdomdConnection conn = GetConnection();
                CubeDef         cube = FindCube(cubeName);
                if (cube != null)
                {
                    foreach (Dimension dim in cube.Dimensions)
                    {
                        DimensionInfo info = InfoHelper.CreateDimensionInfo(dim);
                        if (info != null)
                        {
                            if (!list.ContainsKey(info.UniqueName))
                            {
                                list.Add(info.UniqueName, info);
                            }
                        }
                    }
                }
                return(list);
            }
            finally {
                System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Dimensions List Completed \r\n ",
                                                          DateTime.Now.ToString(), cubeName);
            }
        }
Ejemplo n.º 13
0
        public virtual CellSet ExecuteCellSet(out float elapsedSec)
        {
            // Open the connection
            using (var connection = new AdomdConnection())
            {
                var connectionString = command.Connection.ConnectionString;
                try
                { connection.ConnectionString = connectionString; }
                catch (ArgumentException ex)
                { throw new ConnectionException(ex, connectionString); }
                //TODO
                //try
                //    {connection.Open();}
                //catch (AdomdException ex)
                //    {throw new ConnectionException(ex);}

                Trace.WriteLineIf(NBiTraceSwitch.TraceVerbose, command.CommandText);
                foreach (AdomdParameter param in command.Parameters)
                {
                    Trace.WriteLineIf(NBiTraceSwitch.TraceVerbose, string.Format("{0} => {1}", param.ParameterName, param.Value));
                }

                // capture time before execution
                DateTime timeBefore = DateTime.Now;
                CellSet  cellSet    = null;

                try
                {
                    if (command.Connection.State != ConnectionState.Open)
                    {
                        command.Connection.Open();
                    }
                    cellSet = command.ExecuteCellSet();
                }
                catch (AdomdConnectionException ex)
                {
                    throw new ConnectionException(ex, connectionString);
                }
                catch (AdomdErrorResponseException ex)
                {
                    throw new ConnectionException(ex, connectionString);
                }
                finally
                {
                    if (command.Connection.State == ConnectionState.Open)
                    {
                        command.Connection.Close();
                    }
                }

                // capture time after execution
                DateTime timeAfter = DateTime.Now;

                // setting query runtime
                elapsedSec = (float)timeAfter.Subtract(timeBefore).TotalSeconds;
                Trace.WriteLineIf(NBiTraceSwitch.TraceInfo, string.Format("Time needed to execute query [ADOMD]: {0}", timeAfter.Subtract(timeBefore).ToString(@"d\d\.hh\h\:mm\m\:ss\s\ \+fff\m\s")));

                return(cellSet);
            }
        }
Ejemplo n.º 14
0
        public static ICollection<string> ExecuteMdxCommand(string command, string connectionString, int index)
        {
            Logger.Debug(CultureInfo.CurrentCulture, "Send mdx query.");
            Logger.Debug(CultureInfo.CurrentCulture, "Query: {0}", command);
            Logger.Debug(CultureInfo.CurrentCulture, "AS connection string: {0}", connectionString);
            Logger.Debug(CultureInfo.CurrentCulture, "Index: {0}", index);

            var resultList = new List<string>();
            using (var connection = new AdomdConnection(connectionString))
            {
                connection.Open();

                using (var mdxCommand = new AdomdCommand(command, connection))
                {
                    using (var mdxReader = mdxCommand.ExecuteReader())
                    {
                        while (mdxReader.Read())
                        {
                            ////getName - column name
                            resultList.Add(mdxReader[index].ToString());
                        }
                    }
                }

                return resultList;
            }
        }
Ejemplo n.º 15
0
        public string GetCubeInformation()
        {
            StringBuilder cubeInformation = new StringBuilder();

            AdomdConnection conn = new AdomdConnection(ConnString);

            conn.Open();

            //Cube objects are CubeDef here
            foreach (CubeDef cube in conn.Cubes)
            {
                if (cube.Name.StartsWith('$'))
                {
                    continue;
                }
                cubeInformation.Append("Cube Name: " + cube.Name + '\n');

                cubeInformation.Append("Cube KPIs: " + cube.Kpis.Count + '\n');
                cubeInformation.Append("Cube Measures: " + cube.Measures.Count + '\n');
                cubeInformation.Append("Updated at " + cube.LastUpdated + '\n' + "Dimensions: " + '\n');

                foreach (Dimension dim in cube.Dimensions)
                {
                    cubeInformation.AppendLine(dim.Name);
                }

                cubeInformation.Append("\n\n");
            }

            conn.Close();

            return(cubeInformation.ToString());
        }
Ejemplo n.º 16
0
        public string GetHierarchies(int cubeId, int dimensionId)
        {
            using (AdomdConnection mdConn = new AdomdConnection())
            {
                mdConn.ConnectionString = "provider=msolap;Data Source=V1LGORPC\\ASMAIN;initial catalog=AdventureWorksDW2014Multidimensional-EE;";
                mdConn.Open();

                List <CubeDef> realCubeList = Utilities.Utilities.GetRealCubeList(mdConn.Cubes);

                CubeDef cubeDef = realCubeList[cubeId];

                Microsoft.AnalysisServices.AdomdClient.Dimension dimension = cubeDef.Dimensions[dimensionId];

                List <Models.Hierarchy> hierarchyList = new List <Models.Hierarchy>();

                foreach (Microsoft.AnalysisServices.AdomdClient.Hierarchy hierarchy in dimension.Hierarchies)
                {
                    Models.Hierarchy newHierarchy = new Models.Hierarchy(hierarchy);

                    hierarchyList.Add(newHierarchy);
                }

                return(JsonConvert.SerializeObject(hierarchyList, Formatting.Indented));
            }
        }
Ejemplo n.º 17
0
        public List <Day> Getdays()
        {
            List <Day> Day = new List <Day>();

            AdomdConnection conn = new AdomdConnection(
                "Data Source=.;Initial Catalog=Charlie_BI_AnalysisProject;");

            conn.Open();

            string          commandText = @"SELECT {[Measures].[Fact Sale Count] } ON COLUMNS, NonEmpty({[Dim Date].[Hierarchy].[Day Of Month]}) ON ROWS FROM[Charlie BI F Club]";
            AdomdCommand    cmd         = new AdomdCommand(commandText, conn);
            AdomdDataReader dr          = cmd.ExecuteReader();

            foreach (var item in dr)
            {
                Day Tempday = new Day();
                Tempday.month  = Convert.ToString(item[1]);
                Tempday.day    = Convert.ToString(item[2]);
                Tempday.amount = Convert.ToString(item[3]);
                Day.Add(Tempday);
            }

            dr.Close();
            conn.Close();
            return(Day);
        }
Ejemplo n.º 18
0
        //Below subroutine will return a data table with results. It takes the DB connection string and query as input
        protected System.Data.DataTable GetDataTable(string ConnString, string query)
        {
            System.Data.DataTable dtOlap = new System.Data.DataTable();
            //Connect to Analysis Server
            AdomdConnection conn = new AdomdConnection(ConnString);

            System.Diagnostics.Debug.WriteLine(conn.ConnectionString);

            try
            {
                conn.Open();

                //Create adomd command using connection and MDX query
                AdomdCommand     cmd = new AdomdCommand(query, conn);
                AdomdDataAdapter adr = new AdomdDataAdapter(cmd);
                adr.Fill(dtOlap);
            }
            catch (InvalidCastException e)
            {
                Response.Write("Access denied on " + ConnString);
            }
            finally
            {
                //Close DB Connection
                conn.Close();
            }
            return(dtOlap);
        }
Ejemplo n.º 19
0
 static void Main(string[] args)
 {
     // prepare adomd connection 
     using (AdomdConnection mdConn = new AdomdConnection())
     {
         mdConn.ConnectionString = "provider=msolap;Data Source=(local);initial catalog=HabraCube;";
         mdConn.Open();//new comment
         AdomdCommand mdCommand = mdConn.CreateCommand();
         mdCommand.CommandText = "SELECT {[Measures].[Vote], [Measures].[Votes Count]} ON COLUMNS, [Dim Time].[Month Name].MEMBERS ON ROWS FROM [Habra DW]"; // << MDX Query // work with CellSet 
         CellSet cs = mdCommand.ExecuteCellSet(); // our method supports only 2-Axes CellSets 
         if (cs.Axes.Count != 2) return;
         TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples;
         TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples; // output column headers
         Console.Write("{0,-12}", "Item");
         for (int col = 0; col < tuplesOnColumns.Count; col++)
         {
             Console.Write("{0,-12}", tuplesOnColumns[col].Members[0].Caption);
         }
         Console.WriteLine(); // output rows 
         for (int row = 0; row < tuplesOnRows.Count; row++)
         {
             Console.Write("{0,-12}", tuplesOnRows[row].Members[0].Caption); // fill columns 
             for (int col = 0; col < tuplesOnColumns.Count; col++)
             { Console.Write("{0,-12}", cs.Cells[col, row].Value); } Console.WriteLine();
         }
         Console.ReadLine();
     }
 }
    static void ExecuteDaxQuery()
    {
        // DAX query to be submitted totabuar database engine
        String query = @"
            EVALUATE
                SUMMARIZECOLUMNS(
                    //GROUP BY 
                    Customers[State],
                
                    //FILTER BY
                    TREATAS( {""Western Region""} , 'Customers'[Sales Region] ) ,
                     
                    // MEASURES
                    ""Sales Revenue"" , SUM(Sales[SalesAmount]) ,
                    ""Units Sold"" , SUM(Sales[Quantity])
                )
            ";

        AdomdConnection adomdConnection = new AdomdConnection(connectString);

        adomdConnection.Open();

        AdomdCommand    adomdCommand = new AdomdCommand(query, adomdConnection);
        AdomdDataReader reader       = adomdCommand.ExecuteReader();

        ConvertReaderToCsv(reader);

        reader.Dispose();
        adomdConnection.Close();
    }
Ejemplo n.º 21
0
        static public List <string> getAllMeasures(string datasource, string catalog, string cube)
        {
            List <string> measureNames = new List <string>();

            string connectionString = string.Format(Constants.dataSource, datasource, catalog);

            using (AdomdConnection conn = new AdomdConnection(connectionString))
            {
                conn.Open();

                using (DataSet measures = conn.GetSchemaDataSet(Constants.mdSchema, null, true))
                {
                    foreach (DataTable dta in measures.Tables)
                    {
                        foreach (DataRow dro in dta.Rows)
                        {
                            foreach (DataColumn dco in dta.Columns)
                            {
                                // Get non-null unique measure names
                                if (dro[dco] != null && dco.ColumnName.ToString() == Constants.measureUniqueName)
                                {
                                    measureNames.Add(dro[dco].ToString());
                                }
                            }
                        }
                    }
                }
                conn.Close();
            }

            return(measureNames);
        }
Ejemplo n.º 22
0
        private void btnRequest_Click(object sender, EventArgs e)
        {
            AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();

            try
            {

                foreach (DataGridViewRow row in gridRestrictions.Rows)
                {
                    if (row.Cells["AdomdRestrictionName"].Value != null && row.Cells["AdomdRestrictionName"].Value != DBNull.Value && !string.IsNullOrEmpty(row.Cells["AdomdRestrictionName"].Value.ToString()))
                        restrictions.Add(row.Cells["AdomdRestrictionName"].Value.ToString().Trim(), row.Cells["AdomdRestrictionValue"].Value.ToString().Trim());
                }
                AdomdConnection conn = new AdomdConnection(txtConnStr.Text);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                DataTable table = conn.GetSchemaDataSet(cboxRequestType.Text, restrictions).Tables[0];
                gridResults.DataSource = table;
            }
            catch (Exception ex)
            {
                this.ShowMessage(ex);
            }
        }
Ejemplo n.º 23
0
        public List <Year> GetYear()
        {
            List <Year> Year = new List <Year>();


            AdomdConnection conn = new AdomdConnection(
                "Data Source=.;Initial Catalog=Charlie_BI_AnalysisProject;");

            conn.Open();

            string          commandText = @"SELECT {[Measures].[Fact Sale Count] } ON COLUMNS, NONEMPTY({[Dim Date].[Hierarchy].[Year]} ) ON ROWS FROM[Charlie BI F Club]";
            AdomdCommand    cmd         = new AdomdCommand(commandText, conn);
            AdomdDataReader dr          = cmd.ExecuteReader();

            foreach (var item in dr)
            {
                Year Tempyear = new Year();
                Tempyear.year   = Convert.ToString(item[0]);
                Tempyear.amount = Convert.ToString(item[1]);
                Year.Add(Tempyear);
            }

            dr.Close();
            conn.Close();
            return(Year);
        }
Ejemplo n.º 24
0
        /// <summary>
        /// 根据mdx语句、连接字符串
        /// </summary>
        /// <param name="strMdx"></param>
        /// <param name="ConnectionStringName"></param>
        /// <returns></returns>
        public DataTable GetDataByCon(string strMdx, string ConStr)
        {
            DataTable dt = new DataTable();

            try
            {
                using (AdomdConnection _connection = new AdomdConnection(ConStr))
                {
                    if (_connection != null)
                    {
                        if (_connection.State == ConnectionState.Closed)
                        {
                            _connection.Open();
                        }

                        AdomdCommand cmd = _connection.CreateCommand();
                        cmd.CommandText = strMdx;

                        var     executexml = cmd.ExecuteXmlReader();
                        CellSet cellset    = CellSet.LoadXml(executexml);
                        //_connection.GetSchemaDataSet
                        _connection.Close();
                        dt = ToDataTable(cellset);
                    }
                }

                return(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 25
0
        public static ICollection <string> ExecuteMdxCommand(string command, string connectionString, int index)
        {
            Logger.Debug(CultureInfo.CurrentCulture, "Send mdx query.");
            Logger.Debug(CultureInfo.CurrentCulture, "Query: {0}", command);
            Logger.Debug(CultureInfo.CurrentCulture, "AS connection string: {0}", connectionString);
            Logger.Debug(CultureInfo.CurrentCulture, "Index: {0}", index);

            var resultList = new List <string>();

            using (var connection = new AdomdConnection(connectionString))
            {
                connection.Open();

                using (var mdxCommand = new AdomdCommand(command, connection))
                {
                    using (var mdxReader = mdxCommand.ExecuteReader())
                    {
                        while (mdxReader.Read())
                        {
                            ////getName - column name
                            resultList.Add(mdxReader[index].ToString());
                        }
                    }
                }

                return(resultList);
            }
        }
Ejemplo n.º 26
0
 /// <summary>
 /// 建立连接
 /// </summary>
 /// <param name="connection">AdomdConnection对象的实例</param>
 /// <param name="connectionString">连接字符串</param>
 public void Connect(ref AdomdConnection connection, string connectionString)
 {
     if (connectionString == "")
     {
         throw new ArgumentNullException("connectionString", "The connection string is not valid.");
     }
     //	Ensure an AdomdConnection object exists and that its ConnectionString property is set.
     if (connection == null)
     {
         connection = new AdomdConnection(connectionString);
     }
     else
     {
         Disconnect(ref connection, false);
         connection.ConnectionString = connectionString;
     }
     try
     {
         connection.Open();
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }
Ejemplo n.º 27
0
        /// <summary>
        /// 获取OLAP数据库。
        /// </summary>
        /// <param name="connection">AdomdConnection对象的实例</param>
        /// <param name="connectionString">连接字符串</param>
        /// <returns></returns>
        public DataTable GetSchemaDataSet_Catalogs(ref AdomdConnection connection, string connectionString)
        {
            bool      connected = true;            //判断connection在调用此函数时,是否已经处于连接状态
            DataTable objTable  = new DataTable();

            try
            {
                // Check if a valid connection was provided.
                if (IsConnected(ref connection) == false)
                {
                    //如果连接不存在,则建立连接
                    Connect(ref connection, connectionString);
                    connected = false;                            //更改connection为未连接状态。
                }
                objTable = connection.GetSchemaDataSet(AdomdSchemaGuid.Catalogs, null).Tables[0];
                if (connected == false)
                {
                    //关闭连接
                    Disconnect(ref connection, false);
                }
            }
            catch (Exception err)
            {
                throw err;
            }

            return(objTable);
        }
        public static AdomdConnection GetConnection(string connectionString, string sessionId)
        {
            HttpContext context = HttpContext.Current;
            if (context != null && context.Session != null)
            {
                AdomdConnection conn = context.Session[connectionString] as AdomdConnection;
                if (conn == null)
                {
                    conn = new AdomdConnection(connectionString);
                    AdomdConnectionPool.OpenConnection(conn, sessionId);
                    context.Session[connectionString] = conn;
                }
                if (conn.State == ConnectionState.Closed)
                {
                    AdomdConnectionPool.OpenConnection(conn, sessionId);
                }

                return conn;
            }

            var tmpConn = new AdomdConnection(connectionString);
            AdomdConnectionPool.OpenConnection(tmpConn, sessionId);

            return tmpConn;
        }
Ejemplo n.º 29
0
        public Connect()
        {
            InitializeComponent();
            InitializeTextEditor();

            moConn = new AdomdConnection();
        }
Ejemplo n.º 30
0
        /// <summary>
        /// 获取立方体的维度
        /// </summary>
        /// <param name="connection">AdomdConnection对象的实例</param>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="CubeName">立方体名称</param>
        /// <returns></returns>
        public string[] GetDimensions(ref AdomdConnection connection, string connectionString, string CubeName)
        {
            string[] strDimensions = null;
            bool     connected     = true;

            if (IsConnected(ref connection) == false)
            {
                try
                {
                    Connect(ref connection, connection.ConnectionString);
                    connected = false;
                }
                catch (Exception err)
                {
                    throw err;
                }
            }

            int rowcount = connection.Cubes[CubeName].Dimensions.Count;

            strDimensions = new string[rowcount];
            for (int i = 0; i < rowcount; i++)
            {
                strDimensions[i] = connection.Cubes[CubeName].Dimensions[i].Caption.ToString();
            }
            if (connected == false)
            {
                Disconnect(ref connection, false);
            }
            return(strDimensions);
        }
        protected virtual string InquireFurtherAnalysisService(string connectionString)
        {
            try
            {
                var parsedMode = string.Empty;
                using (var conn = new AdomdConnection(connectionString))
                {
                    conn.Open();
                    var restrictions = new AdomdRestrictionCollection();
                    restrictions.Add(new AdomdRestriction("ObjectExpansion", "ReferenceOnly"));
                    var ds = conn.GetSchemaDataSet("DISCOVER_XML_METADATA", restrictions);
                    var xml = ds.Tables[0].Rows[0].ItemArray[0].ToString();
                    var doc = new XmlDocument();
                    doc.LoadXml(xml);
                    parsedMode = ParseXmlaResponse(doc);
                }

                switch (parsedMode)
                {
                    case "Default": return Olap;
                    case "Multidimensional": return Olap;
                    case "SharePoint": return Tabular;
                    case "Tabular": return Tabular;
                }
            }
            catch (Exception ex)
            {
                Trace.WriteLineIf(NBiTraceSwitch.TraceWarning,"Can't detect server mode for SSAS, using Olap. Initial message:" + ex.Message);
                return Olap;
            }
            return Olap;
        }
Ejemplo n.º 32
0
        /// <summary>
        /// 以connection的方式获取立方体
        /// </summary>
        /// <param name="connection">AdomdConnection对象的实例</param>
        /// <param name="connectionString">连接字符串</param>
        /// <returns></returns>
        public string[] GetCubes(ref AdomdConnection connection, string connectionString)
        {
            string[] strCubesName = null;
            bool     connected    = true;         //判断connection是否已与数据库连接

            if (IsConnected(ref connection) == false)
            {
                try
                {
                    Connect(ref connection, connection.ConnectionString);
                    connected = false;
                }
                catch (Exception err)
                {
                    throw err;
                }
            }

            int rowcount = connection.Cubes.Count;

            strCubesName = new string[rowcount];
            for (int i = 0; i < rowcount; i++)
            {
                strCubesName[i] = connection.Cubes[i].Caption;
            }

            if (connected == false)
            {
                Disconnect(ref connection, false);
            }
            return(strCubesName);
        }
        internal MiningServiceCollectionInternal(AdomdConnection connection) : base(connection)
        {
            ListDictionary      restrictions = new ListDictionary();
            ObjectMetadataCache objectCache  = new ObjectMetadataCache(connection, InternalObjectType.InternalTypeMiningService, MiningServiceCollectionInternal.schemaName, restrictions);

            base.Initialize(objectCache);
        }
Ejemplo n.º 34
0
        /// <summary>
        /// 通过SchemaDataSet的方式获取制定立方体的维度
        /// </summary>
        /// <param name="connection">AdomdConnection对象的实例</param>
        /// <param name="connectionString">连接字符串</param>
        /// <returns></returns>
        public string[] GetSchemaDataSet_Dimensions(ref AdomdConnection connection, string connectionString, string cubeName)
        {
            string[]  strDimensions = null;
            bool      connected     = true;       //判断connection是否已与数据库连接
            DataTable objTable      = new DataTable();

            if (IsConnected(ref connection) == false)
            {
                try
                {
                    Connect(ref connection, connectionString);
                    connected = false;
                }
                catch (Exception err)
                {
                    throw err;
                }
            }
            string[] strRestriction = new string[] { null, null, cubeName, null, null };
            objTable = connection.GetSchemaDataSet(AdomdSchemaGuid.Dimensions, strRestriction).Tables[0];
            if (connected == false)
            {
                Disconnect(ref connection, false);
            }
            strDimensions = new string[objTable.Rows.Count];
            int rowcount = 0;

            foreach (DataRow tempRow in objTable.Rows)
            {
                strDimensions[rowcount] = tempRow["DIMENSION_NAME"].ToString();
                rowcount++;
            }
            return(strDimensions);
        }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //الاتصال مع قاعدة البيانات
        AdomdConnection conn = new AdomdConnection();

        conn.ConnectionString = "Data Source=.;Catalog=AnalysisDB";
        conn.Open();
        // Command
        AdomdCommand cmd = new AdomdCommand();

        cmd.Connection = conn;
        //DMX استعلام
        cmd.CommandText = String.Format(@"select Predict ([Product Name]) from [Customer_ProductsMM]
                                          natural prediction join
                      (select '{0}' as [Age],
		                      '{1}' as [Cars],
		                      '{2}' as [Children],
		                      '{3}' as [Commute Distance],
                              '{4}' as [Education],
                              '{5}' as [Gender],
                              '{6}' as [Home Owner],
                              '{7}' as [Income],
                              '{8}' as [Marital Status],
                              '{9}' as [Occupation],
                              '{10}' as [Region]) as t",
                                        txtAge.Text, txtcar.Text, txtChildren.Text, DRLCommuteD.SelectedItem, DRLeducation.SelectedItem, RBLGender.SelectedItem,
                                        RBLHomeOwner.SelectedItem, txtIncome.Text, DRLMaritalst.SelectedItem, DRLOccupation.SelectedItem, DRLRegion.SelectedItem);
        //إظهار النتيجة
        AdomdDataReader adr = cmd.ExecuteReader();

        while (adr.Read())
        {
            txtresult.Text = adr.GetString(0);
        }
    }
Ejemplo n.º 36
0
        /// <summary>
        /// Execute MDX query and populate model with results
        /// </summary>
        public void GetData()
        {
            DataSet ds = new DataSet();

            using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2"))
            {
                conn.Open();
                using (AdomdCommand cmd = new AdomdCommand(CommandText(), conn))
                {
                    AdomdDataAdapter adapter = new AdomdDataAdapter(cmd);
                    adapter.Fill(ds);
                }
                conn.Close();
            }
            // Single query returns one table
            DataTable dt = ds.Tables[0];
            // read column names to use to select correct fields for data
            // TBD Inspect here to find column names for pivoted Dayname-bikes
            Dictionary <string, int> colnbr = new Dictionary <string, int>();

            foreach (DataColumn dc in dt.Columns)
            {
                colnbr.Add(dc.ColumnName, dc.Ordinal);
            }
            foreach (DataRow dr in dt.Rows)
            {
                TripByCatTimeDistRow oneresult = new TripByCatTimeDistRow(dr, colnbr);

                Add(oneresult);
            }
        }
Ejemplo n.º 37
0
        public static List <string> getAllAttributes(string datasource, string catalog, string cube)
        {
            List <string> attributeNames   = new List <string>();
            string        connectionString = string.Format(Constants.dataSource, datasource, catalog);

            using (AdomdConnection conn = new AdomdConnection(connectionString))
            {
                conn.Open();
                using (DataSet attributes = conn.GetSchemaDataSet(Constants.mdSchemaLevels, null, true))
                {
                    foreach (DataTable dta in attributes.Tables)
                    {
                        foreach (DataRow dro in dta.Rows)
                        {
                            if (Convert.ToInt32((dro.ItemArray[9].ToString())) != 0) // Removes levels of type (All)
                            {
                                foreach (DataColumn dco in dta.Columns)
                                {
                                    // Get non-null unique names
                                    if (dro[dco] != null && dco.ColumnName.ToString() == Constants.levelUniqueName)
                                    {
                                        attributeNames.Add(dro[dco].ToString());
                                    }
                                }
                            }
                        }
                    }
                }
                conn.Close();
            }
            return(attributeNames);
        }
Ejemplo n.º 38
0
        public async Task <string> getData(string query)
        {
            try
            {
                var token = await ADALTokenHelper.GetAppOnlyAccessToken(domain, $"https://{ssasUrl}", clientId, clientSecret);

                var connectionString = GetConn(ssasUrl, token);
                var ssasConnection   = new AdomdConnection(connectionString);
                ssasConnection.Open();
                var cmd = new AdomdCommand(query)
                {
                    Connection = ssasConnection
                };

                using (var reader = cmd.ExecuteXmlReader())
                {
                    var value = reader.ReadOuterXml();
                    return(value);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 39
0
        public List <Members> Getmembers()
        {
            List <Members> members = new List <Members>();

            AdomdConnection conn = new AdomdConnection(
                "Data Source=LAPTOP-ED7T3RSE\\ETELLERANDET;Initial Catalog=Charlie_BI_AnalysisProject;");

            conn.Open();

            string          commandText = @"SELECT {[Measures].[Fact Sale Count]} ON COLUMNS, NONEMPTY({[Dim Member].[Member ID].[Member ID]}) ON ROWS FROM [Charlie BI F Club]";
            AdomdCommand    cmd         = new AdomdCommand(commandText, conn);
            AdomdDataReader dr          = cmd.ExecuteReader();

            foreach (var item in dr)
            {
                Members member = new Members();
                member.member = Convert.ToString(item[0]);
                member.Amount = Convert.ToString(item[1]);
                members.Add(member);
            }

            dr.Close();
            conn.Close();
            return(members);
        }
 /// <summary>
 /// Initializes a new instance of the AdomdConnectionWrapper class by using the specified command.
 /// </summary>
 /// <param name="connection">The AdomdConnection to encapsulate.</param>
 public AdomdConnectionWrapper(AdomdConnection connection)
 {
     if (connection == null)
     {
         throw new ArgumentNullException("connection");
     }
     _innerConnection = connection;
 }
 public override void PreExecute()
 {
     base.PreExecute();
     adoMdConn = new AdomdConnection("Data Source=WHQWSSASQRY1;Catalog=Tyson Analytics");
     adoMdConn.Open();
     AdomdCommand cmd = new AdomdCommand(Variables.qryCube, adoMdConn);
     dataReader = cmd.ExecuteReader();
 }
Ejemplo n.º 42
0
 public CubeOperate(String adomdConnectionString)
 {
     this.ConnectionString = adomdConnectionString;
     Conn = new AdomdConnection(adomdConnectionString);
     Conn.Open();
     CubeDefs = Conn.Cubes.Cast<CubeDef>().ToList();
     Cubes = GetCubes();
     Dimensions = GetDimensions();
 }
        public void Execute_AdventureWorksDimensions_ListStructureContainingNotMeasure()
        {
            var conn = new AdomdConnection(ConnectionStringReader.GetAdomd());
            var factory = new OlapStructureDiscoveryFactory(conn);
            var cmd = factory.Instantiate(Target.Dimensions, TargetType.Object,
                new CaptionFilter[] {
                    new CaptionFilter(Target.Perspectives, "Adventure Works")
                });

            var structs = cmd.Execute();

            Assert.That(structs, Is.Not.Contains("Measures"));
        }
Ejemplo n.º 44
0
        public static void Run()
        {
            // open connection
            //			string connStr="Data Source=localhost;Initial Catalog=Foodmart 2000;";
            string connStr="Data Source=10.3.0.247;Initial Catalog=Adventure Works DW Standard Edition;";
            //			string connStr="Data Source=http://localhost/xmla/msxisapi.dll;Initial Catalog=Foodmart 2000;";
            AdomdConnection conn = new AdomdConnection(connStr);

            AdomdCommand cmd=null;

            // execute mdx
            string queryStr=
            //				@"select
            //    {Product.Members} on rows,
            //    {{Time.Members}} on columns
            //from Sales
            //";

                @"WITH SET [Promotions_set] AS '{[Promotions].[All Promotions].[Bag Stuffers],[Promotions].[All Promotions].[Best Savings],[Promotions].[All Promotions].[Big Promo],[Promotions].[All Promotions].[Big Time Discounts],[Promotions].[All Promotions].[Big Time Savings],[Promotions].[All Promotions].[Bye Bye Baby],[Promotions].[All Promotions].[Cash Register Lottery],[Promotions].[All Promotions].[Coupon Spectacular],[Promotions].[All Promotions].[Dimes Off],[Promotions].[All Promotions].[Dollar Cutters],[Promotions].[All Promotions].[Dollar Days],[Promotions].[All Promotions].[Double Down Sale],
            [Promotions].[All Promotions].[Double Your Savings],[Promotions].[All Promotions].[Fantastic Discounts],[Promotions].[All Promotions].[Free For All],[Promotions].[All Promotions].[Go For It],[Promotions].[All Promotions].[Green Light Days],[Promotions].[All Promotions].[Green Light Special],[Promotions].[All Promotions].[High Roller Savings],[Promotions].[All Promotions].[I Cant Believe It Sale],[Promotions].[All Promotions].[Money Grabbers],[Promotions].[All Promotions].[Money Savers],[Promotions].[All Promotions].[Mystery Sale],[Promotions].[All Promotions].[No Promotion],[Promotions].[All Promotions].[One Day Sale],[Promotions].[All Promotions].[Pick Your Savings],[Promotions].[All Promotions].[Price Cutters],[Promotions].[All Promotions].[Price Destroyers],[Promotions].[All Promotions].[Price Savers],[Promotions].[All Promotions].[Price Slashers],[Promotions].[All Promotions].[Price Smashers],[Promotions].[All Promotions].[Price Winners],[Promotions].[All Promotions].[Sale Winners],[Promotions].[All Promotions].[Sales Days],[Promotions].[All Promotions].[Sales Galore],[Promotions].[All Promotions].[Save-It Sale],[Promotions].[All Promotions].[Saving Days],[Promotions].[All Promotions].[Savings Galore],[Promotions].[All Promotions].[Shelf Clearing Days],
            [Promotions].[All Promotions].[Shelf Emptiers],[Promotions].[All Promotions].[Super Duper Savers],[Promotions].[All Promotions].[Super Savers],[Promotions].[All Promotions].[Super Wallet Savers],[Promotions].[All Promotions].[Three for One],[Promotions].[All Promotions].[Tip Top Savings],[Promotions].[All Promotions].[Two Day Sale],[Promotions].[All Promotions].[Two for One],[Promotions].[All Promotions].[Unbeatable Price Savers],[Promotions].[All Promotions].[Wallet Savers],[Promotions].[All Promotions].[Weekend Markdown],[Promotions].[All Promotions].[You Save Days],[Promotions].[All Promotions]}'
            SET [Promotions_set_wcalc] AS '{{[Promotions_set]}}' SET [Time_set] AS '{[*SET Time.1997.Children*],[*SET Time.Q1.Children*],[*SET Time.Q2.Children*],[*SET Time.Q3.Children*],[*SET Time.Q4.Children*]}' SET [*SET Time.Q4.Children*] AS '{[Time].[1997].[Q4].Children}' SET [*SET Time.Q3.Children*] AS '{[Time].[1997].[Q3].Children}' SET [*SET Time.Q2.Children*] AS '{[Time].[1997].[Q2].Children}' SET [*SET Time.Q1.Children*] AS '{[Time].[1997].[Q1].Children}' SET [*SET Time.1997.Children*] AS '{[Time].[1997].Children}' SET [Time_set_wcalc] AS '{{[Time_set]}}' SET [Store Size in SQFT_set] AS '{[*SET Store Size in SQFT.All Store Size in SQFT.Children*]}' SET [*SET Store Size in SQFT.All Store Size in SQFT.Children*] AS '{[Store Size in SQFT].[All Store Size in SQFT].Children}' SET [Store Size in SQFT_set_wcalc] AS '{{[Store Size in SQFT_set]}}' SET [Product_set] AS '{[*SET Product.Drink.Children*],[*SET Product.Food.Children*],[*SET Product.Alcoholic Beverages.Children*],[*SET Product.Beverages.Children*],[*SET Product.Dairy.Children*],[*SET Product.Baked Goods.Children*],
            [*SET Product.Baking Goods.Children*],[*SET Product.Breakfast Foods.Children*],[*SET Product.Canned Foods.Children*],[*SET Product.Canned Products.Children*],[*SET Product.Deli.Children*],[*SET Product.Eggs.Children*],[*SET Product.Frozen Foods.Children*],[*SET Product.Meat.Children*],[*SET Product.Produce.Children*],[*SET Product.Seafood.Children*],[*SET Product.Snack Foods.Children*],[*SET Product.Snacks.Children*],[*SET Product.Starchy Foods.Children*],[*SET Product.Non-Consumable.Children*],[*SET Product.Carousel.Children*],[*SET Product.Checkout.Children*],[*SET Product.Health and Hygiene.Children*],[*SET Product.Household.Children*],[*SET Product.Periodicals.Children*]}' SET [*SET Product.Periodicals.Children*] AS '{[Product].[All Products].[Non-Consumable].[Periodicals].Children}' SET [*SET Product.Household.Children*] AS '{[Product].[All Products].[Non-Consumable].[Household].Children}' SET [*SET Product.Health and Hygiene.Children*] AS '{[Product].[All Products].[Non-Consumable].[Health and Hygiene].Children}' SET [*SET Product.Checkout.Children*] AS '{[Product].[All Products].[Non-Consumable].[Checkout].Children}'
            SET [*SET Product.Carousel.Children*] AS '{[Product].[All Products].[Non-Consumable].[Carousel].Children}' SET [*SET Product.Non-Consumable.Children*] AS '{[Product].[All Products].[Non-Consumable].Children}' SET [*SET Product.Starchy Foods.Children*] AS '{[Product].[All Products].[Food].[Starchy Foods].Children}' SET [*SET Product.Snacks.Children*] AS '{[Product].[All Products].[Food].[Snacks].Children}' SET [*SET Product.Snack Foods.Children*] AS '{[Product].[All Products].[Food].[Snack Foods].Children}' SET [*SET Product.Seafood.Children*] AS '{[Product].[All Products].[Food].[Seafood].Children}' SET [*SET Product.Produce.Children*] AS '{[Product].[All Products].[Food].[Produce].Children}' SET [*SET Product.Meat.Children*] AS '{[Product].[All Products].[Food].[Meat].Children}' SET [*SET Product.Frozen Foods.Children*] AS '{[Product].[All Products].[Food].[Frozen Foods].Children}' SET [*SET Product.Eggs.Children*] AS '{[Product].[All Products].[Food].[Eggs].Children}' SET [*SET Product.Deli.Children*] AS '{[Product].[All Products].[Food].[Deli].Children}' SET [*SET Product.Canned Products.Children*] AS '{[Product].[All Products].[Food].[Canned Products].Children}' SET [*SET Product.Canned Foods.Children*] AS '{[Product].[All Products].[Food].[Canned Foods].Children}'
            SET [*SET Product.Breakfast Foods.Children*] AS '{[Product].[All Products].[Food].[Breakfast Foods].Children}' SET [*SET Product.Baking Goods.Children*] AS '{[Product].[All Products].[Food].[Baking Goods].Children}' SET [*SET Product.Baked Goods.Children*] AS '{[Product].[All Products].[Food].[Baked Goods].Children}' SET [*SET Product.Dairy.Children*] AS '{[Product].[All Products].[Food].[Dairy].Children}' SET [*SET Product.Beverages.Children*] AS '{[Product].[All Products].[Drink].[Beverages].Children}' SET [*SET Product.Alcoholic Beverages.Children*] AS '{[Product].[All Products].[Drink].[Alcoholic Beverages].Children}' SET [*SET Product.Food.Children*] AS '{[Product].[All Products].[Food].Children}' SET [*SET Product.Drink.Children*] AS '{[Product].[All Products].[Drink].Children}'
            SET [Product_set_wcalc] AS '{{[Product_set]}}' MEMBER [Promotion Media].[*AGGREGATE*] AS 'AGGREGATE({[Promotion Media].[All Media].[Bulk Mail],[Promotion Media].[All Media].[Cash Register Handout],[Promotion Media].[All Media].[Daily Paper],[Promotion Media].[All Media].[Daily Paper, Radio],[Promotion Media].[All Media].[Daily Paper, Radio, TV],[Promotion Media].[All Media].[In-Store Coupon],[Promotion Media].[All Media].[No Media],[Promotion Media].[All Media].[Product Attachment],[Promotion Media].[All Media].[Radio],[Promotion Media].[All Media].[Street Handout],[Promotion Media].[All Media].[Sunday Paper],[Promotion Media].[All Media].[Sunday Paper, Radio],[Promotion Media].[All Media].[Sunday Paper, Radio, TV],[Promotion Media].[All Media].[TV]})' , SOLVE_ORDER=-100
            MEMBER [Store Type].[*AGGREGATE*] AS 'AGGREGATE({[Store Type].[All Store Type].[Deluxe Supermarket],[Store Type].[All Store Type].[Gourmet Supermarket],[Store Type].[All Store Type].[HeadQuarters],[Store Type].[All Store Type].[Mid-Size Grocery],[Store Type].[All Store Type].[Small Grocery],[Store Type].[All Store Type].[Supermarket]})' , SOLVE_ORDER=-100 MEMBER [Yearly Income].[*AGGREGATE*] AS 'AGGREGATE({[Yearly Income].[All Yearly Income].[$10K - $30K],[Yearly Income].[All Yearly Income].[$110K - $130K],[Yearly Income].[All Yearly Income].[$130K - $150K],[Yearly Income].[All Yearly Income].[$150K +],[Yearly Income].[All Yearly Income].[$30K - $50K],[Yearly Income].[All Yearly Income].[$50K - $70K],[Yearly Income].[All Yearly Income].[$70K - $90K],[Yearly Income].[All Yearly Income].[$90K - $110K]})' , SOLVE_ORDER=-100  SELECT   NON EMPTY  HIERARCHIZE({{[Promotions_set_wcalc]}*{[Time_set_wcalc]}*{[Store Size in SQFT_set_wcalc]}}) ON Columns,  NON EMPTY  HIERARCHIZE({{[Product_set_wcalc]}}) ON Rows  FROM [Sales]  WHERE ([Customers].[All Customers],[Education Level].[All Education Level],[Gender].[All Gender],[Marital Status].[All Marital Status],[Measures].[Unit Sales],[Promotion Media].[*AGGREGATE*],[Store].[All Stores],[Store Type].[*AGGREGATE*],[Yearly Income].[*AGGREGATE*])";

            try
            {
                conn.Open();

                cmd=conn.CreateCommand();
                _cmdList.Add(cmd);
                cmd.CommandText=queryStr;
                CellSet cst=cmd.ExecuteCellSet();
                cst=null;
            }
            catch(Exception exc)
            {
                throw exc;
            }
            finally
            {
                if(cmd!=null)
                    _cmdList.Remove(cmd);

                conn.Close();
                conn.Dispose();
            }
        }
        public void Execute_MeasureGroupSalesOrdersLinkedTo_ListStructureContainingCorrectCountOfElements()
        {
            var conn = new AdomdConnection(ConnectionStringReader.GetAdomd());
            var factory = new OlapStructureDiscoveryFactory(conn);
            var cmd = factory.Instantiate(Target.Dimensions, TargetType.Relation,
                        new CaptionFilter[] {
                            new CaptionFilter(Target.Perspectives, "Adventure Works")
                            , new CaptionFilter(Target.MeasureGroups, "Sales Orders")
                        });

            var structs = cmd.Execute();

            Assert.That(structs.Count(), Is.EqualTo(9));
        }
        public void Execute_DateDimensionWithHeighTeenHierarchies_ListStructureContainingCorrectCountOfElements()
        {
            var conn = new AdomdConnection(ConnectionStringReader.GetAdomd());
            var factory = new OlapStructureDiscoveryFactory(conn);
            var cmd = factory.Instantiate(Target.Hierarchies, TargetType.Object,
                new CaptionFilter[] {
                    new CaptionFilter(Target.Perspectives, "Adventure Works"),
                    new CaptionFilter(Target.Dimensions, "Date")
                });

            var structs = cmd.Execute();

            Assert.That(structs.Count(), Is.EqualTo(18));
        }
Ejemplo n.º 47
0
 public static bool TestADOMD()
 {
     try
     {
         AdomdConnection conn = new AdomdConnection(ConfigurationManager.ConnectionStrings["Cube"].ConnectionString);
         conn.Open();
         if (conn.State == ConnectionState.Open)
             return true;
         else
             return false;
     }
     catch(Exception ex)
     {
         SystemHelper.LogEntry("Fail in DbHelper.cs\\TestADOMD(): " + ex.ToString() + "\n");
         throw ex;
     }
 }
        public ViewWeekofyearGivenDistanceDurationCategories GetData(string DistanceCategory, string DurationCategory)
        {
            ViewWeekofyearGivenDistanceDurationCategories model =
                 new ViewWeekofyearGivenDistanceDurationCategories(DistanceCategory, DurationCategory);

            List<BikeTable> hierarchy = new List<BikeTable>();
            hierarchy.Add(new TableWeekday());
            hierarchy.Add(new TableSubscriber());
            hierarchy.Add(new TableHour2());

            string CommandText = @"SELECT
            NON EMPTY { [Measures].[Bikes] } ON COLUMNS,
            NON EMPTY { ([Time Table].[Nameofday].[Nameofday].ALLMEMBERS *
             [Time Table].[Weekofyear].[Weekofyear].ALLMEMBERS *
             [Subscribers].[Subscriber Info].[Subscriber Info].ALLMEMBERS *
             [Time Table].[Hour2ofday].[Hour2ofday].ALLMEMBERS )
              } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
            FROM ( SELECT ( { [Direction].[Direction].&[A-B] } ) ON COLUMNS
               FROM ( SELECT ( STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED) ) ON COLUMNS
              FROM ( SELECT ( STRTOSET(@TripCatTripCategory, CONSTRAINED) ) ON COLUMNS
                     FROM [Bikeshare])))
            WHERE ( IIF( STRTOSET(@TripCatTripCategory, CONSTRAINED).Count = 1,
             STRTOSET(@TripCatTripCategory, CONSTRAINED),
             [TripCat].[Trip Category].currentmember ),
            IIF( STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED).Count = 1,
             STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED),
             [Station Pair Distance].[Mile Categories].currentmember ),
            [Direction].[Direction].&[A-B] )";

            DataSet ds = new DataSet();
            using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2"))
            {
                conn.Open();
                using (AdomdCommand cmd = new AdomdCommand(CommandText, conn))
                {
                    cmd.Parameters.Add(new AdomdParameter("StationPairDistanceMileCategories", DistanceCategory));
                    cmd.Parameters.Add(new AdomdParameter("TripCatTripCategory", DurationCategory));
                    AdomdDataAdapter adapter = new AdomdDataAdapter(cmd);
                    adapter.Fill(ds);
                }
                conn.Close();
            }
            PopulateModel(hierarchy, model, ds);

            return model;
        }
Ejemplo n.º 49
0
        public static List<object> GetData(string command)
        {
            DataSet ds = new DataSet();

            using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2"))
            {
                conn.Open();
                using (AdomdCommand cmd = new AdomdCommand(command, conn))
                {
                    AdomdDataAdapter adapter = new AdomdDataAdapter(cmd);
                    adapter.Fill(ds);
                }
                conn.Close();
            }

            return ConvertDataTableToObjectList(ds.Tables[0]);
        }
Ejemplo n.º 50
0
        public static DataTable ExecuteDataTable(AdomdConnection conn, string CommandText)
        {
            DataTable dt = null;
            try
            {
                AdomdCommand command = new AdomdCommand();
                SetCommand(conn, command, CommandText);
                AdomdDataAdapter da = new AdomdDataAdapter(command);

                dt = new DataTable();
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw;
            }
            return dt;
        }
Ejemplo n.º 51
0
        public static bool ASSPExists(AdomdConnection conn)
        {
            AdomdCommand cmd = new AdomdCommand();
            cmd.CommandText = "call ASSP.AssemblyVersion()";
            cmd.Connection = conn;
            cmd.CommandTimeout = 0;

            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }

        }
Ejemplo n.º 52
0
        protected AdomdCommand CreateCommand()
        {
            var conn = new AdomdConnection();
            conn.ConnectionString = ConnectionString;
            try
            {
                conn.Open();
            }
            catch (AdomdConnectionException ex)
            {

                throw new ConnectionException(ex, conn.ConnectionString);
            }

            var cmd = new AdomdCommand();
            cmd.Connection = conn;
            return cmd;
        }
Ejemplo n.º 53
0
 public void Disconnect()
 {
     try
     {
         Console.WriteLine("Testing Disconnections");
         foreach (DictionaryEntry entry in connectionsStr)
         {
             AdomdConnection conn = new AdomdConnection((String)entry.Value);
             conn.Open();
             Assert.AreEqual(System.Data.ConnectionState.Open, conn.State);
             conn.Close(true);
             Assert.AreEqual(System.Data.ConnectionState.Closed, conn.State);
         }
     }
     catch (Exception ex)
     {
         Assert.Fail(ex.ToString());
     }
 }
Ejemplo n.º 54
0
		static void Main()
		{
			// Import MDX query from file
			var query = new QueryImporter("../../../../LearningMDX.mdx");

			string mdxQuery1 = query.FromFile("Query2");

			using (AdomdConnection conn = new AdomdConnection(connectionString))
			{
				conn.Open();

				using (AdomdCommand command = conn.CreateCommand())
				{
					command.CommandText = mdxQuery1;

					CellSet cellSet1 = command.ExecuteCellSet();
				}
			}
		}
Ejemplo n.º 55
0
    public CubeStructure(String database, String cube, String host, Dictionary<int, string> layerDictionary, String sitesDim)
    {
        m_database = database;
        m_cube = cube;
        String connectionString = "Initial Catalog=" +database+"; Data Source="+ host +"; integrated security=sspi;";
        m_conn = new AdomdConnection();
        m_conn = new AdomdConnection(connectionString);
        m_conn.Open();
        m_xmlcubeschema = new XmlDataDocument();
        m_output = new StreamWriter("C:\\inetpub\\wwwroot\\mattsmaps\\App_Code\\App_Data\\" + m_database + "." + m_cube + "." + "Report.txt");
        m_sitesDim = sitesDim;
        m_exclusionList = new ArrayList();
        foreach (String s in layerDictionary.Values){
            String tmpName = s.Substring(1, s.Substring(1, s.Length -1).IndexOf("."));
            String attributeName = tmpName.Replace("]", "").Replace("[", "");
            m_exclusionList.Add(attributeName);
        }

        generateReport();
    }
		string SetConnectionString(string argument)
		{
			try
			{
				var err = "Argument Schema must be ConnectionId=ConnectionString";
				if (string.IsNullOrEmpty(argument))
					throw new ArgumentException(err);

				var ind = argument.IndexOf('=');
				if (ind < 1)
					throw new ArgumentException(err);

				var connectionName = argument.Substring(0, ind);
				var connectionString = argument.Substring(ind + 1);

				if (string.IsNullOrEmpty(connectionName))
					throw new ArgumentException(err);

				if (string.IsNullOrEmpty(connectionString))
					connectionString = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;

				using (var connection = new AdomdConnection(connectionString))
				{
					string s = "";
					connection.Open();
					foreach (var c in connection.Cubes)
					{
						s += c.Name;
					}
				}
				this.Application[connectionName] = connectionString;
				return connectionName+"="+connectionString;
			}
			catch (Exception E)
			{
				return E.ToString();
			}
		}
Ejemplo n.º 57
0
        protected AdomdCommand CreateCommand()
        {
            var conn = new AdomdConnection();

            //If connectionString is empty throw a ConnectionException
            if (String.IsNullOrEmpty(ConnectionString))
                throw new ConnectionException(new ArgumentNullException(), "No connectionString found.");
            conn.ConnectionString = ConnectionString;

            try
            {
                conn.Open();
            }
            catch (AdomdConnectionException ex)
            {

                throw new ConnectionException(ex, conn.ConnectionString);
            }

            var cmd = new AdomdCommand();
            cmd.Connection = conn;
            return cmd;
        }
        public TableTripCategories GetData()
        {
            TableTripCategories model = new TableTripCategories();
            // hierarchy properly defined within report
            List<BikeTable> hierarchy = new List<BikeTable>();
            hierarchy.Add(new TableSubscriber());
            hierarchy.Add(new TableHour2());

            DataSet ds = new DataSet();
            using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2"))
            {
                conn.Open();
                using (AdomdCommand cmd = new AdomdCommand(CommandText(), conn))
                {
                    AdomdDataAdapter adapter = new AdomdDataAdapter(cmd);
                    adapter.Fill(ds);
                }
                conn.Close();
            }
            PopulateModel(hierarchy, model, ds);

            return (model);
        }
Ejemplo n.º 59
0
        public static CellSet ExecuteCellSet(AdomdConnection conn, string CommandText)
        {
            try
            {
                // Create a new Adomd command
                AdomdCommand command = new AdomdCommand();

                //Prepare the command
                SetCommand(conn, command, CommandText);

                //Execute the command
                CellSet cst = command.ExecuteCellSet();
                //var aa = cst.Axes[0].Set.Tuples[0].Members[0].GetChildren();
                command.Parameters.Clear();
                //var bb = cst.Axes[0].Set.Tuples[0].Members[0].GetChildren();
                return cst;

            }
            catch (Exception ex)
            {
                throw;
            }
        }
Ejemplo n.º 60
0
        public static int ExecuteNonQuery(AdomdConnection conn, string CommandText)
        {
            try
            {
                // Create a new Adomd command
                AdomdCommand command = new AdomdCommand();

                //Prepare the command
                SetCommand(conn, command, CommandText);

                //Execute the command
                int val = command.ExecuteNonQuery();

                command.Parameters.Clear();

                return val;

            }
            catch (Exception ex)
            {
                throw;
            }
        }