-
Notifications
You must be signed in to change notification settings - Fork 0
/
PythonApplication7.py
41 lines (34 loc) · 1.27 KB
/
PythonApplication7.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
# Import packages
import pandas as pd
from sys import path
path.append('\\Program Files\\Microsoft.NET\\ADOMD.NET\\160')
from pyadomd import Pyadomd
from datetime import datetime as dt
# Build connection string
##Catalog is the database
conn_str = 'Provider=PRODIVERINFO; Data Source=YourDatabaseURL;Catalog=OPTIONAL_DATABASENAME;'
# Enter DAX or MDX query
dax_query = """SELECT
Field1*{Field2.&Value1,Field2.&Value2}*{Field3.&Value1,Field3.&Value2
} ON 0,
{Field4} ON 1
FROM
Database_cube
WHERE ({Field4.&Value1,Field4.&Value2},Field5.&Value1,Field6.&Value1)"""
# Output results as pandas dataframe
with Pyadomd(conn_str) as conn:
with conn.cursor().execute(dax_query) as cur:
df = pd.DataFrame(cur.fetchone(),
columns=[i.name for i in cur.description])
# Rename Columns
#df.rename(columns={'OriginalColumnName1':'NewColumnName1',
# 'OriginalColumnName2':'NewColumnName2',
# 'OriginalColumnName3':'NewColumnName3'},
# inplace = True)
df['date_column']=pd.to_datetime(df['date_column'])
#print(df.dtypes)
df['year']=df['date_column'].dt.year
df['quarter']=df['date_column'].dt.quarter
df['month']=df['date_column'].dt.month
df['index']=df.index
df.to_csv('file_name.csv')